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: [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]
