Skip to content

CTE WITH RECURSIVE cannot find column in temp table #2187

Closed
@jlisthood

Description

@jlisthood
Contributor

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

Activity

added
bugSomething isn't working
triageNew issues that hasn't been reviewed
on Apr 3, 2023
prochac

prochac commented on Apr 7, 2023

@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

kyleconroy commented on Oct 25, 2023

@kyleconroy
Collaborator

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

gmldnjs26 commented on Jul 7, 2024

@gmldnjs26

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @kyleconroy@prochac@gmldnjs26@jlisthood

        Issue actions

          CTE WITH RECURSIVE cannot find column in temp table · Issue #2187 · sqlc-dev/sqlc