Unexpected result when using `%` with `NULL`

[suyZhong]

CrateDB version

CrateDB 5.7.0-SNAPSHOT built 571d6c6

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 case below. It is unexpected that the second query returns 0, because the result of the first query is 0, and the predicate (t0.c0%NULL)!=1 cannot be evaluated to TRUE or NULL at the same time. Thus, at least one of the two queries should return empty result.

Steps to Reproduce

DROP TABLE IF EXISTS t0;

CREATE TABLE t0( c0 INT);
INSERT INTO t0 (c0) VALUES (2);
REFRESH TABLE t0;

SELECT t0.c0 FROM t0; -- 2
SELECT t0.c0 FROM t0 WHERE ((t0.c0%NULL)!=1); -- 2 (unexpected)
SELECT t0.c0 FROM t0 WHERE (((t0.c0%NULL)!=1) IS NULL); -- 2

Actual Result

as mentioned above

Expected Result

as mentioned above

[jeeminso]

Thanks for reporting @suyZhong . The 3 bugs are all due to how we attempt to optimize not expressions but missed the case where the query is equivalent to null.