SQL: Any comparison with NULL should yield UNKNOWN

[philrz]

While the SQL NOT IN support added in #5992 seems to work correctly for the general case, according to SQL conventions, there should have been no rows returned here.

$ super -c "SELECT * FROM intest WHERE a NOT IN (NULL, NULL)"
{a:42::int32,b:42::int32,c:42::int32}
{a:43::int32,b:42::int32,c:42::int32}
{a:44::int32,b:41::int32,c:44::int32}

Similar here:

$ super -c "SELECT * FROM intest WHERE NULL NOT IN ('a', 'b');"
{a:42::int32,b:42::int32,c:42::int32}
{a:43::int32,b:42::int32,c:42::int32}
{a:44::int32,b:41::int32,c:44::int32}

Details

Repro is with with super commit e75dfda. This was found via queries from this sqllogictest and this other sqllogictest.

Here's the queries returning no rows in Postgres, per the SQL convention.

$ psql postgres
psql (17.5 (Homebrew))
Type "help" for help.

postgres=# CREATE TABLE intest (a INTEGER, b INTEGER, c INTEGER);
INSERT INTO intest VALUES (42, 42, 42), (43, 42, 42), (44, 41, 44);
CREATE TABLE
INSERT 0 3

postgres=# SELECT * FROM intest WHERE a NOT IN (NULL, NULL)
postgres-# ;
 a | b | c 
---+---+---
(0 rows)

postgres=# SELECT * FROM intest WHERE NULL NOT IN ('a', 'b');
 a | b | c 
---+---+---
(0 rows)

Whereas in SuperDB, the entire table contents are returned

$ super -version
Version: e75dfdaf1

$ super -s intest 
{a:42::int32,b:42::int32,c:42::int32}
{a:43::int32,b:42::int32,c:42::int32}
{a:44::int32,b:41::int32,c:44::int32}

$ super -c "SELECT * FROM intest WHERE a NOT IN (NULL, NULL)"
{a:42::int32,b:42::int32,c:42::int32}
{a:43::int32,b:42::int32,c:42::int32}
{a:44::int32,b:41::int32,c:44::int32}

$ super -c "SELECT * FROM intest WHERE NULL NOT IN ('a', 'b');"
{a:42::int32,b:42::int32,c:42::int32}
{a:43::int32,b:42::int32,c:42::int32}
{a:44::int32,b:41::int32,c:44::int32}

While admittedly kind of weird examples, web searches show folks explaining the behavior as being related to NULL values in SQL representing "unknown" (i.e., of SQL's three logic states TRUE, FALSE, and UNKNOWN), so in a test for whether something is in a set of values of which some are unknown, the answer is also said to be unknown. Or to put it another way, NOT IN will return no rows if the list contains NULL. Similar logic (but flipped around) applies for the NULL NOT IN case.