Repository navigation
A recursive subquery in WHERE clause fails to compile (with incorrect error message.) #3206
Description
Version
1.25.0
What happened?
sqlc failed to compile a query with a recursive query in the WHERE clause.
Error message:
relation "xxx" does not exist
The GetTest1 and GetTest2 queries differ just by WITH RECURSIVE gen and WITH RECURSIVE gen(id) signatures, which are still irrelevant in the given example. Both queries work with Postgres 16.
This bug is significant because the subquery doesn't impact both input, output structures nor query function in the generated code.
Relevant log output
sqlc generate failed.
# package
query.sql:1:1: relation "gen" does not exist
query.sql:23:1: relation "gen" does not existDatabase schema
CREATE TABLE parts (
id BIGSERIAL PRIMARY KEY,
name text NOT NULL,
category_id BIGSERIAL
);
CREATE TABLE part_categories (
id BIGSERIAL PRIMARY KEY,
name text NOT NULL,
parent_id BIGSERIAL
);SQL queries
-- name: GetTest1 :many
SELECT *
FROM parts
WHERE category_id = $1 OR category_id IN (
WITH RECURSIVE gen AS (
SELECT id, parent_id FROM part_categories WHERE parent_id = $1
UNION ALL
SELECT child.id, child.parent_id
FROM part_categories child
JOIN gen
ON gen.id = child.parent_id
)
SELECT
gen.id
FROM gen
LEFT JOIN part_categories parent
ON gen.parent_id = parent.id
);
-- name: GetTest2 :many
SELECT *
FROM parts
WHERE category_id = $1 OR category_id IN (
WITH RECURSIVE gen(id) AS (
SELECT id, parent_id FROM part_categories WHERE parent_id = $1
UNION ALL
SELECT child.id, child.parent_id
FROM part_categories child
JOIN gen
ON gen.id = child.parent_id
)
SELECT
gen.id
FROM gen
LEFT JOIN part_categories parent
ON gen.parent_id = parent.id
);Configuration
{
"version": "2",
"sql": [{
"schema": "schema.sql",
"queries": "query.sql",
"engine": "postgresql",
"gen": {
"go": {
"out": "db"
}
}
}]
}Playground URL
https://play.sqlc.dev/p/c21ba3b10d4cd059e15fb891e65bbe8db449fbba9450f51c19be93097ed8b813
What operating system are you using?
macOS
What database engines are you using?
PostgreSQL
What type of code are you generating?
Go
Activity
dandee commentedon Sep 2, 2025
Hi @kyleconroy , do you have any plans to address this issue?
PS: Playground fails if you try to use any sqlc version above 1.25 (500 Internal Server Error.)
dandee commentedon Apr 21, 2026
Friendly poke @kyleconroy : )