Wrong results: correlated EXISTS/IN subqueries over a groupless aggregate get the wrong outer rows

Describe the bug

A correlated EXISTS or IN subquery containing a groupless aggregate (e.g. count(*), with no GROUP BY) returns incorrect results when the outer row has 0 matches on the subquery.

A groupless aggregate always produces exactly one output row, even over zero input rows. The current decorrelation instead drops the outer row entirely, as if the subquery had produced no rows at all.

To Reproduce

CREATE TABLE t1(t1_int int); INSERT INTO t1 VALUES (1),(2),(3);
CREATE TABLE t2(t2_int int); INSERT INTO t2 VALUES (2);

SELECT t1.t1_int FROM t1 WHERE EXISTS (
    SELECT count(*) FROM t2 WHERE t1.t1_int = t2.t2_int
) ORDER BY 1;

Expected behavior

The query above should return all three rows: 1, 2, 3.

For t1_int=1 and t1_int=3, no row in t2 matches, so the inner count(*) evaluates to 0 rather than producing no row at all. A 0 is still a row, so EXISTS should be true for those two outer rows too.

Currently, DataFusion returns a single row, 2, silently dropping the two rows whose count(*) evaluated to 0.

The NOT EXISTS variant of the same query surfaces the same underlying bug. It should return no rows (since EXISTS is true for every row) but instead returns 1, 3.

Additional context

Same bug class referenced in #10553 and #15032/#15281.

fix: correlated exists/not exists subqueries hit the count bug for gr…

On main (ebf4858) the IN/NOT IN forms still return wrong rows rather than a "not implemented" error:

CREATE TABLE t1(a INT) AS VALUES (1),(2);
CREATE TABLE t2(a INT, b INT) AS VALUES (1,10),(1,NULL);
SELECT a FROM t1 WHERE 0 IN (SELECT count(*) FROM t2 WHERE t2.a = t1.a);      -- returns no rows, expected 2
SELECT a FROM t1 WHERE 0 NOT IN (SELECT count(*) FROM t2 WHERE t2.a = t1.a);  -- returns 1, 2, expected 1
SELECT a, 0 IN (SELECT count(*) FROM t2 WHERE t2.a = t1.a) FROM t1;           -- a = 2 gives false, expected true

The IN path never turns on the count bug handling (decorrelate_predicate_subquery.rs, build_join runs the pull up with it on only for EXISTS).

fix: reject correlated IN/NOT IN over a groupless aggregate (apache#2…

When fixing this bug, I think we should make the query error rather than trying to make it work / give correct results. To make it work and have confidence it will be correct, I think we need a more general approach than patches. See here for details

[dbolympics] General framework to decorrelate the subqueries #5492 (comment)

Checked on current main (14aa185):

The EXISTS / NOT EXISTS repro in the description is correct now (1, 2, 3 and no rows, same as DuckDB 1.5.6)
The three IN queries from Wrong results: correlated EXISTS/IN subqueries over a groupless aggregate get the wrong outer rows #24960 (comment) now fail with Physical plan does not support logical expression InSubquery instead of returning wrong rows

One shape still returns wrong rows, a HAVING that reads both the count and an outer column:

CREATE TABLE t1(t1_int INT) AS VALUES (1), (2), (3);
CREATE TABLE t2(t2_int INT) AS VALUES (2);
SELECT t1_int FROM t1 WHERE EXISTS (SELECT count(*) FROM t2 WHERE t1.t1_int = t2.t2_int HAVING count(*) + t1.t1_int > 1);
-- main: 2, DuckDB: 2, 3
SELECT t1_int FROM t1 WHERE NOT EXISTS (SELECT count(*) FROM t2 WHERE t1.t1_int = t2.t2_int HAVING count(*) + t1.t1_int > 1);
-- main: 1, 3, DuckDB: 1

It looks related to #26695, but that one is about a HAVING on outer columns only, so I'd keep this open for now.

Navigation Menu

Repository navigation

Wrong results: correlated `EXISTS`/`IN` subqueries over a groupless aggregate get the wrong outer rows #24960

Describe the bug

To Reproduce

Expected behavior

Additional context

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Wrong results: correlated EXISTS/IN subqueries over a groupless aggregate get the wrong outer rows #24960

Description

Describe the bug

To Reproduce

Expected behavior

Additional context

Activity

jayzhan211 commented on Oct 23, 2026

alamb commented on Oct 28, 2026

raghavyadav01 commented on Oct 29, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Wrong results: correlated `EXISTS`/`IN` subqueries over a groupless aggregate get the wrong outer rows #24960