Repository navigation
CTE inside subquery not working #3156
Description
Version
1.25.0
What happened?
It seems that the parser cannot identify the relation created from the cte when the cte is inside a subquery
Relevant log output
query.sql:1:1: relation "cte_authors" does not existDatabase schema
CREATE TABLE authors (
id BIGSERIAL PRIMARY KEY,
name text NOT NULL,
bio text
);SQL queries
-- name: test :many
SELECT * FROM (
WITH cte_authors AS (
SELECT * FROM authors
)
SELECT * FROM authors JOIN cte_authors ON autors.id = cte_autors.et_id
) AS sub WHERE true;Configuration
{
"version": "2",
"sql": [{
"schema": "schema.sql",
"queries": "query.sql",
"engine": "postgresql",
"gen": {
"go": {
"out": "db"
}
}
}]
}Playground URL
https://play.sqlc.dev/p/ee35e71a5d2a5322c92655a2cfe5269302155113824459cc9c7f1fb74afd771f
What operating system are you using?
Linux
What database engines are you using?
PostgreSQL
What type of code are you generating?
Go
Activity
matteobassan commentedon Jul 9, 2024
Same problem here.
Using CTE is quite unusable with this package, given the other issues related (#3128)
Bockit commentedon Nov 14, 2026
This has a silent form as well, and I think it is the more serious one.
With
database: managed: true, oranalyzer.database: trueand a URI, therelation "x" does not existerror from sqlc's own analysis is discarded and the database's description of the statement is used in its place. Every column then takes its table'sNOT NULL, so a column from the nullable side of an outer join is generated as never null.On 1.31.1 and on main (3ad8b20), generating Go with
sql_package: pgx/v5:relation "x" does not exist, as reported here.Titleisstring. Move theWITHto the top of the statement and it ispgtype.Text, which is right.*in the statement as well:star expansion failed for query, as in Star expansion failed for query #2915.#2644 and #2004 were closed as fixed by the database-backed analyzer, which is the path that gives the wrong type.
The cause is that the query catalog only registers the statement's own
WITH. I have a fix on a fork that gives each nested statement a scope of its own, with end-to-end cases: Bockit@f7cedce. With it the query in #3206 generates too. I can open a pull request if a fix to this path is wanted.The analysis was done with AI help and checked by me.