adriangb opened a new pull request, #25338: URL: https://github.com/apache/datafusion/pull/25338
## Which issue does this PR close? There is no issue for this change. This PR supersedes https://github.com/apache/datafusion/pull/21363 by @crm26. That PR got a review round that said the change looked good and asked for a rebase. The stale bot closed it on 2026-07-31 before the rebase landed. The original work is kept here as @crm26's commit. Two more commits add a test adjustment and extra test coverage. ## Rationale for this change `DecorrelatePredicateSubquery` only rewrote `IN` and `EXISTS` subqueries in a `WHERE` clause. The same subquery in a `SELECT` list was left in the logical plan. The physical planner cannot plan such an expression, so planning fails. This is what you get on current `main`: ```sql CREATE TABLE t1(id INT) AS VALUES (1),(2); CREATE TABLE t2(id INT) AS VALUES (1); SELECT id, id IN (SELECT id FROM t2) AS m FROM t1; ``` ``` This feature is not implemented: Physical plan does not support logical expression InSubquery(...) ``` | Query | Before | After | | --- | --- | --- | | `SELECT id, id IN (SELECT id FROM t2) AS m FROM t1` | `Physical plan does not support logical expression InSubquery(...)` | `1 true` and `2 false` | The error occurs for a bare boolean column, for a `CASE` expression and for `COALESCE`. We hit this in production with this shape: ```sql COALESCE((x IN (SELECT ...))::boolean, false) AS flag ``` Both PostgreSQL and DuckDB run these queries. ## What changes are included in this PR? The rule gets a `LogicalPlan::Projection` arm. It mirrors the projection handler in `ScalarSubqueryToJoin`, and it reuses the existing `rewrite_inner_subqueries` and `mark_join` helpers. - Each predicate subquery in a projection expression becomes a `LeftMark` join. The `mark` column of the join replaces the subquery predicate in the expression. - Output column names stay the same. The rule adds an alias when the rewrite changes the name of an expression. - The rule bails out for the whole projection if any subquery in it cannot be decorrelated. One example is a correlated subquery with `LIMIT`. The plan is then unchanged, and you get the same error as today for that query. ### What changed since the closed PR - Rebase on current `main` at `add66e424f`. - 7 snapshot lines now show `mark:Boolean;N`, because https://github.com/apache/datafusion/pull/21585 made the mark column nullable. - The author of the closed PR hit a blocker after the last rebase. Two subqueries in separate `SELECT` columns gave `Schema contains duplicate unqualified field name mark` in `optimize_projections`. This no longer occurs on current `main`. New sqllogictest cases cover that shape. - The regression test for https://github.com/apache/datafusion/pull/24574 in `datafusion/sqllogictest/test_files/projection_pushdown.slt` needed a change. That test relied on a `SELECT` list `IN` subquery that stayed in the plan. The query is now a correlated subquery with `LIMIT 1`. The rule cannot pull that subquery up, so it still reaches `ExtractLeafExpressions` and the alias generator still starts at 2. The unit test in `extract_leaf_expressions.rs` also guards that fix. ### Three-valued logic For a hashable `IN` or `NOT IN`, the `LeftMark` join is null aware. This came from https://github.com/apache/datafusion/pull/21585. So `SELECT x IN (SELECT ...)` gives `NULL` if `x` is `NULL` or if the subquery has a `NULL` value. This agrees with DuckDB and PostgreSQL. The new sqllogictest cases assert this. ### Known limitation If the correlation is a non-equality predicate, it becomes a residual join filter. Mark joins are not null aware in that case. So this query gives `false` instead of `NULL` for a `NULL` value of `x`: ```sql SELECT x IN (SELECT y FROM t WHERE t.z < outer.z) FROM outer; ``` This behaviour already exists in the `Filter` path. It is tracked in https://github.com/apache/datafusion/issues/25336, which also records a wrong-results case for `NOT IN` on `main`. This PR does not change that behaviour. ### Out of scope These cases are unchanged and still give an error: - A predicate subquery in an argument of an aggregate function. - A predicate subquery in a window `PARTITION BY` clause. - An `IN` subquery whose outer reference is two query levels up. The reviewer of the original PR suggested a rename of the rule, because the name `DecorrelatePredicateSubquery` no longer describes what it does. That is left for a separate PR. ## What is the testing strategy for this PR? - 9 unit tests in `datafusion/optimizer/src/decorrelate_predicate_subquery.rs`. They cover the plan shapes for `IN`, `NOT IN` and `EXISTS`, the correlated case, a projection with no subquery, the bail-out case, and a projection that mixes a decorrelatable subquery with one that cannot be decorrelated. - A new sqllogictest file `datafusion/sqllogictest/test_files/in_subquery_projection.slt`. It has the 8 original cases plus a new section. The new section covers several subqueries across separate columns, an `EXPLAIN` of the stacked mark joins, the `NULL` semantics, a projection over an aggregate, and the `COALESCE` and cast shape from the report above. - The negative test in `datafusion/sqllogictest/test_files/predicates.slt` now asserts a result instead of an error. - The full sqllogictest suite and the optimizer crate tests pass locally. ## Are there any user-facing changes? Yes. Queries with an `IN` or `EXISTS` subquery in a `SELECT` list now run. Queries that failed before now give a result. There is no API change and no breaking change. There are no docs to update. 🤖 Generated with [Claude Code](https://claude.com/claude-code) -- 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]
