Common Table Expressions relation does not exist

[hugosjoberg]

Version

1.17.2

What happened?

I'm trying to use a nested common table expression with sqlc. The query works fine in Postgres sqlc throws an error.

The query in it's current form doesn't make sense since it can be simplified, however to hide the real-world complexity and just show the error that occurs the query is simplified.

Relevant log output

sqlc generate failed.

# package db
/query.sql:9:1: relation "summary" does not exist

Database schema

CREATE TABLE random_table (
    foo numeric not null,
    bar numeric not null
);

SQL queries

-- name: GetFoo :many
WITH steps AS(
    WITH summary AS(
            select
                *
            from random_table
        )
    SELECT
        *
    FROM summary
)

SELECT
    *
FROM
    steps as s;

Configuration

No response

Playground URL

https://play.sqlc.dev/p/7748d7b8fcb41011cce96416de3d454532b1ee61df6ba53e78aa0654d8985583

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[Jille]

I think you're hitting two problems:

1. Nested WITH statements don't seem to work. As a workaround you could rewrite your query to use multiple queries in one WITH statement. I've modified your example: https://play.sqlc.dev/p/3b9a5558910d515c85e45db2111d106487ff5cb656dbcf1a0225404617285882
2. Naming columns in the FROM clause doesn't work yet. ((values (3, 'ABC'), (2,'DEF')) x(multiplier, symbol) in your example)

[hugosjoberg]

@Jille thank you for taking a look at the issue.

I guess nested with statements not working is a bug then, right?

Is renaming columns something which is planned to add to sqlc?

[Jille]

All missing features in SQL parsing are also kind of a bug in sqlc I guess?

I tried to find an issue for it but couldn't find one.

Let's file two separate issues for each of them and close this one with links to them?

[hugosjoberg]

Sounds good, I created two issues here:

Renaming columns in FROM clause: #2225
Nested WITH statement: #2226

Closing this issue in favor of those two.