Unexpected result when using CASE statement

[suyZhong]

CrateDB version

5.5.0 docker image

CrateDB setup information

-Cdiscovery.type=single-node

Problem description

The third SELECT returns an empty result, which is surprising, given that the result of the second query is TRUE. The value of the CASE expression should always be true, and thus the third query should return the whole table.

Steps to Reproduce

CREATE TABLE t1 (c0 INT);
INSERT INTO t1 VALUES (NULL);

SELECT * FROM t1; -- NULL
SELECT (CASE WHEN 1 THEN 2 ELSE c0 END )!=1 FROM t1; -- TRUE
SELECT * FROM t1 WHERE (CASE WHEN 1 THEN 2 ELSE c0 END )!=1; 

Actual Result

Empty table

Expected Result

NULL

[matriv]

The following works:

SELECT * FROM t1 WHERE (CASE WHEN 1 THEN 2 ELSE c0 END ) = 2;

so it seems to be something wrong with Not or NotEquals operator

[mfussenegger]

Thanks for the report. This should get fixed with the next hotfix release (#15040)