How can i get correct type

[hts0000]

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{}.

[hts0000]

And my sqlc version is v1.30.0.

[rubensantoniorosa2704]

Hello @hts0000. Which database are you using for your schema.sql? PostgreSQL, MySQL, or something else?

[hts0000]

Hello @hts0000. Which database are you using for your schema.sql? PostgreSQL, MySQL, or something else?

I'm using MySQL 5.7.

[rubensantoniorosa2704]

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.

[hts0000]

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
}

[rubensantoniorosa2704]

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:

1. It assumes all math operations return integers (wrong for float division)
2. 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.

[rubensantoniorosa2704]

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.