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?