RECURSIVE cte failed with `star expansion failed for query`

[anthonycj04]

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]

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

[skamranahmed]

Is it possible for sqlc to read the schema directly from the database, instead of requiring a separate schema definition file (.sql)?

I am using Goose with Go migrations and I don't have .sql files for my schema.

I am using the below configuration, but I am still getting the star expansion failed for query error

version: "2"
sql:
- database:
    uri: xxxx
  queries: "db/query"
  engine: "postgresql"
  gen:
    go: 
      package: "db"
      out: "db/sqlc"
      emit_json_tags: true
      emit_interface: true
      emit_empty_slices: true