Unexpected result when querying partitioned table with null values

[suyZhong]

CrateDB version

CrateDB 5.9.0-SNAPSHOT built dcbd23f

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 last query returns only one row because the result of the second query has two rows of TRUE, which means that the WHERE clause in the last query should be evaluated to TRUE and thus return all the rows in the table.

I assume that the partitioned table is like a regular table? as mentioned in https://cratedb.com/docs/crate/reference/en/latest/general/ddl/partitioned-tables.html

Steps to Reproduce

DROP TABLE IF EXISTS t0;

CREATE  TABLE  t0(c0 VARCHAR, c1 STRING) PARTITIONED BY (c0);
INSERT INTO t0(c0) VALUES ('');
INSERT INTO t0(c0, c1) VALUES (NULL, NULL);
REFRESH TABLE t0;

SELECT * FROM t0;-- NULL NULL， ‘’ NULL
SELECT ((t0.c0!=t0.c0)OR(t0.c1='a')) IS NULL FROM t0; -- TRUE, TRUE
SELECT * FROM t0 WHERE ((t0.c0!=t0.c0)OR(t0.c1='a')) IS NULL;
-- Expected:  NULL NULL， ‘’ NULL (2 rows)
-- Actual:  ‘’ NULL (1 row)

Actual Result

1 row

Expected Result

2 rows

[matriv]

The behaviour is the same (wrong) also for a normal table, it doesn't have to be partitioned

This is wrong, indeed it only happens for partitioned tables.

[matriv]

thank you @suyZhong for reporting this issue! The fix has been merged and will be available with the next hotfix release.