Skip to content

Unexpected Results when Using NATURAL LEFT JOIN #13572

Description

@suyZhong

Describe the bug

Consider the following test case. It is unexpected that the second query return 0, since the first query without WHERE condition has a result NULL.

Error message/log

No response

To Reproduce

CREATE TABLE t0(c0 INT, c1 INT, c2 INT, PRIMARY KEY(c0));
CREATE TABLE t1(c0 INT, c1 INT, PRIMARY KEY(c0));

INSERT INTO t0(c0, c1, c2) VALUES (1, 1,  1);
INSERT INTO t1(c0) VALUES (2);

SELECT c2 FROM t1 NATURAL LEFT JOIN t0; -- NULL
SELECT c2 FROM t1 NATURAL LEFT JOIN t0 WHERE (c1 IS NULL); -- 0

Expected behavior

I expected the second query to also return the same as the first one.

How did you deploy RisingWave?

via Docker image and online playground

The version of RisingWave

dev=> SELECT version();
                                     version                                      
----------------------------------------------------------------------------------
 PostgreSQL 9.5-RisingWave-1.3.0-alpha (4313a5a8fe3a11a0edaa3ff8f4fdb639123be618)

Additional context

No response

Metadata

Metadata

Assignees

Labels

type/bugType: Bug. Only for issues.

Type

No type

Projects

No projects

Milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions