> However, not all databases actually implement the boolean > data type, so for many databases, the above table doesn't > really apply
You can't simply output the result of a boolean expression, but you can always extract them via CASE expressions, so yes the truth tables do apply. It's just that if you input >> Aggregate functions generally ignore nulls. I can't think >> of a binary-vs-ternary semantic distinction that would >> make any sense, so that part of the API can be left as is. > > It could be relevant to GROUP_CONCAT(), LISTAGG() and similar > (ordered) aggregate functions. Agreed. Needs checking. In those cases where having a NULL is equivalent to not having a row, no ternary variant is needed. >> I'm not sure about IN and NOT IN (I have no idea what a >> typical database does if the set of values contains a NULL). > > Yes, NOT IN is the most tricky one. 1 NOT IN (NULL, 1) > returns false (or more correctly, returns NULL). Actually "1 not in (NULL, 1)" returns FALSE. You can test that by wrapping the condition in a NOT: For NULL (UNKNOWN for conditions), NOT does not change the outcome, for FALSE, you get TRUE. So "select * from dual where not (1 not in (null, 1))" returns all rows of the DUAL table. That's semantically sound actually. NULL stands for an indefinite value, so 1 is in (NULL, 1) however you vary the NULL, which means that 1 NOT IN (NULL, 1) should return FALSE, not UNKNOWN. > I had recently posted a blog post about this, and how it > isn't handled correctly (the ternary way) in all databases: > http://blog.jooq.org/2012/01/27/sql-incompatibilities-not-in-and-null-values/ Heh. Did I mention that ternary logic is unintuitive? ;-) I guess that should be expanded with "... even for RDBMS experts." >> Ordered comparisons ( < > BETWEEN ) against NULLs tend to >> have three variants: UNKNOWN, NULLS FIRST, NULLS LAST. > > Same here. I'm pretty sure, the SQL standard defines UNKNWON to > be the only correct outcome of such comparisons. > But some databases may have different views on the subject. I was more after "what would a developer need". This was inspired by NULLS FIRST/LAST in ORDER BY clauses, but of course if you have a comparison in a WHERE condition, the outcome of < or > will always be UNKNOWN. It would be useful to build a code generator that emits code for, say, a NULLS FIRST <. However, I guess that's beyond the scope of what Jooq should do. A Java-side lessNullsFirst(a, b) would probably have to emit something like (a is null or b is not null and a < b) (untested, likely buggy), and that means emitting the subexpression for a and b twice - you don't even want to have that generated if when it's useful. > Thanks for your thoughts. I'll do some thinking on my side, too. You're welcome. It's always nice to be heard :-)
