tomsanbear opened a new issue, #23097:
URL: https://github.com/apache/datafusion/issues/23097
### Describe the bug
A `WITH RECURSIVE` CTE with a column-list alias (the `t(n)` part) never
applies those declared names to the recursive working table, so the recursive
term can't resolve them and the query fails to plan.
This doesn't plan:
```sql
WITH RECURSIVE t(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM t WHERE n < 10
)
SELECT n FROM t;
```
```
Schema error: No field named n. Valid fields are t."Int64(1)".
```
The declared name `n` never makes it onto the working table, so the static
term's column stays `Int64(1)` and the self-reference can't find `n`.
A couple of things that *do* work, which narrow it down:
- Non-recursive is fine: `WITH t(n) AS (SELECT 1) SELECT n FROM t;`
- Putting the alias in the static SELECT instead of the column list is fine:
```sql
WITH RECURSIVE t AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM t WHERE n < 10
)
SELECT n FROM t; -- returns 1..10
```
So it's specific to the recursive form with a column-list alias. Postgres
and DuckDB both accept the original query.
### To Reproduce
Run in datafusion-cli:
```sql
WITH RECURSIVE t(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM t WHERE n < 10
)
SELECT n FROM t;
```
You get:
```
Schema error: No field named n. Valid fields are t."Int64(1)".
```
### Expected behavior
It should plan and return 1..10, same as Postgres/DuckDB (and same as the
`SELECT 1 AS n` version above).
### Additional context
Reproduces on `main` and on 54.0.0.
The root cause is in `datafusion/sql/src/cte.rs`: the column-list alias is
applied (via `apply_table_alias`) only after the whole CTE plan is built, but
the recursive working table is created from the static term's schema *before*
that — so the declared names never reach it. The fix is to apply the
column-list alias to the static term before the work table is created. I have a
fix ready and will open a PR.
--
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]
