Derby casting madness – the sequel

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

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:

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


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)))


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)


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


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

Casting other pairs

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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s