sqlc-dev
How can i get correct type
I want to generate sql.NullFloat64 but got interface{}. How can I get the correct type?
Here is my query.sql and schema.sql.
query.sql
-- name: ListTest :many
SELECT
(a1 / 1024) a1_float, (a2 / 1024) a2_float, a3
FROM test;
scheam.sql
CREATE TABLE test (
a1 float NULL,
a2 float NULL,
a3 float NULL
);
The generated code look like this:
const listTest = `-- name: ListTest :many
SELECT
(a1 / 1024) a1_float, (a2 / 1024) a2_float, a3
FROM test
`
type ListTestRow struct {
A1Float interface{}
A2Float interface{}
A3 sql.NullFloat64
}
func (q *Queries) ListTest(ctx context.Context) ([]ListTestRow, error) {
rows, err := q.db.QueryContext(ctx, listTest)
if err != nil {
return nil, err
}
defer rows.Close()
var items []ListTestRow
for rows.Next() {
var i ListTestRow
if err := rows.Scan(&i.A1Float, &i.A2Float, &i.A3); 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
}
The fields A1Float and A2Float in the struct ListTestRow are of type interface{}.
Hello @hts0000. Which database are you using for your schema.sql? PostgreSQL, MySQL, or something else?
Hello @hts0000. Which database are you using for your schema.sql? PostgreSQL, MySQL, or something else?
I'm using MySQL 5.7.
Hmm, I wonder if this happens when generating for PostgreSQL or SQLite as well. That might be the first thing to investigate.
I did a quick commit earlier (as you may have seen), but it was a bit rushed — I’d like to take some proper time to really understand this issue.
It seems to occur because the generator doesn’t interpret the result of a division operation as a potentially nullable value. I also tried manually casting it in query.sql with COALESCE, but it still generated interface{}.
I’ll test it further and update this issue as I make progress.
Hmm, I wonder if this happens when generating for PostgreSQL or SQLite as well. That might be the first thing to investigate.
I did a quick commit earlier (as you may have seen), but it was a bit rushed — I’d like to take some proper time to really understand this issue.
It seems to occur because the generator doesn’t interpret the result of a division operation as a potentially nullable value. I also tried manually casting it in query.sql with COALESCE, but it still generated interface{}.
I’ll test it further and update this issue as I make progress.
Thank you for your hard work. I tried using COALESCE too, maybe this will help you a bit.
query.sql
-- name: ListTest :many
SELECT
(a1 / 1024) a1_float, (a2 / 1024) a2_float, a3
FROM test;
-- name: ListTest2 :many
SELECT
COALESCE(CAST(a1 / 1024 AS FLOAT), 0) a1_float, COALESCE(CAST(a2 / 1024 AS FLOAT), 0) a2_float, a3
FROM test;
-- name: ListTest3 :many
SELECT
CAST(a1 / 1024 AS FLOAT) a1_float, CAST(a2 / 1024 AS FLOAT) a2_float, a3
FROM test;
query.sql.go
const listTest = `-- name: ListTest :many
SELECT
(a1 / 1024) a1_float, (a2 / 1024) a2_float, a3
FROM test
`
type ListTestRow struct {
A1Float interface{}
A2Float interface{}
A3 sql.NullFloat64
}
func (q *Queries) ListTest(ctx context.Context) ([]ListTestRow, error) {
rows, err := q.db.QueryContext(ctx, listTest)
if err != nil {
return nil, err
}
defer rows.Close()
var items []ListTestRow
for rows.Next() {
var i ListTestRow
if err := rows.Scan(&i.A1Float, &i.A2Float, &i.A3); 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
}
const listTest2 = `-- name: ListTest2 :many
SELECT
COALESCE(CAST(a1 / 1024 AS FLOAT), 0) a1_float, COALESCE(CAST(a2 / 1024 AS FLOAT), 0) a2_float, a3
FROM test
`
type ListTest2Row struct {
A1Float interface{}
A2Float interface{}
A3 sql.NullFloat64
}
func (q *Queries) ListTest2(ctx context.Context) ([]ListTest2Row, error) {
rows, err := q.db.QueryContext(ctx, listTest2)
if err != nil {
return nil, err
}
defer rows.Close()
var items []ListTest2Row
for rows.Next() {
var i ListTest2Row
if err := rows.Scan(&i.A1Float, &i.A2Float, &i.A3); 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
}
const listTest3 = `-- name: ListTest3 :many
SELECT
CAST(a1 / 1024 AS FLOAT) a1_float, CAST(a2 / 1024 AS FLOAT) a2_float, a3
FROM test
`
type ListTest3Row struct {
A1Float float64
A2Float float64
A3 sql.NullFloat64
}
func (q *Queries) ListTest3(ctx context.Context) ([]ListTest3Row, error) {
rows, err := q.db.QueryContext(ctx, listTest3)
if err != nil {
return nil, err
}
defer rows.Close()
var items []ListTest3Row
for rows.Next() {
var i ListTest3Row
if err := rows.Scan(&i.A1Float, &i.A2Float, &i.A3); 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
}
Summary: I've tested this with MySQL, PostgreSQL, and SQLite, and all three engines exhibit the same behavior.
TLDR:
The root cause is in output_columns.go, at line 158. When sqlc encounters a mathematical operation (*ast.A_Expr with IsMathematicalOperator), it hardcodes the result as DataType: "int" and ``NotNull: true`, without analyzing the operand types.
case lang.IsMathematicalOperator(op):
cols = append(cols, &Column{Name: name, DataType: "int", NotNull: true})
This causes two problems:
- It assumes all math operations return integers (wrong for float division)
- It assumes results are always non-null (wrong when operating on nullable columns)
Since the inferred type "int" doesn't match MySQL's actual expression result type, it falls back to interface{} in mysql_type.go:137.
default:
[...]
return "interface{}"
I tested this with multiple scenarios:
- a1 / 1024 → generates interface{}
- CAST(a1 / 1024 AS FLOAT) → correctly generates float64 (because TypeCast is handled separately)
- Direct column a3 → correctly generates sql.NullFloat64
The code even has a TODO comment in output_columns.go:191 acknowledging this limitation:
// TODO: The TypeCase and A_Const code has been copied from below. Instead, we
// need a recurse function to get the type of a node.
Fix approach:
To properly fix this, we'd need to:
- Recursively analyze operand types (Lexpr, Rexpr) in mathematical expressions
- Infer the result type based on operands and operator (e.g., float/int → float)
- Preserve nullability when any operand is nullable
- This would require implementing the type inference function mentioned in the TODO.
I'm happy to work on a PR if this approach sounds reasonable to the maintainers.
I've implemented type inference for MySQL mathematical expressions in PR #4172. The main problem was that expressions like SELECT a1 / 1024 FROM table would generate interface{} instead of the actual type.
The solution adds a recursive type inference system that:
- Resolves column types from the schema
- Applies MySQL-specific rules for operators (division returns decimal, float operations return float)
- Propagates nullability correctly (nullable column in expression makes result nullable)
Here's what the generated code looks like now:
// Simple division - generates string (MySQL DECIMAL)
type ListTestRow struct {
A1Float string `json:"a1_float"` // a1 / 1024 (NOT NULL)
A2Float sql.NullString `json:"a2_float"` // a2 / 1024 (nullable)
A3 float64 `json:"a3"` // a3 / 1024 (float)
}
// Arithmetic with nullability
type ListTest4Row struct {
SumResult sql.NullInt32 `json:"sum_result"` // a1 + a2 (nullable because a2 is)
MultResult sql.NullInt32 `json:"mult_result"` // a1 * a2 (nullable)
SubResult sql.NullInt32 `json:"sub_result"` // a1 - a2 (nullable)
ModResult int32 `json:"mod_result"` // a1 % 1024 (NOT NULL)
}
// COALESCE now infers types
type ListTest5Row struct {
WithInference string `json:"with_inference"` // COALESCE(a1 / 1024, 0)
}
I've added test examples in examples/type-inference-test/ that cover the different scenarios. The implementation is MySQL-specific - other engines fall back to the current behavior.
Let me know if you'd like me to adjust anything or add more test cases.