relation does not exist parsing error for WITH RECURSIVE subquery

[will-wow]

Version

1.20.0

What happened?

I'm getting a relation "recursive_table_name" does not exist error when running sqlc generate on a query that has a WITH RECURSIVE subquery.

For instance this query (simplified as a test case) throws a relation "search_tree" does not exist error

-- name: GetLatestVersionWithSubquery :one
SELECT * 
FROM versions
WHERE versions.id IN (
  WITH RECURSIVE search_tree(id, chain_id, chain_counter) AS (
	SELECT base.id, base.id AS chain_id, 0 as chain_counter
	FROM versions AS base
	WHERE versions.previous_version_id IS NULL
	UNION ALL
	SELECT v.id, search_tree.chain_id, v.parent_lead_time_estimate_id, v.chain_counter + 1
	FROM versions AS v
	INNER JOIN search_tree ON search_tree.id = v.previous_version_id 
  )
  SELECT DISTINCT ON (search_tree.chain_id) 
	search_tree.id
  FROM search_tree   
  ORDER BY search_tree.chain_id, chain_counter DESC
);

But that same recursive CTE query parses fine on its own when it's not in a subquery:

-- name: GetLatestVersion :one
WITH RECURSIVE search_tree(id, chain_id, chain_counter) AS (
	SELECT base.id, base.id AS chain_id, 0 as chain_counter
	FROM versions AS base
	WHERE versions.previous_version_id IS NULL
  	UNION ALL
	SELECT v.id, search_tree.chain_id, v.parent_lead_time_estimate_id, v.chain_counter + 1
	FROM versions AS v
	INNER JOIN search_tree ON search_tree.id = v.previous_version_id
)
SELECT DISTINCT ON (search_tree.chain_id) 
	search_tree.id
FROM search_tree   
ORDER BY search_tree.chain_id, chain_counter DESC;

It 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.

Relevant log output

query.sql:24:1: relation "search_tree" does not exist

Database schema

CREATE TABLE versions (
  id   BIGSERIAL PRIMARY KEY,
  name TEXT,
  previous_version_id bigint NOT NULL
);

SQL queries

WITH RECURSIVE search_tree(id, chain_id, chain_counter) AS (
	SELECT base.id, base.id AS chain_id, 0 as chain_counter
	FROM versions AS base
	WHERE versions.previous_version_id IS NULL
  	UNION ALL
	SELECT v.id, search_tree.chain_id, v.parent_lead_time_estimate_id, v.chain_counter + 1
	FROM versions AS v
	INNER JOIN search_tree ON search_tree.id = v.previous_version_id
)
SELECT DISTINCT ON (search_tree.chain_id) 
	search_tree.id
FROM search_tree   
ORDER BY search_tree.chain_id, chain_counter DESC;

Configuration

{
  "version": "1",
  "packages": [
    {
      "path": "db",
      "engine": "postgresql",
      "schema": "query.sql",
      "queries": "query.sql"
    }
  ]
}

Playground URL

https://play.sqlc.dev/p/fd638d4cd5e89589d00806d8575daa55fd45f28199ffa1365a43d879dd9a5915

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[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

[will-wow]

Amazing, thank you! Now I have an excuse to go back and optimize that query 😄