TiDB5.0.0 UNION語法導致ERROR 1105 (HY000): runtime error: index out of range [-1]

jimmyjan0824 · 2021 年4 月 26 日 03:02

【TiDB 版本】v5.0.0

【问题描述】:
以下語法在做UNION時會導致ERROR 1105 (HY000): runtime error: index out of range [-1]
但分開來下時是可以正常返回結果集的。

另外目前我們的sql_mode已先將ONLY_FULL_GROUP_BY拿掉

SELECT
s.member_login,
SUM(s.transfer_amount) AS amount
FROM deposit AS s
LEFT JOIN view_member_agents AS v ON s.member_login = v.member
WHERE 1 = 1
AND (IFNULL(“2021-04-01 04:00:00”, -99) = -99 OR s.accept_time >= “2021-04-01 04:00:00”)
AND (IFNULL(“2021-04-24 04:00:00”, -99) = -99 OR s.accept_time < “2021-04-24 04:00:00”)
AND (IFNULL(NULL, ‘empty’) = ‘empty’ OR v.agent IN (NULL))
AND (IFNULL(NULL, ‘empty’) = ‘empty’ OR v.general_agent IN (NULL))
AND (IFNULL(“TRUE”, ‘empty’) = ‘empty’ OR v.share_login IN (“qatest0114-1”,“alisachang”))
AND s.status = 1
AND s.transfer_amount <> 0
GROUP BY s.member_login
UNION
SELECT
s.member_login,
SUM(s.transfer_amount) AS amount
FROM mandeposit AS s
LEFT JOIN view_member_agents AS v ON s.member_login = v.member
WHERE 1 = 1
AND (IFNULL(“2021-04-01 04:00:00”, -99) = -99 OR s.accept_time >= “2021-04-01 04:00:00”)
AND (IFNULL(“2021-04-24 04:00:00”, -99) = -99 OR s.accept_time < “2021-04-24 04:00:00”)
AND (IFNULL(NULL, ‘empty’) = ‘empty’ OR v.agent IN (NULL))
AND (IFNULL(NULL, ‘empty’) = ‘empty’ OR v.general_agent IN (NULL))
AND (IFNULL(“TRUE”, ‘empty’) = ‘empty’ OR v.share_login IN (“qatest0114-1”,“alisachang”))
AND s.action_code = 9
AND s.transfer_amount <> 0
GROUP BY s.member_login;

fzhedu · 2021 年4 月 26 日 03:41

请把 error log 以及这个 sql 涉及的建表语句上传一下

Kongdom · 2021 年4 月 26 日 03:59

盲猜是这两句导致的，升级到5.0后遇到过不同数据类型操作时报错的情况，改成 IFNULL(,‘’) = ''试试

jimmyjan0824 · 2021 年4 月 26 日 04:07

顧問好,

提供如下:

tidb log:
tidb1.log.tar.gz (1.1 MB)

