Slice bounds out of range error caused by using duplicate key

[Orion7r]

Bug Report

1. Minimal reproduce step (Required)

When I was executing this sql statement, this error appeared, but it was normal in mysql. And, when I removed on duplicate key update, the remaining SQL statements can also be executed normally.

• in tidb v5.1.0
  

• in tidb v5.1.0 without duplicate key update
  

• in mysql 5.7.14
  

2. What did you expect to see? (Required)

I expect to see the same result as mysql 5.7

3. What did you see instead (Required)

ERROR 1105 (HY000): runtime error: slice bounds out of range [26:25]

4. What is your TiDB version? (Required)

• tidb version
  v5.1.0
  
• mysql version
  

5.Others

• Table building statement

CREATE TABLE `cips_chck_detl` (
  `subsys` char(4) NOT NULL,
  `bankcd` varchar(14) NOT NULL,
  `chckdt` varchar(8) NOT NULL,
  `iotype` char(1) DEFAULT NULL,
  `mesgtp` varchar(20) NOT NULL,
  `sendog` varchar(20) DEFAULT NULL,
  `crdbtg` char(4) DEFAULT NULL,
  `cipsam` decimal(18,2) DEFAULT NULL,
  `cipsnm` int(11) DEFAULT NULL,
  `blncdt` varchar(8) DEFAULT NULL,
  `blncbt` varchar(8) DEFAULT NULL,
  `cipsst` char(4) DEFAULT NULL,
  `fcbpdt` varchar(8) DEFAULT NULL,
  `fcbpsq` varchar(20) DEFAULT NULL,
  `tranam` decimal(18,2) DEFAULT NULL,
  `trannm` int(11) DEFAULT NULL,
  `procst` char(4) DEFAULT NULL,
  `chckst` char(1) DEFAULT NULL,
  `brchno` varchar(20) DEFAULT NULL,
  `mssgid` varchar(35) DEFAULT NULL,
  `mybldt` varchar(8) DEFAULT NULL,
  `dnbztg` char(4) DEFAULT NULL,
  `adjtbk` varchar(20) DEFAULT NULL,
  `oprttp` varchar(10) DEFAULT NULL,
  `chcktp` char(1) DEFAULT NULL,
  `chcksc` char(1) DEFAULT NULL,
  KEY `idx_cnap_chck_detl_chckdt` (`chckdt`),
  KEY `idx_cips_chck_detl_all` (`chckdt`,`subsys`,`chckst`,`mesgtp`,`sendog`),
  KEY `idx_cnap_chck_detl_mssgid` (`mssgid`,`chckdt`,`dnbztg`,`iotype`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='table1';



CREATE TABLE `cips_m111` (
  `fcbpdt` char(8) NOT NULL,
  `fcbpsq` varchar(20) NOT NULL,
  `chnlsq` varchar(30) DEFAULT NULL,
  `iotype` char(1) NOT NULL,
  `mesgtp` varchar(35) DEFAULT NULL,
  `procst` char(4) DEFAULT NULL,
  `cipstx` varchar(105) DEFAULT NULL,
  `cipsst` char(4) DEFAULT NULL,
  `looktg` char(1) DEFAULT NULL,
  `lookrm` varchar(120) DEFAULT NULL,
  `repttg` char(1) DEFAULT NULL,
  `transt` char(1) DEFAULT NULL,
  `sendcl` varchar(35) DEFAULT NULL,
  `mssgid` varchar(35) DEFAULT NULL,
  `mesgtm` varchar(19) DEFAULT NULL,
  `etebid` varchar(35) DEFAULT NULL,
  `busils` varchar(35) DEFAULT NULL,
  `crcycd` varchar(3) DEFAULT NULL,
  `tranam` decimal(18,2) DEFAULT NULL,
  `intbdt` char(8) DEFAULT NULL,
  `mesglv` varchar(4) DEFAULT NULL,
  `setltm` varchar(19) DEFAULT NULL,
  `ocrycd` varchar(3) DEFAULT NULL,
  `otraam` decimal(18,2) DEFAULT NULL,
  `exchra` decimal(21,10) DEFAULT NULL,
  `mgfetp` varchar(4) DEFAULT NULL,
  `freetg` char(1) DEFAULT NULL,
  `pyercl` varchar(35) DEFAULT NULL,
  `pyercd` varchar(35) DEFAULT NULL,
  `pyeecl` varchar(35) DEFAULT NULL,
  `pyeecd` varchar(35) DEFAULT NULL,
  `agenog` varchar(35) DEFAULT NULL,
  `agenna` varchar(140) DEFAULT NULL,
  `agseog` varchar(35) DEFAULT NULL,
  `agsena` varchar(140) DEFAULT NULL,
  `pyerna` varchar(140) DEFAULT NULL,
  `pyerad` varchar(497) DEFAULT NULL,
  `pyerog` varchar(35) DEFAULT NULL,
  `prcrno` varchar(35) DEFAULT NULL,
  `prcrtp` varchar(2) DEFAULT NULL,
  `pradcd` varchar(2) DEFAULT NULL,
  `pyerac` varchar(34) DEFAULT NULL,
  `pyerbk` varchar(35) DEFAULT NULL,
  `prbkna` varchar(140) DEFAULT NULL,
  `prbkad` varchar(497) DEFAULT NULL,
  `prbkac` varchar(34) DEFAULT NULL,
  `pyeebk` varchar(35) DEFAULT NULL,
  `pebkna` varchar(140) DEFAULT NULL,
  `pebkad` varchar(497) DEFAULT NULL,
  `pebkac` varchar(34) DEFAULT NULL,
  `pyeena` varchar(140) DEFAULT NULL,
  `pyeead` varchar(497) DEFAULT NULL,
  `pyeeog` varchar(35) DEFAULT NULL,
  `pecrno` varchar(35) DEFAULT NULL,
  `pecrtp` varchar(2) DEFAULT NULL,
  `peadcd` varchar(2) DEFAULT NULL,
  `pyeeac` varchar(34) DEFAULT NULL,
  `progcd` varchar(35) DEFAULT NULL,
  `prognc` varchar(140) DEFAULT NULL,
  `peogcd` varchar(35) DEFAULT NULL,
  `peognc` varchar(140) DEFAULT NULL,
  `pyeemk` text DEFAULT NULL,
  `oderno` varchar(35) DEFAULT NULL,
  `oderna` text DEFAULT NULL ,
  `remark` text DEFAULT NULL ,
  `retnmk` varchar(140) DEFAULT NULL ,
  `setldt` char(8) DEFAULT NULL ,
  `pereid` varchar(60) DEFAULT NULL,
  `peeeid` varchar(60) DEFAULT NULL,
  `dyngtg` varchar(4) DEFAULT NULL,
  `asgnst` char(1) NOT NULL,
  `systno` varchar(20) DEFAULT NULL,
  `brchno` varchar(12) DEFAULT NULL,
  `userid` varchar(12) DEFAULT NULL,
  `ckbkus` varchar(12) DEFAULT NULL,
  `authus` varchar(12) DEFAULT NULL,
  `zztrtm` varchar(20) DEFAULT NULL,
  `rejetx` varchar(300) DEFAULT NULL,
  `trantg` char(1) DEFAULT NULL,
  `authst` char(1) DEFAULT NULL,
  `rqsyid` varchar(10) DEFAULT NULL,
  `zzcltm` varchar(20) DEFAULT NULL,
  `dcmtnm` int(11) DEFAULT NULL,
  `afeetg` char(1) DEFAULT NULL,
  `afeeam` decimal(18,2) DEFAULT NULL,
  `afeecd` varchar(20) DEFAULT NULL,
  `afeeac` varchar(32) DEFAULT NULL,
  `afeena` varchar(180) DEFAULT NULL,
  `dcmttp` varchar(4) DEFAULT NULL,
  `dcmtno` varchar(32) DEFAULT NULL,
  `modfac` varchar(32) DEFAULT NULL,
  `modfna` varchar(180) DEFAULT NULL,
  `blncdt` varchar(8) DEFAULT NULL,
  `blncbt` varchar(4) DEFAULT NULL,
  `ckprst` char(1) DEFAULT NULL,
  `opertp` char(1) DEFAULT NULL,
  `actndc` varchar(128) DEFAULT NULL ,
  `pyerar` varchar(4) DEFAULT NULL ,
  `pyeear` varchar(4) DEFAULT NULL ,
  `pyerat` char(1) DEFAULT NULL ,
  `pyeeat` char(1) DEFAULT NULL ,
  `blanmd` varchar(2) DEFAULT NULL ,
  `ifrefd` char(1) DEFAULT NULL ,
  `oinlno` varchar(24) DEFAULT NULL ,
  `balopc` varchar(6) DEFAULT NULL ,
  `bustyc` varchar(6) DEFAULT NULL ,
  `bustyl` varchar(8) DEFAULT NULL ,
  `iprpym` char(1) DEFAULT NULL ,
  `prpysc` decimal(4,2) DEFAULT NULL ,
  `acperd` int(11) DEFAULT NULL ,
  `custst` varchar(4) DEFAULT NULL ,
  `cerfno` varchar(32) DEFAULT NULL ,
  `contno` varchar(24) DEFAULT NULL ,
  `tranrl` varchar(2) DEFAULT NULL ,
  `trantp` varchar(2) DEFAULT NULL ,
  `peebic` varchar(11) DEFAULT NULL ,
  `tranpp` varchar(6) DEFAULT NULL ,
  `trbstp` varchar(4) DEFAULT NULL ,
  PRIMARY KEY (`fcbpdt`,`fcbpsq`) /*T![clustered_index] NONCLUSTERED */,
  KEY `idx_cips_m111_2` (`fcbpdt`,`sendcl`,`mssgid`),
  KEY `pk_cips_m111_3` (`chnlsq`),
  KEY `pk_cips_m111_4` (`pyerac`,`pyerna`,`tranam`,`pyeeac`,`pyeebk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='流水表';

• SQL statement executed

insert into cips_chck_detl 
( subsys, bankcd, chckdt, iotype, mesgtp, sendog, crdbtg, cipsam, cipsnm, blncdt, blncbt, cipsst, fcbpdt, fcbpsq, tranam, trannm, procst, chckst, brchno, mssgid, mybldt, dnbztg, chcktp, chcksc) 
select 'CIPS', 'ADBNCNBJXXX', '20210827', d.iotype, d.mesgtp, d.sendcl, null, 0, 0, null,null, null, d.fcbpdt, d.fcbpsq, d.tranam, 1 as trannm, case iotype when '0' then procst else cipsst end as procst, 
'0', d.brchno, d.mssgid, d.blncdt, d.dyngtg, 'T', '1' 
from 
cips_m111 d 
where d.blncdt = '20210827' and 'ADBNCNBJXXX' in (d.pyercl,d.pyeecl) and d.dyngtg= 'DAYT' and d.mesgtp = 'cips.111.001.02' and iotype = case when ' CRDT' = 'DBIT' then '0' else '1' end 
and procst not in ('CH**','CH*0','CH00','CH05','CH08','CH13','CH18','CH19') 
on duplicate key 
update 
fcbpdt = d.fcbpdt, fcbpsq = d.fcbpsq, tranam = d.tranam, trannm = 1, procst = d.procst, mybldt = d.blncdt, brchno = d.brchno, chckst = '0';

[kennytm]

stack trace:

github.com/pingcap/tidb/server.(*clientConn).Run.func1()
	~/pingcap/tidb/server/conn.go:918 +0x8f
panic({0x2e884e0, 0xc014d23d70})
	//Go/src/runtime/panic.go:1038 +0x215
github.com/pingcap/tidb/executor.(*ExecStmt).Exec.func1()
	~/pingcap/tidb/executor/adapter.go:334 +0x3c6
panic({0x2e884e0, 0xc014d23d70})
	//Go/src/runtime/panic.go:1038 +0x215
github.com/pingcap/tidb/executor.(*InsertExec).initEvalBuffer4Dup(0xc0152ad140)
	~/pingcap/tidb/executor/insert.go:359 +0x615
github.com/pingcap/tidb/executor.(*InsertExec).Open(0xc0152ad140, {0x3581670, 0xc0152a8180})
	~/pingcap/tidb/executor/insert.go:330 +0x108
github.com/pingcap/tidb/executor.(*ExecStmt).Exec(0xc014ffbe10, {0x3581670, 0xc0152a8180})
	~/pingcap/tidb/executor/adapter.go:379 +0x54b
github.com/pingcap/tidb/session.runStmt({0x3581670, 0xc015202ea0}, 0xc01034f000, {0x35952c8, 0xc014ffbe10})
	~/pingcap/tidb/session/session.go:1675 +0x55d
github.com/pingcap/tidb/session.(*session).ExecuteStmt(0xc01034f000, {0x3581670, 0xc015202ea0}, {0x35ac970, 0xc015135c70})
	~/pingcap/tidb/session/session.go:1569 +0xace
github.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt(0xc000664ed0, {0x3581670, 0xc015202ea0}, {0x35ac970, 0xc015135c70})
	~/pingcap/tidb/server/driver_tidb.go:219 +0x44
github.com/pingcap/tidb/server.(*clientConn).handleStmt(0xc010bbe360, {0x35815c8, 0xc014dd73c0}, {0x35ac970, 0xc015135c70}, {0x53dbcd0, 0x0, 0x0}, 0x1)
	~/pingcap/tidb/server/conn.go:1843 +0x167
github.com/pingcap/tidb/server.(*clientConn).handleQuery(0xc010bbe360, {0x35815c8, 0xc014dd73c0}, {0xc014e83001, 0x3a1})
	~/pingcap/tidb/server/conn.go:1707 +0x887
github.com/pingcap/tidb/server.(*clientConn).dispatch(0xc010bbe360, {0x3581670, 0xc0107b7410}, {0xc014e83000, 0x3a2, 0x3a2})
	~/pingcap/tidb/server/conn.go:1217 +0xe95
github.com/pingcap/tidb/server.(*clientConn).Run(0xc010bbe360, {0x3581670, 0xc0107b7410})
	~/pingcap/tidb/server/conn.go:979 +0x24b
github.com/pingcap/tidb/server.(*Server).onConn(0xc0107c49c0, 0xc010bbe360)
	~/pingcap/tidb/server/server.go:506 +0x966
created by github.com/pingcap/tidb/server.(*Server).startNetworkListener
	~/pingcap/tidb/server/server.go:409 +0x50f

tidb/executor/insert.go

Line 359 in 7b79dd1

evalBufferTypes = append(evalBufferTypes, e.SelectExec.base().retFieldTypes[numWritableCols:]...)

[kennytm]

simplified repro:

drop table if exists ccd;
drop table if exists cm1;
create table ccd (s int, b int, p int);
create table cm1 (p int);
insert into ccd (p) select 3 from cm1 on duplicate key update p = cm1.p;
-- ERROR 1105 (HY000): runtime error: slice bounds out of range [3:2]

[Orion7r]

Will this bug be fixed in the next small version?

[guo-shaoge]

@aytrack We'd better upgrade the severity of the problem to critical, because it's troublesome to bypass the problem, and it's best to cherry pick all branches.

[Orion7r]

As early as a month ago, I mentioned a pr for this bug to fix this problem, but I haven't gotten a reply yet.

[guo-shaoge]

Thank you for your contributation. And you can ask for review by commenting /cc @xxx if your pr is ready. Otherwise we may not know the state of your PR~

[Orion7r]

Get it, thanks~

[XuHuaiyu]

dup with #30224, fixed already

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.

[cfzjywxk]

As this is a blocking issue, raise the severity to critical.

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.

[cfzjywxk]

@zyguan
By now it is only merged on the master branch, When should we close the issue? Or should it close it after all the cherry-picks are merged?

[zyguan]

@zyguan By now it is only merged on the master branch, When should we close the issue? Or should it close it after all the cherry-picks are merged?

We can close a issue once all cherry-pick PRs are created.

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.

[Xunday]

Hello @Orion7r ! I am doing research CIPS and clearly understand that I need help. In this topic I found that you know CIPS. If you can help me with some question please write me on email vksyuha1311@gmail.com.
Thank you in advance!
P:S: I apologize for disturbing