dolthub / dolthub/doltgresql

Support correlated table functions in FROM with implicit and explicit LATERAL semantics

Open
#3,185 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Go
Stars
2.1k
Forks
73
Avg merge
1d 11h
Merged PRs (30d)
152

Description

PostgreSQL table functions in `FROM` may reference columns from preceding `FROM` items. This works both implicitly and with the explicit `LATERAL` keyword. DoltgreSQL currently rejects both correlated forms.

Reproduction:

```sql
CREATE TABLE lateral_array_test (id int, values_array int[]);
INSERT INTO lateral_array_test VALUES
(1, ARRAY[10,20]),
(2, ARRAY[30,40,50]);

SELECT t.id, k
FROM lateral_array_test t
CROSS JOIN generate_subscripts(t.values_array, 1) AS k
ORDER BY t.id, k;

SELECT t.id, k.idx
FROM lateral_array_test t
CROSS JOIN LATERAL generate_subscripts(t.values_array, 1) AS k(idx)
ORDER BY t.id, k.idx;
```

The implicit form currently fails with `table not found: t`. The explicit form is transformed into an unsupported set-returning-function `VALUES` shape and fails before execution. PostgreSQL 15.17 returns `(1,1)`, `(1,2)`, `(2,1)`, `(2,2)`, and `(2,3)` for both forms.

This is distinct from #3173, whose motivating correlated `ARRAY(SELECT ...)` subquery is handled by PR #3178. General correlated table-function joins require the planner to preserve outer-row visibility when building the right-side table function.

Acceptance criteria:

- Support implicit lateral semantics for table functions whose arguments reference preceding `FROM` items.
- Support the equivalent explicit `LATERAL` syntax.
- Preserve scalar aliases and explicit output-column aliases.
- Cover NULL and empty SRF results per outer row.
- Cover multiple outer rows with different result cardinalities.
- Validate all expected results against a real PostgreSQL server.

Contributor guide

Open the contributing guide

Research direction

Start by tracing the planner's handling of FROM table functions, including the implicit correlated form and the explicit LATERAL path that becomes an unsupported set-returning-function VALUES shape. Run the supplied SQL reproduction against DoltgreSQL and PostgreSQL 15.17, then verify aliases, NULL and empty results, and differing result cardinalities across outer rows.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, postgresql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.