adriangb opened a new issue, #25837:
URL: https://github.com/apache/datafusion/issues/25837
### Describe the bug
A `LATERAL` subquery fails with a schema error when its correlated filter is
inside a derived table, and the alias of that derived table is different from
the name of the table in it.
The same query works when the alias is the same as the table name, and when
the subquery is an `EXISTS` instead of a `LATERAL`.
### To Reproduce
```sql
CREATE TABLE o(k INT) AS VALUES (1), (2);
CREATE TABLE l(id INT, v INT) AS VALUES (1, 10), (2, 20);
SELECT o.k, s.v
FROM o, LATERAL (
SELECT l2.v FROM (SELECT * FROM l WHERE l.id = o.k) AS l2
) AS s
ORDER BY o.k;
```
| DataFusion | DuckDB 1.5.2 | PostgreSQL 17.6 |
| --- | --- | --- |
| `Schema error: No field named l.id. Valid fields are o.k.` | `(1, 10)`,
`(2, 20)` | `(1, 10)`, `(2, 20)` |
Other forms of the same query:
| Query | DataFusion |
| --- | --- |
| The same, with `AS l` instead of `AS l2` | `(1, 10)`, `(2, 20)` |
| The same, with `AS x` and `SELECT l.v FROM l WHERE ...` inside | `Schema
error: No field named l.id` |
| `LATERAL (SELECT l2.v FROM l AS l2 WHERE l2.id = o.k)` (no derived table)
| `(1, 10)`, `(2, 20)` |
| `WHERE EXISTS (SELECT 1 FROM m JOIN (SELECT * FROM l WHERE l.id = o.k) AS
l2 ON m.id = l2.id)` | correct |
The error also occurs when the derived table is one input of a join inside
the `LATERAL` subquery (inner join, cross join, `ASOF JOIN`).
### Expected behavior
The results of DuckDB and PostgreSQL above: `(1, 10)`, `(2, 20)`.
### Additional context
The correlated filter `l.id = o.k` is pulled out of the subquery and becomes
the condition of the join that replaces the `LATERAL`. `PullUpCorrelatedExpr`
(`datafusion/optimizer/src/decorrelate.rs`) gives the pulled up filter with the
qualifier of the inner table, `l.id`. Above the `SubqueryAlias` of the derived
table, that column is `l2.id`.
`decorrelate_lateral_join.rs` then calls `requalify_filter` to change the
inner columns of the filter to the qualifier of the `LATERAL` alias (`s`).
`requalify_filter` changes a column only if `inner_schema.has_column(col)` is
true. The schema of the rewritten subquery has `l2.id`, not `l.id`, so `l.id`
is not changed, and the new join condition refers to a column that does not
exist. When the alias is `l`, the names are the same and the query works.
Found on `main` at 991fd23dd0. No wrong results, but this is a common way to
write a `LATERAL` subquery.
Related:
- https://github.com/apache/datafusion/issues/21201 (other `LATERAL`
limitations)
- https://github.com/apache/datafusion/issues/25834,
https://github.com/apache/datafusion/issues/25792 (other `PullUpCorrelatedExpr`
bugs found while testing `LATERAL`)
--
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]
