Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.
I am using HSQL 2.3.0 Beta, version hsqldb-20130629.235800-51.jar, the latest Snapshot as of this date.
I am finding some search ( Select ) statement irregularities that I cannot explain, and, I think it may be new / different with the latest snapshot version.
My database, includes, in the SCRIPT file, the following statement ( as described in the documentation, Chapter 4 - Schemas and Database Objects - Collations ), and, I have issued the command, SHUTDOWN COMPACT, to ensure indices etc are re-created.:
SET DATABASE COLLATION "SQL_TEXT_UCC"
With the above command, my understanding is that any 'search' should be CASE ( UPPER / lower ) insensitive. The field I am searching on, is defined as VARCHAR(9), and, the content is all UPPER CASE.
The following statement works exactly as expected, returning one record:
Select GRPTIME.* From GRPTIME Where GRPTIME.DESCRIPT = 'lunCH';
But, the following finds ZERO records, despite the fact it should have returned ONE record ( but does NOT match CASE ):
Select GRPTIME.* From GRPTIME Where GRPTIME.DESCRIPT LIKE '%nC%';
Likewise, the use of the BUILT-IN functions, LOCATE and INSTR . . . where I thought it find records, regardless of CASE ( UPPER / lower ) apparently will only return records if it ALSO matches case, despite having set, COLLATION "SQL_TEXT_UCC" .
The LOCATE and INSTR functions always use the SQL_TEXT collation and are case-sensitive.
LIKE allowed case-insensitive comparison in previous versions. The next snapshot will restore the bahaviour.
Thank-you for responding.
Regarding use of functions, similar ( like ) to LOCATE and INSTR always using SQL_TEXT collation, I have no problem with this.
My only suggestion is, someplace in the documentation, perhaps this could be mentioned.