On 21 May 2002 at 13:38, Helen Borrie wrote:

>order by the indexed field and order by the calculated field are NOT
>the same thing. If you order by the indexed field, you will get your
>mixed case ordering, as you correctly supposed. If you order by the
>calculated field, you will get the upper-case order. So it depends on
>what you want...if you want the indexed order (and faster output) do
>ORDER BY UNICODE_FIELD

"Right" servers (like Oracle or MS SQL and even MySQL) always use
language-specific ordering because any other ordering of text strings
is a nonsense.
It seems that UNICODE_FSS cannot be converted to uppercase at all.
At least indices are built in binary order, not language-specific.

>What most people do when they want case-insensitive matching and
>sorting is to define a proxy ordering column that is populated by BI

And waste the space in vain...

>and BU triggers that do this: .... if (new.originalcolumn is not null)
>then
> new.proxycolumn = upper(originalcolumn);

Oh, how good it is in theory. But some character sets don't have
uppercase table for default collate order and UPPER() doesn't work
without explicit COLLATE statement.

>Instead of indexing the original column, place your indexes on the
>proxy column.

And forget about fields larger than 84 characters. Or don't use
UNICODE_FSS.

>It's a very fast and bomb-proof solution which totally avoid the
>function call on each row when forming output for selects.

It is an usual trick of Borland: "Sales office pressed us so much
that we didn't have enough time to make the intl module properly, but
there is a couple of workarounds and you won't be bored. And besides,
do you really think that we should learn all these stupid languages?
Use English, it is the perfect one."

SY, Dimitry Sibiryakov.