#1516 Case-insensitive English collation does not work with LIKE clause

[Ewan Mellor]

The docs at http://hsqldb.org/doc/guide/databaseobjects-chapt.html#dbc_collations say (regarding collation strengths) "The value 0 indicates least sensitivity to differences. At this strength the collation is case-insensitive and ignores differences between accented letters." To me, this means that "English 0" should be a case-insensitive collation, and so equality or LIKE clauses should match case-insensitively.

This works as I expect with SQL_TEXT_UCC collation, but with "English 0" collation an equality check is case-insensitive, but a LIKE clause is not. This is inconsistent between those two collations, because I would expect them to be the same in terms of case-insensitivity. I expect the collations to work the same whether it's an equality or a LIKE clause.

SqlTool v. 5736.
JDBC Connection established to a HSQL Database Engine v. 2.4.0 database
sql> create table c1 (s varchar(255) collate "SQL_TEXT_UCC");
sql> insert into c1 values('A'), ('ab');
2 rows updated.
sql> select * from c1 where s='a';
A
sql> select * from c1 where s like 'a%';
A
ab

Fetched 2 rows.

sql> create table c2 (s varchar(255) collate "English 0");
sql> insert into c2 values('A'), ('ab');
2 rows updated.
sql> select * from c2 where s='a';
A
sql> select * from c2 where s like 'a%';
ab

[Fred Toussi]

Thanks for reporting. Fixed and committed to SVN for version 2.5.2 and later.