Unexpected result when using string comparison AND IS NULL

[suyZhong]

CrateDB version

CrateDB 5.6.0-SNAPSHOT built f6f4067

CrateDB setup information

Manual build following the instructions here: https://github.com/crate/crate/blob/master/devs/docs/basics.rst

Problem description

Consider the test cases below. It is unexpected that the third query returns empty result, since the second query returns TRUE, which means that the WHERE clause in the third query should be evaluated to TRUE and thus return the row in the table.

Steps to Reproduce

DROP TABLE IF EXISTS t1;

CREATE TABLE t1(c0 VARCHAR, PRIMARY KEY(c0));
INSERT INTO t1 (c0) VALUES ('a');
REFRESH TABLE t1;

SELECT * FROM t1; -- 'a'
SELECT NOT ((t1.c0 != '1' )AND (t1.c0 IS NULL)) FROM t1; -- TRUE
SELECT * FROM t1 WHERE NOT ((t1.c0 != '1' )AND (t1.c0 IS NULL));
-- Expected: 'a'
-- Actual:   empty result

Actual Result

Empty

Expected Result

'a'

[matriv]

Also exists in 5.5.2.

[jeeminso]

Thanks a lot for reporting, @suyZhong!

SELECT * FROM t1 WHERE NOT ((t1.c0 != '1' )AND (t1.c0 IS NULL)); is equivalent to SELECT * FROM t1 WHERE NOT (t1.c0 IS NULL); but the former uses get (invalid optimization from collect attempting to utilize the primary key in the where clause) while latter uses collect operator:

cr> EXPLAIN SELECT * FROM t1 WHERE NOT ((t1.c0 != '1' )AND (t1.c0 IS NULL)); 
                                                                                                                 
+--------------------------------------------------------------------------------------+
| QUERY PLAN                                                                           |
+--------------------------------------------------------------------------------------+
| Get[doc.t1 | c0 | DocKeys{'1'} | (NOT ((NOT (c0 = '1')) AND (c0 IS NULL)))] (rows=1) |
+--------------------------------------------------------------------------------------+

cr> EXPLAIN SELECT * FROM t1 WHERE NOT ((t1.c0 IS NULL)); 
                                                                                                                 
+------------------------------------------------------------+
| QUERY PLAN                                                 |
+------------------------------------------------------------+
| Collect[doc.t1 | [c0] | (NOT (c0 IS NULL))] (rows=unknown) |
+------------------------------------------------------------+

[jeeminso]

Thanks again for reporting @suyZhong , this is fixed by #15401.

[matriv]

The fix causes issues with outer joins and it's being reverted with: #15468

[jeeminso]

The fix causes issues with outer joins and it's being reverted with: #15468

Thank you for handling the revert @matriv. For the outer join scenario, the arg to is null was actually core.users_roles.user_id which I think could been some kind of a ScopedSymbol(or AliasSymbol?) such that it would be prevented from is null normalization. WDYT? If sounds feasible, this could be something to look into during feature freeze.

[matriv]

Thank you for handling the revert @matriv. For the outer join scenario, the arg to is null was actually core.users_roles.user_id which I think could been some kind of a ScopedSymbol(or AliasSymbol?) such that it would be prevented from is null normalization. WDYT? If sounds feasible, this could be something to look into during feature freeze.

We should not optimize/normalize the is null/is not null of a where clause, when it acts on a column of the table on the "outer" side of the join, I don't think AliasSymbol or so is enough to catch that.

[jeeminso]

Fixed by #15487.