Potential bug when decode is combined with condition

[Jesse Plumb]

Jooq version: 3.4.0

The following code:

Field column = DSL.fieldByName("Table","Column");

Field caseWhen = DSL.decode().when(column.isNull(), 0).otherwise(column);

Condition condition = caseWhen.lessOrEqual("Value");

System.out.println("Condition: " + condition);

outputs:

Condition: case when "Table"."Column" is null then 0 else "Column" end <= null

expected:

Condition: case when "Table"."Column" is null then 0 else "Table"."Column" end <= "Value"

If I wrap the value as a field it doesn't make the field null, but I would like to be able to compare the results of the case statement with a string literal. Any suggestions for a workaround?

[Lukas Eder]

Hello,

I can reproduce your issue and here's the explanation:

caseWhen.getType() == Integer.class

This type is inferred from your passing "0" to the when() method. Because you're using raw types, jOOQ allows you to call otherwise(Field<Object>) and lessOrEqual(String) when you should really use otherwise(Field<Integer>) and lessOrEqual(Field<String>)

Now, the reason why you get "null" is because the lessOrEqual(T) method converts the right-hand-side to be of the same type as the left-hand-side, which doesn't work. Currently, the logic implemented in org.jooq.tools.Convert doesn't follow a "fail-fast" strategy, so you get "null" instead of some DataTypeException. The current strategy has been criticised before. We might change that in jOOQ 4.0... I've now registered an issue as a reminder:

https://github.com/jOOQ/jOOQ/issues/3377

Now, for workarounds:

1. We generally suggest not to bypass generic typesafety. It is there to help you prevent such issues. You really shouldn't mix data types in your THEN / ELSE clauses of a CASE expression, not even in SQL.

2. When using the code generator, this kind of issue usually doesn't appear. Of course, that might not be an option for you.

3. Even if typesafety is applied correctly, you'll run into this issue because your caseWhen reference has a java.lang.Integer type. If you really don't want that type, you'll have to coerce types to java.lang.Object. See also: http://www.jooq.org/doc/latest/manual/sql-building/column-expressions/datatype-coercions/

I.e. possible workarounds are:

Field caseWhen = DSL.coerce(DSL.decode().when(column.isNull(), 0).otherwise(column), Object.class);

Field caseWhen = DSL.decode().when(column.isNull(), DSL.coerce(DSL.val(0), Object.class)).otherwise(column);

Hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jesse Plumb]

Thanks a ton, Lucas! That was extremely helpful. I can probably get away with passing the 0 as a string, so I'll try that first. Appreciate the quick response.

You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/YlE2Dlq64Ic/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

Yes, of course. Passing "0" might be even better for your use-case.