CTE with RECURSIVE and UNION ALL

Version

1.15.0

What happened?

I try to generate go code with a recursive CTE, which uses UNION ALL and it fails.

My query is quite complex, but here is a simple reproduction:

Relevant log output

query.sql:7:8: column "n" does not exist

Database schema

None

SQL queries

-- name: CTERecursive :many
WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT n FROM t;

Configuration

{
  "version": "1",
  "packages": [
    {
      "path": "go",
      "name": "querytest",
      "schema": "query.sql",
      "queries": "query.sql"
    }
  ]
}

Playground URL

https://play.sqlc.dev/p/5c0c86493af9912a8d54ab083a92edcd4105ea8baf30681e1abe761a0cce1246

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

Note that if I replace SELECT n with SELECT sum(n) everything works 🤔

-- name: CTERecursive :many
WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

https://play.sqlc.dev/p/5c0380fceda01ba464e7e65219c9c5bb7b35e8047793a6b01d97aced8e2e21bd

I have a branch of sqlc with this exact test case. I can open a PR (however I have no idea where to look to fix this).

@kyleconroy

@kyleconroy is there anything I can do to help debug/fix this issue ? (I know go, but don't know where to start debugging such an issue)

Fixes #1912

This is fixed in v1.23.0 by enabling the database-backed query analyzer. You can play around with the working example on the playground

fix(compiler): Pull in array information from analyzer (sqlc-dev#2864)

Navigation Menu

Repository navigation

CTE with RECURSIVE and UNION ALL #1912

Version

What happened?

Relevant log output

Database schema

SQL queries

Configuration

Playground URL

What operating system are you using?

What database engines are you using?

What type of code are you generating?

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

CTE with RECURSIVE and UNION ALL #1912

Description

Version

What happened?

Relevant log output

Database schema

SQL queries

Configuration

Playground URL

What operating system are you using?

What database engines are you using?

What type of code are you generating?

Activity

oliverpool commented on Oct 26, 2022

oliverpool commented on Oct 26, 2022

oliverpool commented on Dec 5, 2022

kyleconroy commented on Oct 24, 2023

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions