prefix index + index join - innerWorker panicked

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

source cluster_index.PK_MULTI_COL_3049.000000000.txt

 select/*+ inl_merge_join(t1) */ * from PK_MULTI_COL_3049 t1 join PK_MULTI_COL_3049 t2 on t1.col1 = t2.col1 where t1. col1 > "衹鵂曜供耓脮螘堦豓俁" and t2. col1 = any (select col1 from PK_MULTI_COL_3049)

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

query successfully

3. What did you see instead (Required)

tidb report an error

mysql root@172.16.5.39:cluster_index> select/*+ inl_merge_join(t1) */ * from PK_MULTI_COL_3049 t1 join PK_MULTI_COL_3049 t2 on t1.col1 = t2.col1 where t1. col1 > "衹鵂曜供耓脮螘堦豓俁" and t2. col1 = any (select col1 from PK_MULTI_COL_3049);
(1105, 'runtime error: index out of range [3] with length 1')

tidb log

[2021/04/07 17:22:31.017 +08:00] [ERROR] [index_lookup_join.go:452] ["innerWorker panicked"] [conn=602965] [stack="goroutine 1202211274 [running]:\ngithub.com/pingcap/tidb/executor.(*innerWorker).run.func1(0x3a2ae70, 0xc22e6460c0, 0xc2148baf30, 0xc24d29c394)\n\t/home/tidb/pingcap/tidb/executor/index_lookup_join.go:450 +0xd7\npanic(0x338b8e0, 0xc2ab561890)\n\t/usr/local/go/src/runtime/panic.go:965 +0x1b9\ngithub.com/pingcap/tidb/executor.(*innerWorker).constructLookupContent(0xc11a1f8500, 0xc314d88a00, 0x0, 0x0, 0x0, 0x0, 0x0)\n\t/home/tidb/pingcap/tidb/executor/index_lookup_join.go:535 +0x9d1\ngithub.com/pingcap/tidb/executor.(*innerWorker).handleTask(0xc11a1f8500, 0x3a2ae70, 0xc22e6460c0, 0xc314d88a00, 0x0, 0x0)\n\t/home/tidb/pingcap/tidb/executor/index_lookup_join.go:487 +0x85\ngithub.com/pingcap/tidb/executor.(*innerWorker).run(0xc11a1f8500, 0x3a2ae70, 0xc22e6460c0, 0xc24d29c394)\n\t/home/tidb/pingcap/tidb/executor/index_lookup_join.go:469 +0x145\ncreated by github.com/pingcap/tidb/executor.(*IndexLookUpJoin).startWorkers\n\t/home/tidb/pingcap/tidb/executor/index_lookup_join.go:182 +0x205\n"]
[2021/04/07 17:22:31.018 +08:00] [INFO] [conn.go:812] ["command dispatched failed"] [conn=602965] [connInfo="id:602965, addr:10.9.85.17:56606 status:10, collation:utf8_general_ci, user:root"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="execute stmt using @a"] [txn_mode=PESSIMISTIC] [err="runtime error: index out of range [3] with length 1\ngithub.com/pingcap/tidb/executor.(*innerWorker).run.func1\n\t/home/tidb/pingcap/tidb/executor/index_lookup_join.go:454\nruntime.gopanic\n\t/usr/local/go/src/runtime/panic.go:965\nruntime.goPanicIndex\n\t/usr/local/go/src/runtime/panic.go:88\ngithub.com/pingcap/tidb/executor.(*innerWorker).constructLookupContent\n\t/home/tidb/pingcap/tidb/executor/index_lookup_join.go:535\ngithub.com/pingcap/tidb/executor.(*innerWorker).handleTask\n\t/home/tidb/pingcap/tidb/executor/index_lookup_join.go:487\ngithub.com/pingcap/tidb/executor.(*innerWorker).run\n\t/home/tidb/pingcap/tidb/executor/index_lookup_join.go:469\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1371"]

4. What is your TiDB version? (Required)

release-5.0: e2740f5

[time-and-fate]

A simpler method to reproduce:

CREATE TABLE `tbl_5` (
  `col_25` double NOT NULL,
  `col_26` decimal(15,4) NOT NULL DEFAULT '916569.109',
  `col_27` blob NOT NULL,
  PRIMARY KEY (`col_27`(1),`col_25`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `tbl_3` (
  `col_15` enum('Alice','Bob','Charlie','David') NOT NULL DEFAULT 'Bob',
  `col_16` date NOT NULL,
  `col_17` smallint(5) unsigned NOT NULL,
  `col_18` enum('Alice','Bob','Charlie','David') NOT NULL,
  `col_19` binary(65) NOT NULL DEFAULT 'uC',
  PRIMARY KEY (`col_18`,`col_19`,`col_17`) /*T![clustered_index] CLUSTERED */,
  UNIQUE KEY `idx_10` (`col_15`,`col_18`,`col_16`,`col_17`),
  KEY `idx_11` (`col_16`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `tbl_3` VALUES ('Alice','1971-03-07',29994,'Alice',x'6745710000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000');

select /*+ nth_plan(1) */ tbl_3.col_19 from tbl_3 where col_19 in ( select col_27 from tbl_5 where col_15 = 'Alice'  );

And it seems related to prefix index + index lookup join and also related to several recent issues #23722 #23653.

[zyguan]

Maybe introduced by #23755 , it seems we index inner colLens by outerOffset here? @tangenta

[wshwsh12]

/assign @lzmhhh123

[lzmhhh123]

Confirm it has been fixed.

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions

[zyguan]

[lysu]

@lzmhhh123 PTAL

[lzmhhh123]

@zyguan The screenshot is not complete. Could you provide the reproduce SQL by text?

[zyguan]

@zyguan The screenshot is not complete. Could you provide the reproduce SQL by text?

#23899 (comment) this one.

[lzmhhh123]

It has been fixed by #24568

[ti-srebot]

( FixedVersions AffectedVersions ) fields are empty.