Open
Description
What happens?
Dear DuckDB developers,
I meet unexpected result of two equivalent anti-join queries, and I will appreciate it if you can review it.
Additionally, if t2 is not a empty table, this bug can not be triggered.
CREATE TABLE t0(c0 INT);
CREATE TABLE t2(c0 INT);
INSERT INTO t0(c0) VALUES (5);
SELECT * FROM t0 WHERE NOT EXISTS (select 1 FROM t2 WHERE ((t2.c0)>=(t0.c0)));
-- 5
SELECT * FROM t0 ANTI JOIN t2 ON ((t2.c0)>=(t0.c0));
-- empty
-- wrong result, should be 5
-- Query Plan of Q2 is almost nothing.
┌─────────────────────────────┐
│┌───────────────────────────┐│
││ Physical Plan ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│ EMPTY_RESULT │
└───────────────────────────┘
To Reproduce
CREATE TABLE t0(c0 INT);
CREATE TABLE t2(c0 INT);
INSERT INTO t0(c0) VALUES (5);
SELECT * FROM t0 WHERE NOT EXISTS (select 1 FROM t2 WHERE ((t2.c0)>=(t0.c0)));
-- 5
SELECT * FROM t0 ANTI JOIN t2 ON ((t2.c0)>=(t0.c0));
-- empty
-- wrong result, should be 5
Query Plan of Q2 is almost nothing.
┌─────────────────────────────┐
│┌───────────────────────────┐│
││ Physical Plan ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│ EMPTY_RESULT │
└───────────────────────────┘
OS:
MacOS
DuckDB Version:
v1.3.0-dev3259 e0e4cc2
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