UNION creates duplicate fields

[esenmx]

Version

1.12.0

What happened?

Generated code:

type CreateACRow struct {
	Foo   int32
	Foo_2 int32
	Bar   sql.NullInt32
}

Similar to #568. It always creates duplicate fields with _2 suffix. Using DISTINCT also does not work, checked the #896, it's not supported anyway.

Relevant log output

No response

Database schema

create table a
(
    foo int primary key
);

create table c
(
    bar int
);

SQL queries

-- Simple Example
select * from ((select * from a limit 1) union (select * from c limit 1)) as ac;

-- My Case
with x as (insert into a (foo) values ($1) returning *),
     y as (insert into c (bar) values ($2) returning *)
select *
from ((select * from x) union (select * from y)) as ac;

Configuration

version: "1"
packages:
  - name: "db"
    path: "./db/"
    queries: "./query/"
    schema: "./schema/"
    engine: "postgresql"

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[jamietanna]

I'm seeing this with SQLite too which generates OK, but then fails with:

Error: sql: expected 9 destination arguments in Scan, not 18

[rollcat]

This is completely broken with SQLite.

Input (1.20; using the playground):

-- name: GetUnion :many
select 1 union select 2;

Generated data structure and query code (comment mine):

type GetUnionRow struct {
	Column1 int64
	Column2 int64
}

func (q *Queries) GetUnion(ctx context.Context) ([]GetUnionRow, error) {
	rows, err := q.db.QueryContext(ctx, getUnion)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []GetUnionRow
	for rows.Next() {
		var i GetUnionRow
		if err := rows.Scan(&i.Column1, &i.Column2); err != nil {
			//     ^^^^  ^^^^^^^^^   ^^^^^^^^^
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

Meanwhile in sqlite3 CLI:

sqlite> select 1 union select 2;
1
-
1
2

I'd expect the generated code to be closer to this:

type GetUnionRow struct {
	Column1 int64
}

func (q *Queries) GetUnion(ctx context.Context) ([]GetUnionRow, error) {
	rows, err := q.db.QueryContext(ctx, getUnion)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []GetUnionRow
	for rows.Next() {
		var i GetUnionRow
		if err := rows.Scan(&i.Column1); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

Or better, produce an []int64 result, like what happens with a plain select 1;.

[jamietanna]

I've just upgraded to v1.21.0 and that appears to have resolved the issue, including generating much more reasonable structs 👏

 type RetrievePackageAdvisoriesRow struct {
-       Platform         string
-       Organisation     string
-       Repo             string
-       PackageName      string
-       Version          sql.NullString
-       CurrentVersion   sql.NullString
-       DepTypes         string
-       AdvisoryType     string
-       Description      string
-       Platform_2       string
-       Organisation_2   string
-       Repo_2           string
-       PackageName_2    string
-       Version_2        string
-       CurrentVersion_2 sql.NullString
-       DepTypes_2       string
-       AdvisoryType_2   string
-       Description_2    string
-       Platform_3       string
-       Organisation_3   string
-       Repo_3           string
-       PackageName_3    string
-       Version_3        string
-       CurrentVersion_3 sql.NullString
-       DepTypes_3       string
-       AdvisoryType_3   interface{}
-       Description_3    interface{}
+       Platform       string
+       Organisation   string
+       Repo           string
+       PackageName    string
+       Version        sql.NullString
+       CurrentVersion sql.NullString
+       DepTypes       string
+       AdvisoryType   string
+       Description    string
 }