René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"CTE WITH RECURSIVE cannot find column in temp table","articleBody":"### Version\n\n1.17.2\n\n### What happened?\n\nWe tried to generate go code for a CTE using WITH RECURSIVE, but sqlc breaks.\r\n\r\nNote: this may be a duplicate of https://github.com/kyleconroy/sqlc/issues/1912 \n\n### Relevant log output\n\n```shell\nsqlc generate failed.\r\n# package db\r\nquery.sql:41:7: column \"parent\" does not exist\n```\n\n\n### Database schema\n\n```sql\nCREATE TABLE case_intent_version\r\n(\r\n    version_id SERIAL NOT NULL PRIMARY KEY,\r\n    reviewer TEXT NOT NULL,\r\n    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()\r\n);\r\nCREATE TABLE case_intent\r\n(\r\n    id SERIAL NOT NULL PRIMARY KEY,\r\n    case_intent_string TEXT NOT NULL,\r\n    description TEXT NOT NULL,\r\n    author TEXT NOT NULL\r\n);\r\nCREATE TABLE case_intent_parent_join\r\n(\r\n    case_intent_id BIGINT NOT NULL,\r\n    case_intent_parent_id BIGINT NOT NULL,\r\n    constraint fk_case_intent_id foreign key (case_intent_id) references case_intent(id),\r\n    constraint fk_case_intent_parent_id foreign key (case_intent_parent_id) references case_intent(id)\r\n);\r\nCREATE TABLE case_intent_version_join\r\n(\r\n    case_intent_id BIGINT NOT NULL,\r\n    case_intent_version_id INT NOT NULL,\r\n    constraint fk_case_intent_id foreign key (case_intent_id) references case_intent(id),\r\n    constraint fk_case_intent_version_id foreign key (case_intent_version_id) references case_intent_version(version_id)\r\n);\n```\n\n\n### SQL queries\n\n```sql\n-- name: ListCaseIntentHistory :many\r\nWITH RECURSIVE descendants AS\r\n   ( SELECT case_intent_parent_id AS parent, case_intent_id AS child, 1 AS lvl\r\n     FROM case_intent_parent_join\r\n     UNION ALL\r\n     SELECT d.parent as parent, p.case_intent_id as child, d.lvl + 1 as lvl\r\n     FROM descendants d\r\n              JOIN case_intent_parent_join p\r\n                   ON d.child = p.case_intent_parent_id\r\n   )\r\nselect distinct child, 'child' group_\r\nfrom descendants\r\nwhere parent = @case_intent_id\r\nunion\r\nselect distinct parent, 'parent' group_\r\nfrom descendants\r\nwhere child = @case_intent_id\r\nORDER BY child;\n```\n\n\n### Configuration\n\n```yaml\n{\r\n  \"version\": \"1\",\r\n  \"packages\": [\r\n    {\r\n      \"path\": \"db\",\r\n      \"engine\": \"postgresql\",\r\n      \"schema\": \"query.sql\",\r\n      \"queries\": \"query.sql\"\r\n    }\r\n  ]\r\n}\n```\n\n\n### Playground URL\n\nhttps://play.sqlc.dev/p/cf9b0cde66620c6c151bad0b6d6f6ccd40b510fa6e1e6541621b777b4f970b17\n\n### What operating system are you using?\n\nmacOS\n\n### What database engines are you using?\n\nPostgreSQL\n\n### What type of code are you generating?\n\nGo","author":{"url":"https://github.com/jlisthood","@type":"Person","name":"jlisthood"},"datePublished":"2023-04-03T19:16:04.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":3},"url":"https://github.com/2187/sqlc/issues/2187"}