sum in select result in model field type interface{}

[sosolyht]

Version

1.14.0

What happened?

Hello, I found an error while using sqlc and mysql

return value must be int64

but it's an interface .

need cast to int64

const totalVAT = `-- name: TotalVAT :one
SELECT sum(vat) as vatpence
FROM sales
`

func (q *Queries) TotalVAT(ctx context.Context) (interface{}, error) {
    row := q.db.QueryRowContext(ctx, totalVAT)
    var vatpence interface{}
    err := row.Scan(&vatpence)
    return vatpence, err
}

Relevant log output

No response

Database schema

CREATE TABLE sales (
  vat integer      NOT NULL
);

SQL queries

-- name: TotalVAT :one
SELECT sum(vat) as vatpence
FROM sales;

Configuration

No response

Playground URL

https://play.sqlc.dev/p/6552b583f39af63c0d407f76b62cafdfe471b130c76d9066b9843b8c20b8cd90

What operating system are you using?

macOS

What database engines are you using?

MySQL

What type of code are you generating?

Go

[brlala]

use this as a workaround

SELECT sum(vat) :: integer as vatpence
FROM sales

[sosolyht]

@brlala thank you, but this solution does not work with mysql

[sosolyht]

func convertInterfaceToInt64(t interface{}) (int64, error) {
    switch t := t.(type) {
    case int64:
        return t, nil
    case int:
        return int64(t), nil
    case string:
        return strconv.ParseInt(t, 10, 64)
    case []byte:
        return strconv.ParseInt(string(t), 10, 64)
    default:
        return 0, fmt.Errorf("type %T not supported", t)
    }
}

query is return from interface to byte type

so we can solve this problem

[abh]

Related to #1622

[andrewmbenton]

use this as a workaround

SELECT sum(vat) :: integer as vatpence
FROM sales

As noted, this workaround doesn't work for MySQL. The following does work though:

SELECT CAST(sum(vat) AS unsigned) vatpence
FROM sales;

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