WITH clause unable to find relation

[markdessain]

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

[markdessain]

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

[grgbrn]

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.

[jtarchie]

This appears to happen with the UPDATE statements, too.