sqlite: aliases in RETURNING are ignored when naming result columns #4680

Version

1.31.1

What happened?

On the SQLite engine, aliases in a RETURNING clause are ignored. The generated row struct (or scalar variable) is named after the underlying column, or ColumnN for an expression, instead of the alias:

INSERT INTO t (name, score) VALUES (?, ?)
RETURNING id AS new_id, score * 2 AS doubled;

type InsertReturningRow struct {
	ID      int64 // expected: NewID
	Column2 int64 // expected: Doubled
}

SQLite itself reports the column names new_id and doubled:

$ sqlite3 :memory: -header "CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT NOT NULL, score INTEGER NOT NULL, created TEXT); INSERT INTO t (name, score) VALUES ('a', 2) RETURNING id AS new_id, score * 2 AS doubled;"
new_id|doubled
1|4

This affects INSERT, INSERT … ON CONFLICT DO UPDATE, UPDATE and DELETE, and both the default analyzer and the core analyzer (SQLCEXPERIMENT=coreanalyzer). The same aliases in a SELECT list work.

The generated code compiles and runs, but the field names do not match the SQL. If the alias is added later, every caller breaks. Two aliases on the same column can't be told apart (ID / ID_2).

Relevant log output

Database schema

CREATE TABLE t (
    id      INTEGER PRIMARY KEY,
    name    TEXT    NOT NULL,
    score   INTEGER NOT NULL,
    created TEXT
);

CREATE UNIQUE INDEX t_name ON t (name);

SQL queries

-- name: InsertReturningAlias :one
INSERT INTO t (name, score) VALUES (?, ?)
RETURNING id AS new_id, score * 2 AS doubled;

-- name: UpsertReturningAlias :one
INSERT INTO t (name, score) VALUES (?, ?)
ON CONFLICT (name) DO UPDATE SET score = score + excluded.score
RETURNING id AS new_id, score AS total;

-- name: UpdateReturningAlias :many
UPDATE t SET score = ? WHERE name = ?
RETURNING id AS updated_id, score - 1 AS previous;

-- name: DeleteReturningAlias :many
DELETE FROM t WHERE score < ?
RETURNING id AS deleted_id, upper(name) AS shout;

Configuration

version: "2"
sql:
  - engine: "sqlite"
    schema: "schema.sql"
    queries: "query.sql"
    gen:
      go:
        package: "db"
        out: "db"

Playground URL

No response

What operating system are you using?

Linux

What database engines are you using?

SQLite

What type of code are you generating?

Go