Hi Ariel, Very early versions of jOOQ did exactly what you're suggesting. Render "x IS NULL" when writing x.eq(null) in Java. That appeared to have been too clever and only led to pain and suffering:
- What happens to x.eq(val(null))? - What happens to x.eq(y), where y is a nullable database column? - What happens to val(null).eq(x)? Or worse, val(null).eq(null)? - What happens to other predicates, like x.gt(null) or x.between(null).and(null)? - What happens to IN predicates, i.e. x.in(null) or worse, x.notIn(null)? (semantics of the latter is *very* different) - What happens to row value expressions, e.g. row(a, b).eq(row(null, null))? - What happens in the (unlikely) event where someone really *wants* a predicate that is "unknown"? - And probably the worst: Query.getSQL() would now render less bind values than reported by Query.getParams() and Query.getBindValues(). jOOQ could no longer interoperate with other APIs, e.g. Spring's JdbcTemplate. There were many more reasons why we finally removed that "clever" optimisation in favour of accepting the fact that NULL / UNKNOWN is a bit different in SQL :) Note that if your database supports the DISTINCT predicate, you can use x.isNotDistinctFrom(null): - http://www.jooq.org/doc/latest/manual/sql-building/conditional-expressions/distinct-predicate/ - http://blog.jooq.org/2012/09/21/the-is-distinct-from-predicate/ Or, you could guard your bind values by writing x.eq(nvl(null, "dummy")) Hope this helps Lukas 2014-06-26 0:18 GMT+02:00 Ariel Tal <[email protected]>: > Doesn't appear to be related to any particular data-type. It didn't work > for String, Integer and Enum data-types. > Using JOOQ 3.1.0. > > Thanks > > > On Wednesday, June 25, 2014 6:02:31 PM UTC-4, Ariel Tal wrote: >> >> This is possibly related to: https://groups.google.com/ >> forum/#!searchin/jooq-user/eq(null)/jooq-user/xmkOXh6VxdQ/QpAzJlEK2k0J >> >> >> If you try to compare a field to a variable using >> MY_TABLE.MY_FIELD.eq(myVar) and myVar happens to be null, the resulting >> condition in the query (for MySQL) will be: >> `MY_TABLE`.`MY_FIELD` = null instead of `MY_TABLE`.`MY_FIELD` IS NULL >> (as it should). >> >> >> Is this a known issue? >> >> Thanks, >> Ariel >> > -- > 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 [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
