Case Sensitivity

[Jeremy Boynes <jb...@apache.org>]

This is a subject that keeps coming up on the mailing lists so is 
probably worth addressing.

One challenge we face is that many users are likely to be considering 
Derby as an alternative to MySQL which by default uses a 
case-insensitive collation. This is legal SQL, just different from most 
other databases (Oracle, DB2, SQL Server (which actually allows you to 
choose when you create the database)).

The standard way of solving this would be to support user-specified 
character sets and collations per the spec but
1) that's likely to be a lot of work
2) I don't know of another database that actually does this the way
    the spec says, making us "different", and
3) it is likely to be more confusing than helpful

Other options include:
a) support collation specification at the schema level like SQL Server
b) support function-based indexes, so users can create indexes
    on UPPER(col) to reduce the performance hit
c) just document and offer workarounds (like adding a second column)
d) change Derby to be like MySQL (gets a -1 from me but ...)

Thoughts?
--
Jeremy

Re: Case Sensitivity

[Satheesh Bandaram <sa...@Sourcery.Org>]

I personally like the functional indexes. I think they are useful for
all cases where querries have expressions in predicates.

Satheesh

Jeremy Boynes wrote:

> This is a subject that keeps coming up on the mailing lists so is
> probably worth addressing.
>
> One challenge we face is that many users are likely to be considering
> Derby as an alternative to MySQL which by default uses a
> case-insensitive collation. This is legal SQL, just different from
> most other databases (Oracle, DB2, SQL Server (which actually allows
> you to choose when you create the database)).
>
> The standard way of solving this would be to support user-specified
> character sets and collations per the spec but
> 1) that's likely to be a lot of work
> 2) I don't know of another database that actually does this the way
>    the spec says, making us "different", and
> 3) it is likely to be more confusing than helpful
>
> Other options include:
> a) support collation specification at the schema level like SQL Server
> b) support function-based indexes, so users can create indexes
>    on UPPER(col) to reduce the performance hit
> c) just document and offer workarounds (like adding a second column)
> d) change Derby to be like MySQL (gets a -1 from me but ...)
>
> Thoughts?
> -- 
> Jeremy
>
>
>