CTE WITH RECURSIVE cannot find column in temp table

[jlisthood]

Version

1.17.2

What happened?

We tried to generate go code for a CTE using WITH RECURSIVE, but sqlc breaks.

Note: this may be a duplicate of #1912

Relevant log output

sqlc generate failed.
# package db
query.sql:41:7: column "parent" does not exist

Database schema

CREATE TABLE case_intent_version
(
    version_id SERIAL NOT NULL PRIMARY KEY,
    reviewer TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);
CREATE TABLE case_intent
(
    id SERIAL NOT NULL PRIMARY KEY,
    case_intent_string TEXT NOT NULL,
    description TEXT NOT NULL,
    author TEXT NOT NULL
);
CREATE TABLE case_intent_parent_join
(
    case_intent_id BIGINT NOT NULL,
    case_intent_parent_id BIGINT NOT NULL,
    constraint fk_case_intent_id foreign key (case_intent_id) references case_intent(id),
    constraint fk_case_intent_parent_id foreign key (case_intent_parent_id) references case_intent(id)
);
CREATE TABLE case_intent_version_join
(
    case_intent_id BIGINT NOT NULL,
    case_intent_version_id INT NOT NULL,
    constraint fk_case_intent_id foreign key (case_intent_id) references case_intent(id),
    constraint fk_case_intent_version_id foreign key (case_intent_version_id) references case_intent_version(version_id)
);

SQL queries

-- name: ListCaseIntentHistory :many
WITH RECURSIVE descendants AS
   ( SELECT case_intent_parent_id AS parent, case_intent_id AS child, 1 AS lvl
     FROM case_intent_parent_join
     UNION ALL
     SELECT d.parent as parent, p.case_intent_id as child, d.lvl + 1 as lvl
     FROM descendants d
              JOIN case_intent_parent_join p
                   ON d.child = p.case_intent_parent_id
   )
select distinct child, 'child' group_
from descendants
where parent = @case_intent_id
union
select distinct parent, 'parent' group_
from descendants
where child = @case_intent_id
ORDER BY child;

Configuration

{
  "version": "1",
  "packages": [
    {
      "path": "db",
      "engine": "postgresql",
      "schema": "query.sql",
      "queries": "query.sql"
    }
  ]
}

Playground URL

https://play.sqlc.dev/p/cf9b0cde66620c6c151bad0b6d6f6ccd40b510fa6e1e6541621b777b4f970b17

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[prochac]

I found a workaround for SQLite, give it na extra kick.

CREATE TABLE IF NOT EXISTS c
(
    port_num INTEGER NOT NULL
);

CREATE VIEW IF NOT EXISTS all_ports AS
WITH RECURSIVE c(port_num) AS
                   (VALUES (1)
                    UNION ALL
                    SELECT port_num + 1
                    FROM c
                    WHERE port_num < 65535)
SELECT port_num
FROM c;

Now I'm migrating to Postgres and got the kick back.

CREATE VIEW all_ports AS
WITH RECURSIVE c(port_num) AS
                   (SELECT 1
                    UNION ALL
                    SELECT port_num + 1
                    FROM c
                    WHERE port_num < 65535)
SELECT port_num
FROM c;

[kyleconroy]

This is fixed in v1.23.0 by enabling the database-backed query analyzer.

You can play around with the working example on the playground

[gmldnjs26]

@kyleconroy
I am facing the same issue, but I am using MySQL. Can I enable the database-backed query analyzer in MySQL?