The generated SQL code has an incorrect data type. #4250
Description
Version
1.30.0
What happened?
TitlePrefix should be sql.NullString instead of interface{}
Relevant log output
type GetTicketsParams struct {
StartTime sql.NullTime `json:"startTime"`
EndTime sql.NullTime `json:"endTime"`
TitlePrefix interface{} `json:"titlePrefix"`
ProblemID sql.NullInt64 `json:"problemId"`
ProblemModuleID sql.NullInt64 `json:"problemModuleId"`
}
type GetTicketsRow struct {
ID int64 `json:"id"`
Title string `json:"title"`
TicketStatus int8 `json:"ticketStatus"`
ProblemTypeID int64 `json:"problemTypeId"`
ProblemModuleID int64 `json:"problemModuleId"`
PriorityID int32 `json:"priorityId"`
SubmitterID int64 `json:"submitterId"`
CurrentHandlerID int64 `json:"currentHandlerId"`
CreatedAt time.Time `json:"createdAt"`
UpdatedAt time.Time `json:"updatedAt"`
TestConfirmedAt sql.NullTime `json:"testConfirmedAt"`
MarkedProcessedAt sql.NullTime `json:"markedProcessedAt"`
ProcessingDuration string `json:"processingDuration"`
Description sql.NullString `json:"description"`
PriorityName sql.NullString `json:"priorityName"`
ProblemTypeName sql.NullString `json:"problemTypeName"`
ModuleName sql.NullString `json:"moduleName"`
}
func (q *Queries) GetTickets(ctx context.Context, arg *GetTicketsParams) ([]*GetTicketsRow, error) {
rows, err := q.db.QueryContext(ctx, getTickets,
arg.StartTime,
arg.StartTime,
arg.EndTime,
arg.EndTime,
arg.TitlePrefix,
arg.TitlePrefix,
arg.ProblemID,
arg.ProblemID,
arg.ProblemModuleID,
arg.ProblemModuleID,
)
if err != nil {
return nil, err
}
defer rows.Close()
var items []*GetTicketsRow
for rows.Next() {
var i GetTicketsRow
if err := rows.Scan(
&i.ID,
&i.Title,
&i.TicketStatus,
&i.ProblemTypeID,
&i.ProblemModuleID,
&i.PriorityID,
&i.SubmitterID,
&i.CurrentHandlerID,
&i.CreatedAt,
&i.UpdatedAt,
&i.TestConfirmedAt,
&i.MarkedProcessedAt,
&i.ProcessingDuration,
&i.Description,
&i.PriorityName,
&i.ProblemTypeName,
&i.ModuleName,
); 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
}Database schema
-- 工单表
CREATE TABLE ticket
(
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '工单编号',
ticket_status TINYINT NOT NULL COMMENT '工单状态',
problem_type_id BIGINT NOT NULL COMMENT '问题类型ID',
problem_module_id BIGINT NOT NULL COMMENT '问题模块ID',
priority_id INT NOT NULL COMMENT '优先级ID',
submitter_id BIGINT NOT NULL COMMENT '提交人ID',
current_handler_id BIGINT NOT NULL COMMENT '当前处理人ID',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
test_confirmed_at DATETIME NULL COMMENT '测试确认时间',
marked_processed_at DATETIME NULL COMMENT '标记已处理时间',
title VARCHAR(255) NOT NULL COMMENT '工单标题',
description TEXT COMMENT '工单描述',
processing_duration VARCHAR(255) NOT NULL DEFAULT '' COMMENT '处理时长',
INDEX idx_ticket_created_at_status (created_at, ticket_status),
INDEX idx_ticket_type_module_priority (problem_type_id, problem_module_id, priority_id),
INDEX idx_ticket_handler_submitter (current_handler_id, submitter_id),
INDEX idx_ticket_title (title)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='工单表';
-- 模块名称表
CREATE TABLE problem_module
(
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '模块ID',
parent_module_id INT NOT NULL DEFAULT 0 COMMENT '父模块ID，根模块为0',
is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '是否已删除',
module_name VARCHAR(255) NOT NULL COMMENT '模块名称',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
INDEX idx_module_name (module_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='问题模块表';
-- 优先级表
CREATE TABLE priority
(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '优先级ID',
priority_name VARCHAR(255) NOT NULL COMMENT '优先级名称',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
UNIQUE KEY uk_priority_name (priority_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='优先级表';
-- 问题类型表
CREATE TABLE problem_type
(
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '问题类型ID',
problem_type_name VARCHAR(255) NOT NULL COMMENT '问题类型名称',
problem_level TINYINT NOT NULL COMMENT '问题等级',
is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '是否已删除',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
UNIQUE KEY uk_problem_type_name (problem_type_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='问题类型表';SQL queries
-- name: GetTickets :many
-- 工单查询：按创建时间、工单标题前缀、问题类型、问题模块筛选，并按优先级升序、ID升序排序
SELECT
t.id,
t.title,
t.ticket_status,
t.problem_type_id,
t.problem_module_id,
t.priority_id,
t.submitter_id,
t.current_handler_id,
t.created_at,
t.updated_at,
t.test_confirmed_at,
t.marked_processed_at,
t.processing_duration,
t.description,
p.priority_name,
pt.problem_type_name,
pm.module_name
FROM ticket t
LEFT JOIN priority p ON t.priority_id = p.id
LEFT JOIN problem_type pt ON t.problem_type_id = pt.id
LEFT JOIN problem_module pm ON t.problem_module_id = pm.id
WHERE (sqlc.narg(start_time) IS NULL OR t.created_at >= sqlc.narg(start_time))
AND (sqlc.narg(end_time) IS NULL OR t.created_at <= sqlc.narg(end_time))
AND (sqlc.narg(title_prefix) IS NULL OR t.title LIKE CONCAT(sqlc.narg(title_prefix), '%'))
AND (sqlc.narg(problem_id) IS NULL OR t.problem_type_id = sqlc.narg(problem_id))
AND (sqlc.narg(problem_module_id) IS NULL OR t.problem_module_id = sqlc.narg(problem_module_id))
ORDER BY t.priority_id ASC, t.id ASC;Configuration
version: "2"
sql:
- engine: "mysql"
# 查询sql存放目录
queries: "mysql/query"
# 数据库schema存放目录
schema: "mysql/schema"
rules:
- sqlc/db-prepare
- mysql-query-too-costly
gen:
go:
package: "dal"
out: "internal/data/dal"
emit_json_tags: true
emit_result_struct_pointers: true
# 当前只支持postgres，不支持mysql，保留配置等以后使用
emit_params_struct_pointers: true
emit_pointers_for_null_types: true
json_tags_case_style: "camel"Playground URL
No response
What operating system are you using?
No response
What database engines are you using?
No response
What type of code are you generating?
No response