Possible bug - null variable in 'eq' doesn't translate into IS NULL

[Ariel Tal]

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

[Ariel Tal]

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

[Deven Phillips]

Ariel,

    No, you are correct.. To get that effect, you should use the .isNull() method on the field type:

.where(MYTABLE.MYFIELD.isNull()).fetchOne()

Cheers,

Deven

[Lukas Eder]

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

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

[Lukas Eder]

A small correction:

2014-06-26 8:18 GMT+02:00 Lukas Eder <lukas...@gmail.com>:

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

That might not be correct, in fact, as the behaviour of these methods was changed with

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

[Ariel Tal]

Null handling is indeed not trivial, but I think this is more relevant for eq/ne than for gt/in - gt(null) should throw an NPE in my opinion, and in(null) should probably translate to IN () ).

Leaving it up to the user is a reasonable decision. In my case the column is nullable and I ended up creating a wrapper eqOrIsNull and neOrNotNull to get the behavior I was expecting.

Thanks,

Ariel

[Lukas Eder]

Hi Ariel,

2014-06-26 15:32 GMT+02:00 Ariel Tal <ariel...@gmail.com>:

Null handling is indeed not trivial, but I think this is more relevant for eq/ne than for gt/in - gt(null) should throw an NPE in my opinion,

What about gt(val(null))? Or gt(DSL.field("null"))? Or gt(DSL.field("?", null))?

 

and in(null) should probably translate to IN () ).

IN () is probably not a very good idea :-)

For the reference, here's the syntax specs from the SQL standard:

         <in predicate> ::=
              <row value constructor>
                [ NOT ] IN <in predicate value>

         <in predicate value> ::=
                <table subquery>
              | <left paren> <in value list> <right paren>

         <in value list> ::=
              <value expression> { <comma> <value expression> }...

Empty <in value list> are not possible... Besides, in(null, 1) is a valid predicate, again...

Leaving it up to the user is a reasonable decision.

I think it's the only reasonable decision, given that even if translating eq(null) / ne(null) seems to be a low-hanging fruit at first, it is completely unexpected and inconsistent with pretty much all of the other API.

 

In my case the column is nullable and I ended up creating a wrapper eqOrIsNull and neOrNotNull to get the behavior I was expecting.

Hmm, yes, that could be useful. Essentially, you're generating A = x OR x IS NULL for A.eq(x)?

Best,

Lukas

[Ariel Tal]

On Thursday, June 26, 2014 4:37:41 PM UTC-4, Lukas Eder wrote:

I think it's the only reasonable decision, given that even if translating eq(null) / ne(null) seems to be a low-hanging fruit at first, it is completely unexpected and inconsistent with pretty much all of the other API.

 

In my case the column is nullable and I ended up creating a wrapper eqOrIsNull and neOrNotNull to get the behavior I was expecting.

Hmm, yes, that could be useful. Essentially, you're generating A = x OR x IS NULL for A.eq(x)?

If value that's passed in is null I return IS NULL, otherwise I return eq(val):

public static <T> Condition eqOrIsNull(Field<T> fld, T val) {

    return val==null ? fld.isNull() : fld.eq(val);

}

Thanks