Derby casting madness – the sequel

I have recently blogged about the general bind variable casting madness in SQL:

http://lukaseder.wordpress.com/2011/08/31/rdbms-bind-variable-casting-madness/

So this is the sequel of the above story, purely dedicated to Derby with its “conversion table from hell“. One of jOOQ’s goals is to make SQL as compatible as possible across various databases, in a way that you can re-use the same SQL on various environments. For instance:

• Use Derby to develop your database
• Use DB2 for production

While I personally discourage such set-ups, I know that many developers prefer that, especially when it comes to run fast-running integration tests. And the above coupling of Derby and DB2 is an especially good one, as Derby is quite similar to DB2. See also this Stack Overflow Question:

http://stackoverflow.com/questions/4419684/portable-schema-between-derby-and-db2

But back to casting. In order to make casting as compatible as possible, jOOQ generates casting SQL according to the following rules:

Casting NUMERIC to VARCHAR

Interestingly, this is not supported, but casting to CHAR is. So jOOQ generates:

-- When 123 is inlined:
trim(cast(cast(123 as char(38)) as varchar(32672)))

-- When 123 is bound as a variable
trim(cast(cast(cast(? as int) as char(38)) as varchar(32672)))

Casting CHAR/VARCHAR to DOUBLE/FLOAT/REAL

Again, this isn’t supported for some reason. So jOOQ generates:

-- When 123.0 is inlined:
cast(cast('123.0' as decimal) as float)

-- When 123.0 is bound as a variable
cast(cast(cast(? as varchar(32672)) as decimal) as float)

Casting NUMERIC to BOOLEAN

This can’t be expressed simply with a CAST clause. A CASE .. WHEN clause is rendered instead, by jOOQ (note that Derby doesn’t support the simple CASE clause either…):

case when cast(? as int) = 0 then false
     when cast(? as int) is null then null
     else true
end

Casting CHAR/VARCHAR to BOOLEAN

The Derby documentation claims that this should work, but I’ve experienced quite some trouble. Derby seems to accept only SQL standard boolean literals and rejects values such as ’0′, ’1′, etc… Most databases accept ’0′, ’1′ as boolean string values as well. So jOOQ simulates the following

case when       cast(? as varchar(32672))  = '0' then false
     when lower(cast(? as varchar(32672))) = 'false' then false
     when lower(cast(? as varchar(32672))) = 'f' then false
     when cast(? as varchar(32672)) is null then null
     else true
end

Casting other pairs

Luckily, all other common types of casts seem to work as expected, also in the Derby database.

From http://lukaseder.wordpress.com/2011/10/29/derby-casting-madness-the-sequel/