Invalid row fields for selecting * from subquery with distinct

[krhubert]

Version

1.14.0

What happened?

The proper generated row should be

type GetAuthorRow struct {
	ID     int64
	Name   string
}

// instead of
type GetAuthorRow struct {
	ID     int64
	Name   string
	ID_2   int64
	Name_2 string
	Bio    sql.NullString
}

The * should take only columns from the subquery and not the whole table and subquery at the same time.

Relevant log output

-

Database schema

-- Example queries for sqlc
CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

SQL queries

-- name: GetAuthor :one
select distinct on (row.id) * from (
  select a.id, a.name from authors a
) as row;

Configuration

{
  "version": "1",
  "packages": [
    {
      "path": "db",
      "engine": "postgresql",
      "schema": "query.sql",
      "queries": "query.sql"
    }
  ]
}

Playground URL

https://play.sqlc.dev/p/2292f29b8a05fb2f8b8ce2993849db633c6c03424af01bf8582bf2264419f1c9

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[krhubert]

This is the only workaround I found to get the proper results. https://play.sqlc.dev/p/c42b9c650b6b24cf1a9cf2fb91c31aec17753f270e0cdef31da28f8b955d70a1

But it actually doesn't run on PSQL 14

psq # 
select row.id, row.name from (
select distinct on (row.id) * from (
  select a.id, a.name from authors a
) as row
) as t;
ERROR:  missing FROM-clause entry for table "row"
LINE 2: row.id,

and using CTE it returns error - column reference "id" is ambiguous

[akutschera]

Another workaround is to specify the exact columns you want in the query

-- name: GetAuthor :one
select distinct on (row.id) row.id, row.name from (
  select a.id, a.name from authors a
) as row;

This generates the expected struct

type GetAuthorRow struct {
	ID   int64
	Name string
}