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