Is your feature request related to a problem or challenge?
Follow-up to #25529 (fix for #25519).
To stop wrong results, #25529 keeps a correlated filter below an aggregate with a grouping set unless every set already groups by each column the pull up would add. That guard is conservative: it also rejects non-empty sets that leave out the correlated column. On main those queries were answered correctly, and with #25529 they fail to plan:
CREATE TABLE o(k INT) AS VALUES (1), (2), (NULL), (4), (5);
CREATE TABLE i(k INT, j INT) AS VALUES (1, 10), (NULL, 20), (5, 30), (2, 40);
SELECT o.k FROM o
WHERE EXISTS (
SELECT 1 FROM i WHERE i.k = o.k
GROUP BY GROUPING SETS ((i.k), (i.j))
)
ORDER BY o.k;
-- with #25529: This feature is not implemented: Physical plan does not support logical expression Exists(...)
Because the filter i.k = o.k fixes i.k to one value per outer row, adding i.k to a non-empty set that lacks it leaves the rows of each outer row unchanged. Only two things change: the value of i.k in those rows (NULL becomes o.k) and __grouping_id. So the old rewrite was wrong only when something above the aggregate reads the NULL-filled column or GROUPING(). One such case is HAVING i.k IS NULL, which is covered in subquery.slt.
Describe the solution you'd like
Decorrelate these queries again without bringing back the wrong results. Two options:
- Add the correlated column to each set that lacks it under an alias, so the column the query reads keeps its NULL fill and the join key is a separate column.
- Reject only when a set is empty (
ROLLUP/CUBE always contain one) or when a node above the aggregate reads the NULL-filled column or GROUPING().
Empty sets must stay rejected: they yield a row for outer rows that match nothing, and a join cannot produce that row.
Describe alternatives you've considered
Keep the current guard. The queries fail to plan instead of returning wrong results.
Additional context
The guard is in the Aggregate arm of PullUpCorrelatedExpr::f_up, datafusion/optimizer/src/decorrelate.rs. The known limitation is documented in datafusion/sqllogictest/test_files/subquery.slt.
Is your feature request related to a problem or challenge?
Follow-up to #25529 (fix for #25519).
To stop wrong results, #25529 keeps a correlated filter below an aggregate with a grouping set unless every set already groups by each column the pull up would add. That guard is conservative: it also rejects non-empty sets that leave out the correlated column. On
mainthose queries were answered correctly, and with #25529 they fail to plan:Because the filter
i.k = o.kfixesi.kto one value per outer row, addingi.kto a non-empty set that lacks it leaves the rows of each outer row unchanged. Only two things change: the value ofi.kin those rows (NULL becomeso.k) and__grouping_id. So the old rewrite was wrong only when something above the aggregate reads the NULL-filled column orGROUPING(). One such case isHAVING i.k IS NULL, which is covered insubquery.slt.Describe the solution you'd like
Decorrelate these queries again without bringing back the wrong results. Two options:
ROLLUP/CUBEalways contain one) or when a node above the aggregate reads the NULL-filled column orGROUPING().Empty sets must stay rejected: they yield a row for outer rows that match nothing, and a join cannot produce that row.
Describe alternatives you've considered
Keep the current guard. The queries fail to plan instead of returning wrong results.
Additional context
The guard is in the
Aggregatearm ofPullUpCorrelatedExpr::f_up,datafusion/optimizer/src/decorrelate.rs. The known limitation is documented indatafusion/sqllogictest/test_files/subquery.slt.