You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "comphead (via GitHub)" <gi...@apache.org> on 2026/10/04 16:20:09 UTC
[I] Correlated `IN` subquery fails with "Projections require unique expression names" when its column is cast [datafusion]
comphead opened a new issue, #26033:
URL: https://github.com/apache/datafusion/issues/26033
### Describe the bug
A correlated `IN` subquery fails to plan when its output column is cast, for example because the `IN` value has a different type. The query is valid, and DuckDB runs it.
### To Reproduce
```sql
CREATE TABLE o(k INT, b BIGINT) AS VALUES (1, 1), (2, 2), (3, 30);
CREATE TABLE i(y INT) AS VALUES (1), (2), (3);
SELECT o.k FROM o WHERE o.b IN (SELECT i.y FROM i WHERE i.y = o.k) ORDER BY o.k;
```
```
Optimizer rule 'decorrelate_predicate_subquery' failed
caused by
Error during planning: Projections require unique expression names but the expression "CAST(i.y AS Int64)" at position 0 and "i.y" at position 1 have the same name. Consider aliasing ("AS") one of them.
```
The same error happens for `1 IN (...)`, `o.k + 0 IN (...)`, `NOT IN`, and an explicit `SELECT CAST(i.y AS BIGINT)` in the subquery. With `SELECT i.y + 0 AS y`, the error is `Schema contains qualified field name i.y and unqualified field name y which would be ambiguous`.
It works when no cast is needed, as in `o.k IN (SELECT i.y FROM i WHERE i.y = o.b)`, when the subquery is not correlated, or when the cast has another name (`CAST(i.y AS BIGINT) AS y2`).
### Expected behavior
`1`, `2`, which DuckDB 1.1.2 returns.
### Additional context
`PullUpCorrelatedExpr` adds the correlated column `i.y` to the projection of the subquery, so that the join can read it. It skips the column only when the projection already holds the exact expression `i.y`:
https://github.com/apache/datafusion/blob/c3ef346887412eeaeef644809c8fcfaf782f94e4/datafusion/optimizer/src/decorrelate.rs#L679-L695
Here the projection holds `CAST(i.y AS Int64)`, which type coercion added for the `IN` comparison and which has the same output name. `LogicalPlanBuilder::project` then rejects the new projection.
#4478 is the same name rule for a projection that a user writes, `SELECT a, CAST(a AS INT)`. Here the user writes no duplicate name, and the pull up creates it.
Found on `main` at c3ef34688.
--
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] Correlated `IN` subquery fails with "Projections require unique expression names" when its column is cast [datafusion]
Posted by "vinay121314 (via GitHub)" <gi...@apache.org>.
vinay121314 commented on issue #26033:
URL: https://github.com/apache/datafusion/issues/26033#issuecomment-5988528219
Hi @comphead, are you actively working on this? If not, I'd be happy to pick it up.
--
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] Correlated `IN` subquery fails with "Projections require unique expression names" when its column is cast [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #26033:
URL: https://github.com/apache/datafusion/issues/26033#issuecomment-6479711057
I think we should implement support support for this feature as part of the more general subquery decorrelation project described here
- https://github.com/apache/datafusion/issues/5492
in my opinion WE SHOULD NOT try and extend the existing subquery rewrites to support this type of query as that risks introducing many more wrong results, see https://github.com/apache/datafusion/issues/5492#issuecomment-6479109212
--
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