Repository navigation
Three-valued logic for NOT, AND, OR and IN with NULL (measured against SQLite) #2597
Description
Three-valued logic for v5, as a breaking change: queries that use NOT with a NULL will return fewer rows. This continues #742 (2016) with measurements on v4.20.0. Requirement decided 2026-10-15: correct results, and no measurable slowdown for queries without NULLs.
What happens today
alasql has two values for a condition: true or false. A comparison with NULL gives undefined, which counts as false. NOT then turns it into true. SQL has three values: true, false and unknown. NOT unknown is unknown, and a WHERE only keeps rows that are true.
Measured on a table without declared types, rows (1) and (NULL). The table shows the number of rows kept:
WHERE … |
alasql | SQLite 3.43 |
|---|---|---|
NOT (a = 1) |
1 (the NULL row) | 0 |
NOT (a > 5) |
2 | 1 |
NOT (a IN (1)) |
1 | 0 |
NOT (a BETWEEN 0 AND 5) |
1 | 0 |
NOT (a LIKE '1') |
1 | 0 |
NOT (NULL = NULL) |
2 | 0 |
a <> 1, a NOT IN (1) |
0 | 0 (already right: the operator itself compares with NULL) |
In the select list:
| Expression | alasql | SQLite |
|---|---|---|
NOT (1 = NULL) |
true |
NULL |
NULL AND FALSE |
undefined |
0 (false) |
NULL OR FALSE |
false |
NULL |
NULL OR TRUE |
true |
1 |
So NOT (x) and x with the operator negated (<>, NOT IN) give different answers today.
A related trap: in a table with a declared INT column, INSERT … VALUES (NULL) stores NaN. Then a IS NULL does not match that row, and NOT (a IS NULL) does. That is a storage fault, tracked in #2588.
Scope for v5
NOT,ANDandORfollow the SQL truth tables when an operand is NULL.WHERE,HAVING,ONandCASE WHENkeep a row only when the condition is true.IN/NOT INwith a NULL in the list follows SQL (Fix NULL comparison in IN and NOT IN operators to follow SQL semantics #2315, Comprison on NULL differes from SQL #1414).- How, within the requirement: a
WHEREkeeps only true rows, so unknown and false give the same result there. The third value matters only under aNOT(andAND/ORbeneath it) and where a condition's value is returned (SELECT a = 1 AS x,CASE). Emit plain JavaScript everywhere else and a NULL check only in those places. Measure withtest/performance/. If a cost remains that matters,compare-fast(One comparison module with levels (dates compared by value, MAX/MIN on strings, one NULL order) #2595) keeps v4's two-valued logic, so developers can choose.
The sqllogictest corpus has about 446,000 lines (counting all engine variants) that contain WHERE NOT, AND NOT or OR NOT. Not all of them involve NULL, so the effect on the score is unknown until a run is made.
Related: #742, #2580 (NOT IN with NULL), #2588 (null instead of undefined), #2590 (CAST(NULL AS INTEGER) gives 0), #2315, #1414, #368, #458.
Activity
[-]v5: three-valued logic for NOT, AND, OR and IN with NULL (measured against SQLite)[/-][+]Three-valued logic for NOT, AND, OR and IN with NULL (measured against SQLite)[/+]