LATERAL subquery fails with a schema error when a derived table alias differs from the table name

[adriangb]

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

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 991fd23. No wrong results, but this is a common way to write a LATERAL subquery.

Related:

• Support LATERAL subqueries with outer relation references outside WHERE clause #21201 (other LATERAL limitations)
• Wrong results: a correlated filter under the right side of an ASOF JOIN is pulled above the join #25834, Wrong results: a correlated filter under a window function is pulled above the window #25792 (other PullUpCorrelatedExpr bugs found while testing LATERAL)