SQLC Generated Go Code Throws Scan Error for Non-Existent user_id

[qcrao]

Version

1.20.0

What happened?

I am using SQLC to generate Go code for my SQL queries. I have defined the following SQL statement:

-- name: GetUserDepositCountBetween :one
SELECT user_id, COUNT(*) AS deposit_count FROM transaction WHERE user_id = ? and type = 'deposit' and created_at >= sqlc.arg(start) AND created_at < sqlc.arg(end);

When executing the generated Go code, I encounter an error if the user_id is not found in the database. The error message is:

"error":"sql: Scan error on column index 0, name \"user_id\": converting NULL to int64 is unsupported"

This makes it difficult for me to distinguish between a timeout error and a "record not found" error.

Relevant log output

error message: "error":"sql: Scan error on column index 0, name \"user_id\": converting NULL to int64 is unsupported"

Database schema

No response

SQL queries

-- name: GetUserDepositCountBetween :one
SELECT user_id, COUNT(*) AS deposit_count FROM transaction WHERE user_id = ? and type = 'deposit' and created_at >= sqlc.arg(start) AND created_at < sqlc.arg(end);

Configuration

No response

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

MySQL

What type of code are you generating?

Go

[kyleconroy]

We're going to need a bit more information to reproduce this. Can you please share the definition for the transaction table and your sqlc configuration file?

[qcrao]

@kyleconroy Certainly, here are the details you requested:

definition(simplified):

CREATE TABLE IF NOT EXISTS `transaction`
(
    `id`          BIGINT      NOT NULL AUTO_INCREMENT COMMENT 'id',
    `type`        VARCHAR(50) NOT NULL DEFAULT 'deposit' COMMENT '交易类型',
    `user_id`     BIGINT      NOT NULL DEFAULT 0 COMMENT '用户ID',
    `created_at`  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `type_idx` (`type`),
    KEY `user_id_idx` (`user_id`),
    KEY `created_at_idx` (`created_at`)
)
ENGINE = InnoDB
CHARSET = utf8mb4 COMMENT ='交易流水表';

sqlc configuration file:

version: 1
packages:
  - path: "."
    name: "mysql"
    engine: "mysql"
    schema: "sql/schema/"
    queries: "sql"
    emit_json_tags: true
    emit_prepared_queries: false
    emit_interface: false
    emit_exact_table_names: false
    emit_empty_slices: true
    overrides:
      - db_type: "decimal"
        go_type: "github.com/shopspring/decimal.Decimal"

generated code:

const getUserDepositCountBetween = `-- name: GetUserDepositCountBetween :one
SELECT user_id, COUNT(*) AS deposit_count FROM transaction WHERE user_id = ? and type = 'deposit' and created_at >= ? AND created_at < ?
`

type GetUserDepositCountBetweenParams struct {
	UserID int64     `json:"user_id"`
	Start  time.Time `json:"start"`
	End    time.Time `json:"end"`
}

type GetUserDepositCountBetweenRow struct {
	UserID       int64 `json:"user_id"`
	DepositCount int64 `json:"deposit_count"`
}

func (q *Queries) GetUserDepositCountBetween(ctx context.Context, arg GetUserDepositCountBetweenParams) (GetUserDepositCountBetweenRow, error) {
	row := q.db.QueryRowContext(ctx, getUserDepositCountBetween, arg.UserID, arg.Start, arg.End)
	var i GetUserDepositCountBetweenRow
	err := row.Scan(&i.UserID, &i.DepositCount)
	return i, err
}

[orisano]

It seems that in some cases user_id is null, are you sure your query and table definitions are correct?
Can you confirm that user_id is not null when executed directly from mysql client?

[qcrao]

@orisano @kyleconroy

I think the code generated by sqlc should be like this, it should handle the ErrNoRows:

func (q *Queries) GetUserDepositCountBetween(ctx context.Context, arg GetUserDepositCountBetweenParams) (GetUserDepositCountBetweenRow, error) {
	row := q.db.QueryRowContext(ctx, getUserDepositCountBetween, arg.UserID, arg.Start, arg.End)
	var i GetUserDepositCountBetweenRow

//=========should handle ErrNoRows
	if row.Err() == sql.ErrNoRows {
		return i, sql.ErrNoRows
	}
//===========================
	err := row.Scan(&i.UserID, &i.DepositCount)
	return i, err
}

[orisano]

Fixing that won't solve the problem.
https://github.com/golang/go/blob/3466e57354974cd2d17ce902df02e0ac58b8ded5/src/database/sql/sql.go#L3448
In the Scan method, if it is err, it is not processed.

sql: Scan error on column index 0, name \"user_id\": converting NULL to int64 is unsupported

The error message above is that the user_id, which would not be null, is null, causing the Scan to fail.
sql.NullInt64 is not being used because according to the schema and query it is not null.

[kyleconroy]

As @orisano said, either your table definition or query is incorrect, as user_id can not be null, yet you're attempting to scan a NULL value into that parameter.