wrong result: NOT EXISTS predicate with correlated non-equality comparison #9308
Description
What happens?
A query with a correlated non-equality comparison in WHERE NOT EXISTS is not returned by the outer SELECT despite matching no rows in the correlated subquery. See reproduction.
To Reproduce
create or replace table t1(c1 int64);
insert into t1 values (1);
create or replace table t2(c1 int64);
-- shows that the "not exists" expression evaluates to true
select c1, not exists (select 1 from t2 where t1.c1 <= t2.c1) from t1;
-- 0 rows appear in the output
select c1 from t1 where not exists (select 1 from t2 where t1.c1 <= t2.c1);
The first SELECT produces:
┌───────┬───────────────────────────────────────────────────────┐
│ c1 │ (NOT EXISTS(SELECT 1 FROM t2 WHERE (t1.c1 <= t2.c1))) │
│ int64 │ boolean │
├───────┼───────────────────────────────────────────────────────┤
│ 1 │ true │
└───────┴───────────────────────────────────────────────────────┘
showing that the NOT EXISTS evaluates to true
The 2nd select results in:
┌────────┐
│ c1 │
│ int64 │
├────────┤
│ 0 rows │
└────────┘
I believe this should return the 1 row.
OS:
aarch64
DuckDB Version:
0.9.1, main branch (downloaded Oct 11, 2023)
DuckDB Client:
CLI
Full Name:
Steven Pelley
Affiliation:
independent
Have you tried this on the latest main branch?
I have tested on the latest main branch
Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?
- Yes, I have