Repository navigation
How can i get correct type #4153
Description
Activity
And my sqlc version is
v1.30.0.Hello @hts0000. Which database are you using for your schema.sql? PostgreSQL, MySQL, or something else?
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
COALESCEtoo, 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 inoutput_columns.go, at line 158. When sqlc encounters a mathematical operation(*ast.A_Expr with IsMathematicalOperator), it hardcodes the result asDataType: "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:191acknowledging 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 tablewould generateinterface{}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.
Reacted by hts0000I ended up with thisd https://github.com/reddec/editstruct
Hi! I've been working on a fix for this issue in PR #4391, which adds type inference for MySQL numeric expressions so that
float / intreturnssql.NullFloat64instead ofinterface{}.I also noticed a related bug in
dolphin/convert.gowhereopcode.Div(float division/) andIntDiv(integer divisiondiv) are swapped — I could open a separate, focused PR just for that fix if it's easier to review.I've also been following the recent changes to the codebase and I'm aware the PR may need to be adapted to fit the new
coreanalysis direction. Happy to work on that if it's useful.Just wanted to share in case it helps anyone hitting this issue!
I want to generate
sql.NullFloat64but gotinterface{}. How can I get the correct type?Here is my
query.sqlandschema.sql.query.sql
scheam.sql
The generated code look like this:
The fields
A1FloatandA2Floatin the structListTestRoware of typeinterface{}.