And create a table as follows:
CREATE TABLE foo (
id INTEGER GENERATED BY DEFAULT AS IDENTITY,
And insert a test row...
INSERT INTO foo (column1) VALUES ('test')
A "standard" query works as expected ...
SELECT * FROM foo WHERE column1 LIKE 'TE%'
... does indeed perform a case in-sensitive LIKE as desired, and returns this row:
So far, so good. Now this query...
SELECT * FROM foo WHERE id = '1' -- Note the quoted '1' rather than a raw 1
... returns this error:
Error: Comparisons between 'INTEGER' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
Unfortunately, this is VERY unlike other databases. This is a trivial query which I would expect to work. If Derby knows the collation the the database, why are casts not automatically performed? I actually encountered this error first with this query to list all tables:
SELECT T2.* FROM SYS.SYSTABLES systabs, TABLE (SYSCS_DIAG.SPACE_TABLE(systabs.tablename)) AS T2
WHERE systabs.tabletype = 'T'
ORDER BY isindex, conglomeratename
Which returns a similar error whereby the only solution is to CAST(systabs.tabletype AS CHAR).
My company is, or rather was, in the process of switching our product from Derby with default (UCS_BASIC) collation, to TERRITORY_BASED:PRIMARY in order to solve performance issues related to the inability to perform case in-sensitive queries (because using a function like LCASE() will not use an index).
Unfortunately, given the size of the code-base (several hundred thousand lines), the fact that a myriad of these CAST related errors could be hiding in nooks and crannies is forcing us to reconsider. Even if we _could_ find and correct all queries were something like an INTEGER is being compared to a string, having to put CAST everywhere we might interact with system tables seems gross.
Is there any solution to this? Any connection mode, startup parameter, etc. that we're missing? Is there any reason Derby shouldn't perform these CAST automatically? No other database I've used (Oracle, SQL Server, MySQL, PostgreSQL, ...) seem to have this problem.