Skip to content

Unexpected Results when Using CASE-WHEN with LEFT JOIN #7903

@suyZhong

Description

@suyZhong

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

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions