Wrong inner join result when handling join condition related to primary key #17380
Description
What happens?
Dear DuckDB Developers,
Please see the following cases. I meet a wrong inner join result.
CREATE TABLE t2(c0 DOUBLE, PRIMARY KEY(c0));
CREATE TABLE t1(c0 DOUBLE, PRIMARY KEY(c0));
-- two primary key is necessary
INSERT INTO t1(c0) VALUES (1);
INSERT INTO t1(c0) VALUES (0.2);
INSERT INTO t2(c0) VALUES (0.4);
INSERT INTO t2(c0) VALUES (6);
SELECT * FROM t2 INNER JOIN t1 ON ((t1.c0)<(t2.c0)) and (((t2.c0)OR(t2.c0)) IN (t1.c0));
-- empty result
-- wrong, should be 6.0, 1.0SQLite3 Result: https://www.db-fiddle.com/f/aoNzKXGSEaPxax8kYkrQ4K/0
To Reproduce
CREATE TABLE t2(c0 DOUBLE, PRIMARY KEY(c0));
CREATE TABLE t1(c0 DOUBLE, PRIMARY KEY(c0));
-- two primary key is necessary
INSERT INTO t1(c0) VALUES (1);
INSERT INTO t1(c0) VALUES (0.2);
INSERT INTO t2(c0) VALUES (0.4);
INSERT INTO t2(c0) VALUES (6);
SELECT * FROM t2 INNER JOIN t1 ON ((t1.c0)<(t2.c0)) and (((t2.c0)OR(t2.c0)) IN (t1.c0));
-- empty result
-- wrong, should be 6.0, 1.0OS:
MacOS
DuckDB Version:
v1.3.0-dev3051 9327663
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