SELECT * expansion fail with recursive CTE

[elvizlai]

Version

1.10.0

What happened?

when using recursive query, it can not generate related go code.

Relevant log output

# package gen
sql/query/dict.sql:1:1: edited query syntax is invalid: syntax error at or near ","

Database schema

create table dict(
    id           VARCHAR(36)     PRIMARY KEY DEFAULT gen_random_uuid(),
    app_id       VARCHAR(36)     NOT NULL,
    code         VARCHAR(64),
    parent_code  VARCHAR(64)     NOT NULL,
    label        TEXT            NOT NULL DEFAULT '',
    value        TEXT            NULL,
    weight       INT             NOT NULL DEFAULT 0,
    is_default   BOOLEAN         NOT NULL DEFAULT false,
    is_virtual   BOOLEAN         NOT NULL DEFAULT false,
    status       SMALLINT        NOT NULL DEFAULT 1,
    create_at    TIMESTAMPTZ(0)  NOT NULL DEFAULT now(),
    create_by    VARCHAR(36)     NOT NULL DEFAULT '',
    update_at    TIMESTAMPTZ(0),
    update_by    VARCHAR(36),
    is_delete    BOOLEAN         NOT NULL DEFAULT false
);

-- test case
insert into dict(app_id, code, parent_code, label) values('1','test.l1','','layer1');
insert into dict(app_id, code, parent_code, label) values('1','test.l2-1','test.l1','layer2-1');
insert into dict(app_id, code, parent_code, label) values('1','test.l2-2','test.l1','layer2-2');
insert into dict(app_id, code, parent_code, label) values('1','test.l3-1','test.l2-2','test.l3-1');
insert into dict(app_id, code, parent_code, label) values('1','test.l4-1','test.l3-1','test.l4-1');
insert into dict(app_id, parent_code, label, value) values('1','test.l4-1','l4-1-v1', '1');
insert into dict(app_id, parent_code, label, value) values('1','test.l4-1','l4-1-v2', '1');

SQL queries

-- name: GetDictTree :many
with recursive dictTree(id, code, parent_code, label, value, path, depth) AS (
	select id, code, parent_code, label, value, ARRAY[COALESCE((select id from dict where code=''),'virtual_root'), id], 1 as depth from dict where app_id = '1' and parent_code = '' and is_delete=false
	union
		select d.id, d.code, d.parent_code, d.label, d.value, t.path || ARRAY[d.id], t.depth+1 as depth from dict d join dictTree t on d.parent_code = t.code and not d.id = ANY(t.path) and d.is_delete=false
)
select * from dictTree d order by depth, parent_code;

Configuration

version: "1"
packages:
  - name: "gen"
    path: "mapper/gen"
    queries: "./sql/query/"
    schema: "./sql/schema/"
    engine: "postgresql"
    emit_prepared_queries: true
    emit_interface: false
    emit_exact_table_names: false
    emit_empty_slices: false
    emit_json_tags: false
    json_tags_case_style: "camel"
rename:
  id: "Id"

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[capnspacehook]

I ran into a similar issue with PostgreSQL, Go and sqlc v1.10.0 when trying to use a recursive CTE, though I got a different error.

Playground link: https://play.sqlc.dev/p/0ab785e6d5aaadf734521e870a8140399e767f8ecec6ba8934a421f40c440ec9

May be related to #723, the error message is the same as reported in that issue.

[saquibmian]

Was there any traction on this?

[kyleconroy]

This is fixed in v1.23.0 by enabling the database-backed query analyzer. We added a test case for this issue so it won’t break in the future.

You can play around with the working example on the playground

EDIT: Linked to the wrong test case, fixed.