Considering the test case below.
CREATE TABLE t0(c0 INTEGER);
CREATE TABLE t1(c0 INTEGER);
INSERT INTO t0(c0) VALUES (1);
SELECT * FROM t0 NATURAL LEFT JOIN t1; -- 1
SELECT CASE 1 WHEN t1.c0 THEN false ELSE true END FROM t0 NATURAL LEFT JOIN t1; -- <true>
SELECT * FROM t0 NATURAL LEFT JOIN t1 WHERE CASE 1 WHEN t1.c0 THEN false ELSE true END ;
-- Expected: 1
-- Actual: empty
The third SELECT returns an empty result, which is surprising: If the result of second query is true, the value of the CASE expression should be true, and thus the third query should return the row of the JOIN, that is 1, same as the first query.
I found this in version 6.0.0.168 where I built from source code cc8cb88 Things work well in v4 I think
Considering the test case below.
The third
SELECTreturns an empty result, which is surprising: If the result of second query istrue, the value of theCASEexpression should be true, and thus the third query should return the row of theJOIN, that is1, same as the first query.I found this in version 6.0.0.168 where I built from source code cc8cb88 Things work well in v4 I think