You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "wjjc1017 (via GitHub)" <gi...@apache.org> on 2026/10/23 01:55:44 UTC
[I] decorrelate_predicate_subquery removes inner LIMIT inside EXISTS even when a HAVING or WHERE above it depends on the row count [datafusion]
wjjc1017 opened a new issue, #26629:
URL: https://github.com/apache/datafusion/issues/26629
### Describe the bug
### Describe the bug
In `datafusion/optimizer/src/decorrelate.rs:276-309`, `PullUpCorrelatedExpr::f_down` reasons that "for `EXISTS`
only emptiness matters" and deletes any `Limit` with `SkipType::Literal(0)` and non-zero fetch:
```rust
match (limit.get_skip_type()?, fetch) {
(SkipType::Literal(0), FetchType::Literal(_)) => {
self.column_refs_above.pop();
let mut t = self.f_down((*limit.input).clone())?;
t.transformed = true;
Ok(t)
}
_ => self.stop_pull_up(plan),
}
```
Removing `LIMIT k` preserves emptiness only when the `Limit` is the **outermost** operator of the `EXISTS`
subquery. When the `LIMIT` sits *below* an `Aggregate` (`HAVING COUNT(*) > 1`) or a `Filter`
(`WHERE sub.v = 200`), deleting it changes the rows seen by the enclosing operator and the result is no longer
equivalent.
### To Reproduce
```sql
CREATE TABLE a(k INT, x INT) AS VALUES (1, 10), (2, 20);
CREATE TABLE b(k INT, v INT) AS VALUES (1, 100), (1, 200), (2, 300);
-- Case 1: LIMIT 1 below HAVING COUNT(*) > 1 (can never be > 1 -> must return 0 rows)
SELECT a.k FROM a
WHERE EXISTS (
SELECT 1 FROM (SELECT b.k, b.v FROM b WHERE b.k = a.k LIMIT 1) sub
HAVING COUNT(*) > 1
);
-- Case 2: LIMIT 1 below WHERE sub.v = 200 (first row for k=1 has v=100 -> must return 0 rows)
SELECT a.k FROM a
WHERE EXISTS (
SELECT 1 FROM (SELECT b.k, b.v FROM b WHERE b.k = a.k LIMIT 1) sub
WHERE sub.v = 200
);
```
### Expected behavior
- Case 1: expected `[]`; DataFusion `55.0.0` returns `[(1,)]`.
- Case 2: expected `[]`; DataFusion `55.0.0` returns `[(1,)]`.
The optimized plan shows the inner `Limit` gone while `HAVING COUNT(*) > 1` / `Filter b.v = 200` remain.
### Additional context
### Root cause
`decorrelate.rs:276-309` applies the "emptiness only" argument without checking the position of the `Limit`
relative to aggregates and filters. The condition must be: the `Limit` is the top-level operator of the `EXISTS`
subquery (or every operator between the `Limit` and the `EXISTS` root is row-count-preserving and cannot observe
the difference).
### Suggested fix
Only strip `LIMIT` when it is the root of the `EXISTS` subquery (possibly under row-preserving
`Projection`/`SubqueryAlias`). Otherwise stop pull-up (`stop_pull_up`) and decorrelate using the
`row_number()`-based limit decorrelation already implemented for correlated `LIMIT` (as used for scalar
subqueries). Also handle `LIMIT 0` explicitly: `EXISTS (... LIMIT 0)` is always false and must not return rows.
### Willingness to contribute
None
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] decorrelate_predicate_subquery removes inner LIMIT inside EXISTS even when a HAVING or WHERE above it depends on the row count [datafusion]
Posted by "jayzhan211 (via GitHub) via github" <gi...@datafusion.apache.org>.
jayzhan211 closed issue #26629: decorrelate_predicate_subquery removes inner LIMIT inside EXISTS even when a HAVING or WHERE above it depends on the row count
URL: https://github.com/apache/datafusion/issues/26629
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] decorrelate_predicate_subquery removes inner LIMIT inside EXISTS even when a HAVING or WHERE above it depends on the row count [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #26629:
URL: https://github.com/apache/datafusion/issues/26629#issuecomment-6479192632
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
- https://github.com/apache/datafusion/issues/5492#issuecomment-6479090126
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org