Schema:
1. deposit
CREATE TABLE deposit (
id char(36) NOT NULL,
added_time datetime(6) NOT NULL,
member_login varchar(50) NOT NULL,
account_number varchar(30) NOT NULL,
account_name varchar(20) NOT NULL,
account_bank_code varchar(10) NOT NULL,
account_bank_name varchar(30) NOT NULL,
currency_code char(3) NOT NULL,
transfer_amount decimal(18,2) NOT NULL DEFAULT ‘0.00’,
transfer_charge decimal(18,2) NOT NULL DEFAULT ‘0.00’,
transfer_method varchar(45) DEFAULT NULL,
transfer_audit_rate decimal(18,2) DEFAULT ‘1.00’,
transfer_audit_due decimal(18,2) DEFAULT NULL,
transfer_audit_charge decimal(18,2) DEFAULT NULL,
transfer_time datetime NOT NULL,
card_number varchar(30) NOT NULL,
card_name varchar(20) NOT NULL,
card_bank_code varchar(10) NOT NULL,
card_bank_name varchar(20) NOT NULL,
discount_id char(36) DEFAULT NULL,
discount_name varchar(45) DEFAULT NULL,
discount_amount decimal(18,2) DEFAULT NULL,
discount_audit_rate decimal(18,2) DEFAULT NULL,
discount_audit_due decimal(18,2) DEFAULT NULL,
status int(11) NOT NULL DEFAULT ‘0’,
remark varchar(500) DEFAULT NULL,
deposit_amount decimal(18,2) DEFAULT NULL,
accept_time datetime(6) DEFAULT NULL,
accept_login varchar(45) DEFAULT NULL,
audit_status int(11) NOT NULL DEFAULT ‘0’,
audit_time datetime(6) DEFAULT NULL,
audit_login varchar(45) DEFAULT NULL,
withdraw_id char(36) DEFAULT NULL,
sign char(128) NOT NULL COMMENT,
PRIMARY KEY (id) /*T![clustered_index] NONCLUSTERED */,
KEY idx_added (added_time),
KEY idx_accept (accept_time),
KEY idx_status (member_login,status,audit_status,added_time,accept_time),
KEY idx_account_number_card_name_added_time (account_number,card_name,added_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

2. mandeposit
CREATE TABLE mandeposit (
id char(36) NOT NULL,
audit_id int(11) DEFAULT NULL,
added_time datetime(6) NOT NULL COMMENT,
member_login varchar(50) NOT NULL,
currency_code char(3) NOT NULL,
transfer_amount decimal(18,2) NOT NULL DEFAULT ‘0.00’,
transfer_audit_rate decimal(18,2) DEFAULT NULL,
transfer_audit_due decimal(18,2) DEFAULT NULL,
transfer_audit_charge decimal(18,2) DEFAULT NULL,
discount_id char(36) DEFAULT NULL,
discount_name varchar(45) DEFAULT NULL,
discount_amount decimal(18,2) DEFAULT NULL,
discount_audit_point decimal(18,2) DEFAULT NULL,
discount_audit_rate decimal(18,2) DEFAULT NULL,
discount_audit_due decimal(18,2) DEFAULT NULL,
status tinyint(4) NOT NULL DEFAULT ‘0’,
remark varchar(500) DEFAULT NULL,
deposit_amount decimal(18,2) DEFAULT NULL,
accept_time datetime(6) DEFAULT NULL,
accept_login varchar(45) DEFAULT NULL,
audit_status int(11) NOT NULL DEFAULT ‘0’,
audit_time datetime(6) DEFAULT NULL,
audit_login varchar(45) DEFAULT NULL,
withdraw_id char(36) DEFAULT NULL,
sign char(128) NOT NULL,
action_code varchar(10) DEFAULT NULL,
action_name varchar(150) DEFAULT NULL,
account_number varchar(30) DEFAULT NULL,
account_name varchar(20) DEFAULT NULL,
merchant_id varchar(36) DEFAULT NULL,
merchant_name varchar(50) DEFAULT NULL,
PRIMARY KEY (id) /*T![clustered_index] NONCLUSTERED */,
KEY idx_added (added_time),
KEY idx_accept (accept_time),
KEY idx_status (member_login,status,audit_status,added_time,accept_time),
KEY idx_audit_id (audit_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

3. view_member_agents
CREATE VIEW view_member_agents (member, member_added_time, name, level_code, user_level_code, agent, general_agent, share_login) AS SELECT m.login AS member,m.added_time AS member_added_time,m.name AS name,IFNULL(a.level_code, 0) AS level_code,m.level_code AS user_level_code,m.agent_login AS agent,a.agent_login AS general_agent,a.share_login AS share_login FROM cdd.member AS m LEFT JOIN cdd.agent AS a ON ((m.agent_login=a.login));

view有使用到的table
一. member
CREATE TABLE member (
login varchar(50) NOT NULL,
name varchar(45) DEFAULT NULL,
level_code int(11) DEFAULT NULL,
status int(11) DEFAULT ‘1’,
locked int(11) DEFAULT ‘0’,
passwd char(128) NOT NULL,
security_code char(128) NOT NULL,
salt char(36) NOT NULL,
currency_code char(3) DEFAULT NULL,
credit decimal(18,2) NOT NULL DEFAULT ‘0.00’,
balance decimal(18,2) NOT NULL DEFAULT ‘0.00’,
agent_login varchar(100) DEFAULT NULL,
referral_login varchar(45) NOT NULL,
deposit_times int(11) NOT NULL DEFAULT ‘0’,
deposit_total decimal(18,2) NOT NULL DEFAULT ‘0.00’ ,
deposit_max decimal(18,2) NOT NULL DEFAULT ‘0.00’ ,
withdraw_times int(11) NOT NULL DEFAULT ‘0’,
withdraw_total decimal(18,2) NOT NULL DEFAULT ‘0.00’ ,
added_time datetime DEFAULT NULL,
last_login_time datetime DEFAULT NULL ,
failed_attempt_count int(11) NOT NULL DEFAULT ‘0’,
note varchar(500) DEFAULT NULL ,
special_note varchar(200) DEFAULT NULL ,
profile blob DEFAULT NULL,
lang_code char(5) NOT NULL ,
lang_name varchar(45) DEFAULT NULL,
card_number varchar(30) DEFAULT NULL,
card_bank_name varchar(50) DEFAULT NULL,
card_branch varchar(100) DEFAULT NULL,
updated_time datetime(6) DEFAULT NULL,
sign char(128) DEFAULT NULL,
source varchar(500) DEFAULT NULL ,
card_city varchar(45) DEFAULT NULL,
is_trial tinyint(4) NOT NULL DEFAULT ‘0’,
trial_ended_time datetime(6) DEFAULT NULL,
lv_updated_time datetime(6) DEFAULT NULL,
transfer_limit int(11) NOT NULL DEFAULT ‘0’,
domain varchar(45) DEFAULT NULL,
frozen_status tinyint(4) NOT NULL DEFAULT ‘0’,
name_passed tinyint(4) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (login) /*T![clustered_index] NONCLUSTERED */,
KEY idx_member_name (name),
KEY idx_time (added_time),
KEY idx_level_code_balance_credit (level_code,balance,credit),
KEY idx_agent_login (agent_login)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

二. agent
CREATE TABLE agent (
login varchar(50) NOT NULL,
level_code int(11) NOT NULL DEFAULT ‘0’,
name varchar(45) DEFAULT NULL,
status int(11) DEFAULT ‘1’,
locked int(11) DEFAULT ‘0’,
passwd char(128) NOT NULL,
salt char(36) NOT NULL,
parent_login varchar(100) DEFAULT NULL,
added_time datetime(6) DEFAULT NULL,
last_login_time datetime(6) DEFAULT NULL,
failed_attempt_count int(11) NOT NULL DEFAULT ‘0’,
note varchar(500) DEFAULT NULL,
profile blob DEFAULT NULL,
share_id char(36) DEFAULT NULL,
comm_id char(36) DEFAULT NULL,
domain varchar(500) DEFAULT NULL,
path varchar(1024) DEFAULT NULL,
share_login varchar(45) DEFAULT NULL,
agent_login varchar(45) DEFAULT NULL,
binding_member varchar(50) DEFAULT NULL,
security_code char(4) NOT NULL DEFAULT ‘0000’,
domain_promotion varchar(100) DEFAULT ‘’,
PRIMARY KEY (login) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

jimmyjan0824 · 2021 年4 月 26 日 07:23

經與顧問交涉後已確認為bug
官方已建立issue單
https://github.com/pingcap/tidb/issues/24281

fzhedu · 2021 年4 月 26 日 07:31

是否可以把建表语句用无格式文件上传？或者加上代码引用。我这里copy 下来在本地运行报错

parker · 2021 年5 月 10 日 10:06

当前我们的sql执行也遇到了同样的问题，如何进行升级版本啊

yilong · 2021 年5 月 10 日 11:09

这个报错不一定是相同的问题，请重新开贴，提供错误栈和表结构，以及查询sql 和当前版本
如果要升级，查看下官方文档搜索升级

jim.ni · 2021 年5 月 11 日 03:46

要命，相同的问题，什么时候修复？

system · 2022 年10 月 31 日 19:19

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。