Wrong multiple join result #17372
Description
What happens?
Hi, DuckDB developers, I perform the following multiple join but it meets the wrong result.
CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT);
CREATE TABLE t2(c0 INT);
INSERT INTO t2(c0) VALUES (3);
INSERT INTO t1(c0) VALUES (2);
INSERT INTO t0(c0) VALUES (1);
SELECT * FROM t2, t0 INNER JOIN LATERAL (SELECT t0.c0 AS col_0, t2.c0 AS col_1) as subQuery1 ON ((subQuery1.col_0)<=(0)) right join t1 on true;
-- empty result
-- wrong result, PostgreSQL produces 3 ,null, null, null, 2
PostgreSQL Result: https://www.db-fiddle.com/f/xbboDqVU5K32euELUcZd6W/0
To Reproduce
CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT);
CREATE TABLE t2(c0 INT);
INSERT INTO t2(c0) VALUES (3);
INSERT INTO t1(c0) VALUES (2);
INSERT INTO t0(c0) VALUES (1);
SELECT * FROM t2, t0 INNER JOIN LATERAL (SELECT t0.c0 AS col_0, t2.c0 AS col_1) as subQuery1 ON ((subQuery1.col_0)<=(0)) right join t1 on true;
-- empty result
-- wrong result, PostgreSQL produces 3 ,null, null, null, 2
OS:
MacOS
DuckDB Version:
v1.3.0-dev3013 a56da2c
DuckDB Client:
CLI
Hardware:
No response
Full Name:
TheoristCoder
Affiliation:
NUS
What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.
I have tested with a nightly build
Did you include all relevant data sets for reproducing the issue?
Yes
Did you include all code required to reproduce the issue?
- Yes, I have
Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?
- Yes, I have