Wrong results: a correlated filter under an aggregate with a grouping set is pulled above the aggregate

[adriangb]

Describe the bug

A correlated subquery whose filter sits below an aggregate with a grouping set gives wrong results after decorrelation. PullUpCorrelatedExpr moves the correlated filter above the aggregate and adds the correlated column to every grouping set. ROLLUP(i.k) is GROUPING SETS ((i.k), ()); after the pull up it is GROUPING SETS ((i.k), (i.k, i.k)). The empty grouping set is gone, and with it the grand-total row that the correlated subquery gives for every outer row, also when the filter matches nothing.

EXISTS and IN are both wrong. EXISTS (SELECT 1 FROM i WHERE i.k = o.k GROUP BY ROLLUP(i.k)) is true for every outer row, because the grand-total row always exists. DataFusion gives false when no i.k matches.

There is no error and no warning. The aggregate has no aggregate expressions, so is_distinct = aggregate.aggr_expr.is_empty() in decorrelate.rs treats it as a DISTINCT and keeps can_pull_up set.

To Reproduce

CREATE TABLE o(k INT) AS VALUES (1), (2), (NULL), (4), (5);
CREATE TABLE i(k INT) AS VALUES (1), (NULL), (5), (2);

SELECT o.k, EXISTS (SELECT 1 FROM i WHERE i.k = o.k GROUP BY ROLLUP(i.k)) AS e FROM o ORDER BY o.k;
SELECT o.k, o.k IN (SELECT i.k FROM i WHERE i.k = o.k GROUP BY ROLLUP(i.k)) AS m FROM o ORDER BY o.k;

datafusion-cli on main at 64871d9:

o.k	EXISTS, DataFusion	EXISTS, correct	IN, DataFusion	IN, correct
1	true	true	true	true
2	true	true	true	true
4	false	true	false	NULL
5	true	true	true	true
NULL	false	true	false	NULL

DuckDB 1.5.2 and PostgreSQL give the "correct" columns. For o.k = 4 the correlated subquery result is {NULL}: the grand-total row, with i.k rolled up to NULL. So EXISTS is true and 4 IN {NULL} is UNKNOWN.

The plan for the EXISTS query on main. The grouping set () has become (i.k, i.k):

Projection: o.k, __correlated_sq_1.mark AS e
  LeftMark Join: o.k = __correlated_sq_1.k
    TableScan: o projection=[k]
    SubqueryAlias: __correlated_sq_1
      Projection: i.k
        Aggregate: groupBy=[[GROUPING SETS ((i.k), (i.k, i.k))]], aggr=[[]]
          TableScan: i projection=[k]

Expected behavior

The results in the "correct" columns above. If the pull up cannot keep the per-row semantics of a grouping set, it should refuse to decorrelate the subquery with the unsupported() helper that #25284 adds (can_pull_up = false), instead of giving a wrong result.

Additional context

Same class, a correlated filter pulled above a node that changes the row set:

• Wrong results: a correlated filter under the nullable side of an outer join is pulled above the join #25507: the nullable side of an outer join.
• Correlated EXISTS/IN subqueries with groupless aggregates hit the count bug #24960 and fix: correlated exists/not exists subqueries hit the count bug for groupless aggregates #25391: a groupless aggregate (the count bug).
• Correlated EXISTS subquery with OFFSET returns wrong results #25283 and fix: keep the OFFSET of a correlated EXISTS subquery #25284: OFFSET.
• Wrong results: correlated NOT IN loses the correlation when it is an equality on the IN value column #25480: the correlation repeats the IN predicate and is dropped.

#25338 had a guard that made only the IN form of this query return NULL. It removes that guard again, so that the rule does not carry a list of plan nodes that can put a NULL back into a column, and this issue tracks the fix in the pull up instead.

[namanjain24-sudo]

take