kita-renji opened a new issue, #25789:
URL: https://github.com/apache/datafusion/issues/25789
### Is your feature request related to a problem or challenge?
#25764 (which fixes #25507) stops `PullUpCorrelatedExpr` from pulling a
correlated filter out from under the side of an outer join that gets
NULL-extended, because that gave wrong results. Those subqueries now stay
correlated and fail with a not-implemented error:
```sql
CREATE TABLE o(k INT) AS VALUES (1), (5);
CREATE TABLE a(id INT) AS VALUES (1), (2);
CREATE TABLE b(id INT, y INT) AS VALUES (1, 1), (2, 2);
SELECT o.k,
EXISTS (SELECT 1
FROM a LEFT JOIN (SELECT * FROM b WHERE b.y = o.k) AS b
ON a.id = b.id
WHERE b.y IS NULL) AS e
FROM o ORDER BY k;
-- expected (DuckDB): (1, true), (5, true)
-- now: This feature is not implemented: Physical plan does not support
logical expression Exists(...)
```
The same applies to `IN`, `WHERE [NOT] EXISTS`, scalar subqueries, the left
side of a `RIGHT JOIN`, either side of a `FULL JOIN`, a correlated filter
nested under an inner join on the nullable side, and `LATERAL` subqueries
(which fail with the `OuterReferenceColumn` not-implemented error). The
sqllogictest cases added in #25764 (`subquery.slt`, `lateral_join.slt`) cover
all of these.
### Describe the solution you'd like
Run these queries and return the right rows. Since the filter cannot move
above the join, the outer relation has to be brought down to the filter: join
the distinct outer keys into the nullable input, add the outer key columns to
the join condition, and group or semi-join on them above. This is the dependent
join approach from Neumann and Kemper, "Unnesting Arbitrary Queries".
DuckDB 1.5.5 returns the right results for the LEFT and RIGHT cases. For the
FULL case it fails with "Unsupported join type for flattening correlated
subquery".
### Describe alternatives you've considered
Keep the not-implemented error. It no longer returns wrong rows, but these
are valid queries that DuckDB runs.
### Additional context
- Relevant code: `PullUpCorrelatedExpr` in
`datafusion/optimizer/src/decorrelate.rs`; the guard added in #25764 is the
`LogicalPlan::Join` arm in `f_down`.
- #25284 (`Limit`) and #25529 (grouping sets) mark other shapes as
unsupported in the same way.
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
