H2 In memory DB treating ORDER BY differently in MySQL mode

I have a query that does anORDER BY on a VARCHAR开发者_如何转开发 column that contains email addresses.

If I hit my physical MySQL db, it ignores case in the ORDER BY. However, my h2 in-memory DB is respecting case. It is set to MySQL mode.

Anyone know why this is?

Case sensitivity when evaluating strings in databases is determined by the collation.

Check the collation handling on H2: http://www.h2database.com/html/grammar.html#set_collation

As an alternative to using a collation, you can disable case sensitivity using SET IGNORECASE TRUE. This needs to be done before creating the tables.

The reason why the MySQL mode of H2 isn't case insensitive is: compatibility modes in H2 don't affect how things are persisted (otherwise you couldn't access a database in a different compatibility mode later on, or disable the compatibility mode). Case sensitivity does affect how things are stored (specially indexes).

Use VARCHAR_IGNORECASE instead of VARCHAR.

Reference