A recursive subquery in WHERE clause fails to compile (with incorrect error message.)

[dandee]

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 exist

Database 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

[dandee]

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.)