WITH clause unable to find relation

Version

1.17.2

What happened?

The following is valid when running it in the sqlite command line:

sqlite> WITH abc AS (
   ...>   SELECT 1 AS n
   ...> )
   ...> SELECT * FROM abc;
1

but when selecting the sqlite engine and running it through sqlc generate it throws the following error:

sqlc generate failed.
package db
query.sql:1:1: relation "abc" does not exist

If I switch the engine from sqlite to be postgresql it works as expected. Which suggests the issue is just related to the sqlite implementation.

Relevant log output

No response

Database schema

No response

SQL queries

WITH abc AS (
    SELECT 1 AS n
)
SELECT * FROM abc;

Configuration

No response

Playground URL

Example of it working with postgresql engine: https://play.sqlc.dev/p/aae5b19f0a2bf4f083aa6a0156163225930d12d8ba5cc296f0b54994e2b5ccda

Same code failing when running against the sqlite engine: https://play.sqlc.dev/p/6e6dbf8511ed9ed583b7bb3eac38d57ed43c188921fd9b5b967c3213682f302b

What operating system are you using?

mac

What database engines are you using?

sqlite

What type of code are you generating?

No response

I'm not too familiar with the .g4 files but took a quick look through the parser code I could see the delete has the with clause - https://github.com/kyleconroy/sqlc/blob/v1.17.2/internal/engine/sqlite/parser/SQLiteParser.g4#L242-L244

So I had a go at creating a new query using the with clause.

WITH abc AS (
  SELECT 1
)
DELETE FROM authors WHERE id IN abc

It generates successfully - https://play.sqlc.dev/p/036d3cb198ee8ad034f18a5ee1e2f3108ec7daf38d6420ee7922cbd02a2d806b

I also ran into this today, seems to be the same issue.

Not familiar with .g4 files either, but it looks like SELECT and DELETE statements use different rules in the grammar to parse the WITH CTE, so maybe that's a clue as to why DELETE works but not SELECT.

fix(engine/sqlite): fixed to be able to find relation from WITH clause

fix(engine/sqlite): fixed to be able to find relation from WITH clause (

This appears to happen with the UPDATE statements, too.

fix(engine/sqlite): fixed to be able to find relation from WITH clause (

I still have this issue with sqlite, sqlc version v1.30.0. It logs relation "<my_with_alias>" does not exist when trying to insert after a with statement.

Navigation Menu

Repository navigation

WITH clause unable to find relation #2136

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

WITH clause unable to find relation #2136

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

markdessain commented on Mar 9, 2023

grgbrn commented on Mar 14, 2023

jtarchie commented on Sep 16, 2023

nexovec commented on Apr 14, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions