René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"relation does not exist parsing error for WITH RECURSIVE subquery","articleBody":"### Version\n\n1.20.0\n\n### What happened?\n\nI'm getting a `relation \"recursive_table_name\" does not exist` error when running `sqlc generate` on a query that has a WITH RECURSIVE subquery.\r\n\r\nFor instance this query (simplified as a test case) throws a `relation \"search_tree\" does not exist` error\r\n\r\n```sql\r\n-- name: GetLatestVersionWithSubquery :one\r\nSELECT * \r\nFROM versions\r\nWHERE versions.id IN (\r\n  WITH RECURSIVE search_tree(id, chain_id, chain_counter) AS (\r\n\tSELECT base.id, base.id AS chain_id, 0 as chain_counter\r\n\tFROM versions AS base\r\n\tWHERE versions.previous_version_id IS NULL\r\n\tUNION ALL\r\n\tSELECT v.id, search_tree.chain_id, v.parent_lead_time_estimate_id, v.chain_counter + 1\r\n\tFROM versions AS v\r\n\tINNER JOIN search_tree ON search_tree.id = v.previous_version_id \r\n  )\r\n  SELECT DISTINCT ON (search_tree.chain_id) \r\n\tsearch_tree.id\r\n  FROM search_tree   \r\n  ORDER BY search_tree.chain_id, chain_counter DESC\r\n);\r\n```\r\n\r\nBut that same recursive CTE query parses fine on its own when it's not in a subquery:\r\n\r\n```sql\r\n-- name: GetLatestVersion :one\r\nWITH RECURSIVE search_tree(id, chain_id, chain_counter) AS (\r\n\tSELECT base.id, base.id AS chain_id, 0 as chain_counter\r\n\tFROM versions AS base\r\n\tWHERE versions.previous_version_id IS NULL\r\n  \tUNION ALL\r\n\tSELECT v.id, search_tree.chain_id, v.parent_lead_time_estimate_id, v.chain_counter + 1\r\n\tFROM versions AS v\r\n\tINNER JOIN search_tree ON search_tree.id = v.previous_version_id\r\n)\r\nSELECT DISTINCT ON (search_tree.chain_id) \r\n\tsearch_tree.id\r\nFROM search_tree   \r\nORDER BY search_tree.chain_id, chain_counter DESC;\r\n```\r\n\r\nIt seems like something in the SQL parser isn't matching the CTE's name with the auxiliary statement, only when it's nested in a subquery. But the real version of this query that I'm trying to write does run fine against real postgres, so it seems that the syntax is valid.\n\n### Relevant log output\n\n```shell\nquery.sql:24:1: relation \"search_tree\" does not exist\n```\n\n\n### Database schema\n\n```sql\nCREATE TABLE versions (\r\n  id   BIGSERIAL PRIMARY KEY,\r\n  name TEXT,\r\n  previous_version_id bigint NOT NULL\r\n);\n```\n\n\n### SQL queries\n\n```sql\nWITH RECURSIVE search_tree(id, chain_id, chain_counter) AS (\r\n\tSELECT base.id, base.id AS chain_id, 0 as chain_counter\r\n\tFROM versions AS base\r\n\tWHERE versions.previous_version_id IS NULL\r\n  \tUNION ALL\r\n\tSELECT v.id, search_tree.chain_id, v.parent_lead_time_estimate_id, v.chain_counter + 1\r\n\tFROM versions AS v\r\n\tINNER JOIN search_tree ON search_tree.id = v.previous_version_id\r\n)\r\nSELECT DISTINCT ON (search_tree.chain_id) \r\n\tsearch_tree.id\r\nFROM search_tree   \r\nORDER BY search_tree.chain_id, chain_counter DESC;\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/fd638d4cd5e89589d00806d8575daa55fd45f28199ffa1365a43d879dd9a5915\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/will-wow","@type":"Person","name":"will-wow"},"datePublished":"2023-08-21T20:41:33.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":2},"url":"https://github.com/2644/sqlc/issues/2644"}