Joachim, Stéphane, Thank you very much for your valuable input and for sharing your reasoning. I entirely agree with this paragraph by Stéphane:
> Stéphane: > -------------- > In you upper limit example, the fact that you wrote a special widget for > your nullable upper limit field is a good indicator that properly handling > NULL needs separate code. As a result, you probably don't really benefit > from an API mixing both cases. [...] Indeed, when dealing with NULL as a "special value", you will probably have to resort to "special logic" - no matter whether you understand that "specialty" in the Java way (no value), or in the SQL way (an unknown value). However, remember that jOOQ's current implementation of handling "eq(null)" predicates as if they were "IS NULL" predicates originates from the fact that a true "X = NULL" or "X <> NULL" comparison predicate is hardly of any use, even to those SQL aficionados that want to implement ternary logic. > Stéphane: > -------------- > Imho, there aren't many use cases where you can make your life easier by > automatically transforming eq(null) to IS NULL, a NULL in a column is rarely > just a value like any other, rather a special case.like for example the > absence of a value. So it's pretty rare that you can use the same code for > selecting, filtering etc... The most important use case that lead to the current implementation in jOOQ are filters for optional foreign keys. In that case, Java's null and SQL's NULL usually share the same semantics in that SQL's NULL doesn't represent UNKNOWN, but "no value". But then again, as Stéphane suggested, a Java "null" value for such a filter probably indicates "no filter", rather than a filter for "no reference". In other words, even if BOOK.AUTHOR_ID is nullable, a null Java filter reference would probably indicate "all books", rather than "books with no author" > Joachim > ------------ > Once you start writing outer joins, null handling becomes > really important. For example, to test whether a relationship > exists, you can test any primary key column: if it's null, > the corresponding related record didn't exist. This isn't > important when doing interactive SQL but quite common > in batch processing, where you try to shove as much work > as possible into the query optimizer. I'm guessing that you're hinting at writing a NOT EXISTS anti-join using OUTER JOIN and IS NULL predicates? In that case, you should really explicitly use an IS NULL predicate via FIELD.isNull(), instead of relying on some jOOQ voodoo using FIELD.eq(null) if it's only to clearly show your intent. > Stéphane: > -------------- > Another possibility would be to use a dedicated > constand, like c# DbNull that can be easily differentiated... > T_BOOK.idAuthor.eq( DB_NULL ) A DB_NULL constant would "consume" a value in the Java world, to actually represent that null value. A possible value for integers would be Integer.MIN_VALUE, Long.MIN_VALUE. This is quite problematic for byte and short though. "Wasting" Byte.MIN_VALUE for such a special semantic seems wrong / surprising to me. Or did I miss something from C# DB_NULL? >From these discussions and also your input, I think jOOQ 3.0 should introduce a slight backwards-incompatibility of behaviour in the eq() / equals() / ne() / notEquals() methods, and remove this "feature" for these reasons: 1. It is surprising to some users, and thus "voodoo". Voodoo is never good. 2. It is not entirely reliable when extracting bind values from jOOQ, thus the original GitHub issue https://github.com/jOOQ/jOOQ/issues/2001. Without removing the current feature, this issue is hard to fix and it was brought up several times on this list. 3. Its implementation is a kludge (isNullLiteral()). Removing this implementation would greatly improve the code base 4. Those few users that might rely on this feature can live with writing if / else checks as suggested in this thread. 5. jOOQ already supports the DISTINCT predicate (IS [NOT] DISTINCT FROM), which is the correct SQL way of doing NULL-agnostic checks Again, thanks for your inputs. In a second step (after removing this feature), I will again review the API to see if any other parts should be adapted for ternary logic. Cheers Lukas
