I have use cases for both "null-safe" (binary) and ternary logic. The problem with ternary logic is that it's quite unintuitive unless your intuition is full SQL. I.e. people who think SQL will want the ternary logic as default, people who think Java will want the null-safe (binary) logic as default. Jooq being a Java library should go with Java conventions. However, it would be useful to have alternate predicates that employ ternary logic, i.e. equal3 and notEqual3 (which would need to return TRUE/FALSE/UNKNOWN so you can have and3, or3, not3 etc.).
________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of Lukas Eder Sent: Wednesday, December 12, 2012 9:07 PM To: [email protected] Subject: [REQUEST FOR FEEDBACK] Handling of NULL in jOOQ's comparison predicates Hello group I have received interesting feedback about jOOQ's handling of NULL in comparison predicates. This is about a user who uses jOOQ for SQL rendering. They use jOOQ's support for named parameters as documented in the manual, here: http://www.jooq.org/doc/2.6/manual/sql-building/bind-values/named-parameters/ The issue can be seen here: https://github.com/jOOQ/jOOQ/issues/2001 As some of you may be aware of, the jOOQ .equal() and .notEqual() methods have a "special" behaviour, when passing a Java null value. This is documented in the Javadoc: If value == null, then this will return a condition equivalent to isNull() <http://www.jooq.org/javadoc/latest/org/jooq/Field.html#isNull()> for convenience. SQL's ternary NULL logic is rarely of use for Java programmers. Taken from: http://www.jooq.org/javadoc/latest/org/jooq/Field.html#equal(T) Now, while this may be quite convenient, as in 80% of the use cases, users really don't want to deal with SQL's ternary NULL/UNKNOWN logic, it can cause quite some confusion when users actually want to extract SQL and bind values from jOOQ queries, as jOOQ is then "swallowing" these particular null bind values. My questions to the group: 1. Do you find this "feature" really useful (as opposed to doing null checks and handling IS NULL manually)? Or do you think it should be removed? 2. Do you think a new Setting should be introduced to allow for distinguishing the two use-cases for jOOQ? (my favourite option, so far, even if I don't like adding to many settings) 3. Do you see any other solution to deal with the problem? Thanks for any feedback! Cheers Lukas
