CTE with RECURSIVE column does not exist

[johnatannvmd]

sqlc.yaml

version: "1"
packages:
  - name: main
    path: .
    schema: schema.sql
    queries: query.sql
    engine: postgresql
    emit_json_tags: true
    emit_prepared_queries: false
    emit_interface: true
    emit_exact_table_names: false
    emit_empty_slices: false

schema.sql

create table graph
(
	id serial not null primary key,
	data int not null
);

create unique index graph_id_uindex
	on graph (id);

--

create table graph_link
(
	g_id int not null
		constraint graph_link_graph_id_fk
			references graph (id),
	import_g int not null
		constraint graph_link_graph_id_fk_2
			references graph (id)
);

create unique index graph_link_g_id_import_g_uindex
	on graph_link (g_id, import_g);

query.sql

-- name: GetGraphDeps :many
WITH RECURSIVE search_graph(id, import_id, data, depth, path, cycle) AS (
    SELECT gl.g_id id, gl.import_g import_id, g.data, 1 as depth,
           ARRAY[gl.g_id] as path, false as cycle
    FROM graph_link gl
    JOIN graph g on g.id = gl.g_id

    UNION ALL

    SELECT gl.g_id id, gl.import_g import_id, g.data, search_graph.depth + 1 as depth,
           search_graph.path || gl.import_g as path, gl.g_id = ANY(search_graph.path) as cycle
    FROM (graph_link gl JOIN graph g on g.id = gl.g_id),
         search_graph
    WHERE gl.g_id = search_graph.import_id AND NOT search_graph.cycle
)
SELECT DISTINCT search_graph.id, search_graph.depth, search_graph.data FROM search_graph;

sqlc generate gives me

query.sql:11:17: column "id" does not exist

[kindermoumoute]

This looks related to #709

[johnatannvmd]

@kindermoumoute playing with aliases names didn't help. I saw #709 issue before posting this.

[kindermoumoute]

Alias didn't help for me too, I have no idea why it worked for @jschaf 🤷‍♂️

[jschaf]

I wasn't doing a recursive CTE.

[kindermoumoute]

The below example will result in an error column "modified_at" does not exist similar to what you have.

CREATE TABLE foo (
    key uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    modification_date timestamp with time zone DEFAULT now() NOT NULL
);

-- name: FooBar :many
WITH agg1 AS (
    SELECT t.key, t.modification_date as modified_at FROM foo t
)
SELECT key FROM agg1 WHERE modified_at > @from_date;

All aliases I tried failed but the original name works:

-- name: FooBar :many
WITH agg1 AS (
    SELECT t.key, t.modification_date as modification_date FROM foo t
)
SELECT key FROM agg1 WHERE modification_date > @from_date;

Error comes from this line so I would suggest the sqlc code is not indexing aliases from the WITH. Maybe @kyleconroy has some additional inputs on this?

[jschaf]

Maybe try a fully qualified reference or using a table alias? I have a pretty complex query with 5 CTEs that works and I'm using table aliases for each CTE. In general, I've found being painfully explicit with aliases and casting sometimes helps sqlc work through the query.

I also use this syntax for params: sqlc.arg('EndTime'), though it probably doesn't matter your case.

[kyleconroy]

Fixed in #896

[capnspacehook]

I believe I ran into this same issue with on sqlc v1.10.0. Playground link: https://play.sqlc.dev/p/0ab785e6d5aaadf734521e870a8140399e767f8ecec6ba8934a421f40c440ec9