sqlc-dev
MIN() returns an interface{}
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
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.
@d-tsuji I'm using MySQL for this project and the equivalent features (CAST(foo as BIGINT)) don't seem to be implemented.
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: https://github.com/kyleconroy/sqlc/issues/687
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
The workaround I use is to add the result to 0. SELECT 0 + count(value) FROM table; gets correctly recognized as an int.
