MIN() returns an interface{}

[abh]

Version

Other

What happened?

(version 1.16.0)

select min(id) from authors; creates a function that returns an interface{} instead of a NULL-able version of the id type.

#1574 talks about this for DATETIME types, but other issues indicates that it should work for simpler types, maybe?

If not, what's the workaround? Parsing the interface{} is awkward, best I can tell.

Relevant log output

No response

Database schema

No response

SQL queries

No response

Configuration

No response

Playground URL

https://play.sqlc.dev/p/96e9cf0291f2e13cc784144f8abd4d5a556c549f2bfe909a26377703f671ff61

What operating system are you using?

macOS

What database engines are you using?

MySQL

What type of code are you generating?

Go

[d-tsuji]

You can avoid this by casting a typecast. A PostgreSQL example is below.

-- name: GetMinID :one
select min(id)::BIGINT from authors;

Explicit typecasting will generate the intended code.

func (q *Queries) GetMinID(ctx context.Context) (int64, error) {
	row := q.db.QueryRowContext(ctx, getMinID)
	var column_1 int64
	err := row.Scan(&column_1)
	return column_1, err
}

In v1.16.0, aggregate functions such as MIN() and MAX() are not capable of generating fields of type depending on the column.

[abh]

@d-tsuji I'm using MySQL for this project and the equivalent features (CAST(foo as BIGINT)) don't seem to be implemented.

[andrewmbenton]

This is still an issue in v1.18.0: https://play.sqlc.dev/p/3931aa6d071068031e5eb03f4e40bd474d22afb2e47aabe0c34e68101e76d2f1.

There is already an issue open for adding CAST support: #687

[abh]

It didn't work for my actual use case, but I noticed that in a simple use case doing a sub-select sometimes(?) makes sqlc figure out the correct type.

select id from (select min(id) from authors) as min_author;

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