Unexpected Results when Querying with NULL values

[suyZhong]

Considering the test case below:

CREATE TABLE t2(c0 INT, PRIMARY KEY(c0))
INSERT INTO t2(c0) VALUES (1);

SELECT * FROM t2; -- 1
SELECT (c0 IN (NULL >= 1)) IS NULL FROM t2; -- 1
SELECT * FROM t2 WHERE ((c0 IN (NULL >= 1)) IS NULL); 
-- Expected: 1
-- Actual: empty

The third SELECT returns an empty result, which is surprising: If the result of second query is 1 (TRUE), the value of the IN expression should be true, and thus the third query should return the row in t2.

These test cases works well in MySQL 8.0.33, however not in dolt.

I originally find this by building dolt from source version 4cffade. It could also be reproduced in 1.26.1. I haven't tried on the PR that has not merged yet.

-- We also found similar cases as below
SELECT (c0 IN (NULL NOT BETWEEN -1 AND 1)) IS NULL FROM t2; -- 1
SELECT * FROM t2 WHERE (c0 IN (NULL NOT BETWEEN -1 AND 1)) IS NULL; -- empty

SELECT (c0 IN (NULL * 1)) IS NULL FROM t2; -- 1
SELECT * FROM t2 WHERE ((c0 IN (NULL * 1)) IS NULL); -- empty

Kindly inform me if these are caused by different reasons.

[max-hoffman]

These all look related to our hash map IN executor, so probably a bug there:

tmp4> explain SELECT (c0 IN (NULL NOT BETWEEN -1 AND 1)) IS NULL FROM t2;
+--------------------------------------------------------------------+
| plan                                                               |
+--------------------------------------------------------------------+
| Project                                                            |
|  ├─ columns: [(t2.c0 IN ((NOT((NULL BETWEEN -1 AND 1))))) IS NULL] |
|  └─ Table                                                          |
|      ├─ name: t2                                                   |
|      └─ columns: [c0]                                              |
+--------------------------------------------------------------------+
5 rows in set (0.01 sec)

tmp4> explain SELECT * FROM t2 WHERE (c0 IN (NULL NOT BETWEEN -1 AND 1)) IS NULL;
+------------------------------------+
| plan                               |
+------------------------------------+
| Filter                             |
|  ├─ (t2.c0 HASH IN (NULL)) IS NULL |
|  └─ Table                          |
|      ├─ name: t2                   |
|      └─ columns: [c0]              |
+------------------------------------+
5 rows in set (0.00 sec)