sqlc-dev / sqlc

Generate type-safe code from SQL
https://sqlc.dev
MIT License
13.59k stars 813 forks source link

RECURSIVE cte failed with `star expansion failed for query` #3286

Open anthonycj04 opened 8 months ago

anthonycj04 commented 8 months ago

Version

1.25.0

What happened?

Failed to generate go model when using recursive CTE

Relevant log output

-- With database-backed analysis
# package
db/queries/tickers.sql:1:1: star expansion failed for query

-- Without database-backed analysis
# package
db/queries/tickers.sql:1:1: edit start location is out of bounds

Database schema

CREATE TYPE ticker_symbol AS enum('btc', 'eth', 'usdt');
CREATE TABLE tickers(
    symbol ticker_symbol NOT NULL,
    timestamp bigint NOT NULL,
    ask numeric NOT NULL,
    bid numeric NOT NULL,
    price numeric NOT NULL,
    source text NOT NULL,
    is_processed boolean NOT NULL DEFAULT FALSE,
    created_at timestamp NOT NULL DEFAULT NOW(),
    updated_at timestamp NOT NULL DEFAULT NOW(),
    PRIMARY KEY (symbol, timestamp)
);

SQL queries

-- name: GetLatestTickers :many
WITH RECURSIVE cte AS (
    (
        SELECT *
        FROM tickers
        ORDER BY symbol,
            timestamp DESC
        LIMIT 1
    )
    UNION ALL
    SELECT l.*
    FROM cte c
        CROSS JOIN LATERAL (
            SELECT *
            FROM tickers t
            WHERE t.symbol > c.symbol
            ORDER BY t.symbol,
                t.timestamp DESC
            LIMIT 1
        ) l
) TABLE cte
ORDER BY symbol;

Configuration

version: "2"
sql:
- schema: "schema.sql"
  queries: "query.sql"
  engine: "postgresql"
  database:
    uri: xxx
  gen:
    go:
      out: db
      sql_package: "pgx/v5"

Playground URL

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

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

semanser commented 8 months ago

The same error happens if sqlc can't find your schema. Make sure that schema field in sqlc.yml points to a correct location.