CODEWITHSUNDEEP
sqlutf-8firebirdfirebird2.5
mad
mad

Reputation: 1047

Firebird order by collation

I am with strange problem with Firebird 2.5.

My database has default charset = utf8.

I have a column p_nname in patienten table:

CREATE TABLE PATIENTEN (
   P_NNAME       VARCHAR(25) DEFAULT '' NOT NULL COLLATE UNICODE_CI,

I expect collation to work everywhere. I mean in WHERE and ORDER BY clauses.

What I have is working collation in WHERE. Two queries below give me similar result and it is good.

select * from patienten where p_nname='adler'
select * from patienten where p_nname='ADler'

Problem is ORDER BY clause does not work as I expect.

This SQL works as if the column has no UNICODE_CI collation.

select * from patienten order by p_nname

To get the needed result with good sorting I have to write so:

select * from patienten order by p_nname collate unicode_ci

Is there a way to omit COLLATE flag in ORDER BY clause?

Upvotes: 4

Views: 1894

Answers (1)

ain
ain

Reputation: 22749

Looks like a bug indeed, the documentation states:

The keyword COLLATE specifies the collation order for a string column if you need a collation that is different from the normal one for this column. The normal collation order will be either the default one for the database character set or one that has been set explicitly in the column's definition.

so it should work without specifing the collate clause in ORDER BY. I suggest you file a bug report.

Upvotes: 1

Related Questions