Error when using CTE and self join

[matbur]

Version

1.17.2

What happened?

When using CTE (WITH clause) in query and self join, sqlc doesn't see any columns. When using join from another table/view it works as expected.

Relevant log output

sqlc generate failed.

# package db
query.sql:6:5: column "id" does not exist

Database schema

No response

SQL queries

-- name: Q :many
WITH
    items1 AS (SELECT 'id'::TEXT AS id, 'name'::TEXT AS name),
    items2 AS (SELECT 'id'::TEXT AS id, 'name'::TEXT AS name)
SELECT
    i1.id AS id1,
    i2.id AS id2
FROM
    items1 i1
        JOIN items1 i2 ON 1 = 1;
        -- change ^ above items1 to items2 to fix

Configuration

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

Playground URL

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

What operating system are you using?

No response

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

[matbur]

Thank you for fixing this! Now it works perfectly.