Skip to content

Unexpected results when the join condition contains the OR predicate #7908

@suyZhong

Description

@suyZhong

Considering the test case below.

CREATE TABLE t1(c0 BOOLEAN);
CREATE TABLE t0(c0 BOOLEAN, c1 BOOLEAN);
CREATE UNIQUE INDEX i0 ON t0(c1 , c0 );
INSERT INTO t0 (c0) VALUES (false);
INSERT INTO t1 (c0) VALUES (false);

SELECT * FROM t1, t0; -- false, false, null
SELECT NOT (false AND ((t1.c0 <= false) NOT IN (t0.c1))) FROM t1, t0; -- true
SELECT * FROM t1, t0 WHERE (NOT (false AND ((t1.c0 <= false) NOT IN (t0.c1))));
-- Expected: false, false, null
-- Actual: empty

The third SELECT returns an empty result, which is surprising: If the result of second query is true, the value of the NOT expression should be true, and thus the third query should return the row of the table after JOIN, that is false, false, null, same as the first query.

I found this in version LI-T6.0.0.170 where I built from source code 3dc03dd. Things work fine in v4.

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions