Unexpected result when using `INNER JOIN` and `CASE` in filter

[suyZhong]

CrateDB version

CrateDB 5.7.0-SNAPSHOT built 2891ea8

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 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 t0;
DROP TABLE IF EXISTS t1;

CREATE TABLE t0(c0 INTEGER);
CREATE TABLE t1(c0 VARCHAR, c1 BOOLEAN);
INSERT INTO t0 (c0) VALUES (0);
INSERT INTO t1 (c1, c0) VALUES (true, 'a');
REFRESH TABLE t0;
REFRESH TABLE t1;


SELECT * FROM t1 INNER  JOIN t0 ON (t1.c1 = t1.c1); -- 'a' TRUE 0
SELECT  (CASE 'q' WHEN t1.c0 THEN false ELSE (t0.c0 IN (t0.c0)) END ) FROM t1 INNER  JOIN t0 ON (t1.c1 = t1.c1); -- TRUE
SELECT * FROM t1 INNER  JOIN t0 ON (t1.c1 = t1.c1) WHERE (CASE 'q' WHEN t1.c0 THEN false ELSE (t0.c0 IN (t0.c0)) END ); 
-- Expected: 'a' TRUE 0
-- Actual:   empty result

Actual Result

Empty table

Expected Result

'a' TRUE 0

[jeeminso]

Thank you alot for reporting @suyZhong ! The fix will be available in 5.6.3 release.