panic in buildTableReaderForIndexJoin

[wjhuang2016]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `ba2bc2d8-3932-44fa-8bd9-f7c6822020dd` (
  `791be5bd-3a41-434f-b194-0a8f7f5fa27b` double DEFAULT '1.335088259490289',
  `cb64194a-f141-4bfb-a2dd-dd78b6cf9f91` set('mj','4s7ht','z','3i','b26','9','cg11','uvzcp','c','ns','fl9') NOT NULL DEFAULT 'mj,z,3i,9,cg11,c',
  PRIMARY KEY (`cb64194a-f141-4bfb-a2dd-dd78b6cf9f91`) /*T![clustered_index] CLUSTERED */,
  KEY `013a92c6-db30-4963-b902-d4943d4023ed` (`791be5bd-3a41-434f-b194-0a8f7f5fa27b`),
  KEY `7f8209dc-6f2e-4646-9ddf-51c62e48b6dc` (`791be5bd-3a41-434f-b194-0a8f7f5fa27b`),
  KEY `2347bbe5-fe5a-4984-b2c5-79f451095482` (`791be5bd-3a41-434f-b194-0a8f7f5fa27b`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_chinese_ci;

INSERT INTO `ba2bc2d8-3932-44fa-8bd9-f7c6822020dd` VALUES (634.2783557491367,''),(2000.5041449792013,'4s7ht'),(634.2783557491367,'3i'),(634.2783557491367,'9'),(7803.173688589342,'uvzcp'),(634.2783557491367,'ns'),(634.2783557491367,'fl9');

CREATE TABLE `9aed23b9-9772-40dc-951c-7263c605c776` (
  `a480d6d7-02aa-48d8-b8ad-fffb94689039` decimal(56,16) DEFAULT '931359772706767457132645278260455518957.9866038319986886',
  `f8c37c8b-9167-4f46-a378-c1863f858433` set('3bqx','g','6op3','2g','jf','arkd3','y0b','jdy','1g','ff5z','224b') DEFAULT '3bqx,2g,ff5z,224b',
  `c0083600-1ec7-4176-bbe2-b8a4a71596d7` smallint(6) NOT NULL DEFAULT '-25973',
  `e542dbe8-59c2-4ec8-bec0-c179fea8f542` year(4) DEFAULT '2122',
  `90c28415-53f7-4443-b5e1-4177e42f0f39` text DEFAULT NULL,
  PRIMARY KEY (`c0083600-1ec7-4176-bbe2-b8a4a71596d7`) /*T![clustered_index] CLUSTERED */,
  KEY `d20dfca1-6dda-4e65-a4f2-c0e3f5cb1b2a` (`e542dbe8-59c2-4ec8-bec0-c179fea8f542`),
  KEY `482006a0-2c85-45b1-9c4a-3a94c8f12ded` (`c0083600-1ec7-4176-bbe2-b8a4a71596d7`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='bbbf3f9c-a23e-4607-979a-0d4ce886f2c0'
PARTITION BY HASH (`c0083600-1ec7-4176-bbe2-b8a4a71596d7`) PARTITIONS 4;

INSERT INTO `9aed23b9-9772-40dc-951c-7263c605c776` VALUES (465.0000000000000000,'jdy',-8542,2008,'FgZXe');

select    /*+ inl_join( `ba2bc2d8-3932-44fa-8bd9-f7c6822020dd` , `9aed23b9-9772-40dc-951c-7263c605c776` ) */ avg(   `9aed23b9-9772-40dc-951c-7263c605c776`.`c0083600-1ec7-4176-bbe2-b8a4a71596d7` ) as r0 , repeat( `9aed23b9-9772-40dc-951c-7263c605c776`.`90c28415-53f7-4443-b5e1-4177e42f0f39` , `9aed23b9-9772-40dc-951c-7263c605c776`.`c0083600-1ec7-4176-bbe2-b8a4a71596d7` ) as r1 , locate( `9aed23b9-9772-40dc-951c-7263c605c776`.`90c28415-53f7-4443-b5e1-4177e42f0f39` , `9aed23b9-9772-40dc-951c-7263c605c776`.`90c28415-53f7-4443-b5e1-4177e42f0f39` ) as r2 , unhex( `ba2bc2d8-3932-44fa-8bd9-f7c6822020dd`.`791be5bd-3a41-434f-b194-0a8f7f5fa27b` ) as r3 from `ba2bc2d8-3932-44fa-8bd9-f7c6822020dd` right join `9aed23b9-9772-40dc-951c-7263c605c776` on `ba2bc2d8-3932-44fa-8bd9-f7c6822020dd`.`cb64194a-f141-4bfb-a2dd-dd78b6cf9f91` = `9aed23b9-9772-40dc-951c-7263c605c776`.`c0083600-1ec7-4176-bbe2-b8a4a71596d7` where not( `9aed23b9-9772-40dc-951c-7263c605c776`.`c0083600-1ec7-4176-bbe2-b8a4a71596d7` in ( -7860 ,-13384 ,-12940 ) ) and not( `ba2bc2d8-3932-44fa-8bd9-f7c6822020dd`.`cb64194a-f141-4bfb-a2dd-dd78b6cf9f91` between '4s7ht' and 'mj' );

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

No error

3. What did you see instead (Required)

[index_lookup_join.go:484] ["innerWorker panicked"] [conn=3533669842974415417] [recover="runtime error: index out of range [2] with length 2"] [stack="github.com/pingcap/tidb/executor.(*innerWorker).run.func1\n\t/Users/pingcap/workspace/build-common@4/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:484\nruntime.gopanic\n\t/usr/local/go1.19.3/src/runtime/panic.go:884\nruntime.goPanicIndex\n\t/usr/local/go1.19.3/src/runtime/panic.go:113\ngithub.com/pingcap/tidb/table/tables.(*partitionedTable).locateHashPartition\n\t/Users/pingcap/workspace/build-common@4/go/src/github.com/pingcap/tidb/table/tables/partition.go:1112\ngithub.com/pingcap/tidb/table/tables.(*partitionedTable).locatePartition\n\t/Users/pingcap/workspace/build-common@4/go/src/github.com/pingcap/tidb/table/tables/partition.go:994\ngithub.com/pingcap/tidb/table/tables.(*partitionedTable).GetPartitionByRow\n\t/Users/pingcap/workspace/build-common@4/go/src/github.com/pingcap/tidb/table/tables/partition.go:1159\ngithub.com/pingcap/tidb/executor.(*dataReaderBuilder).buildTableReaderForIndexJoin\n\t/Users/pingcap/workspace/build-common@4/go/src/github.com/pingcap/tidb/executor/builder.go:4211\ngithub.com/pingcap/tidb/executor.(*dataReaderBuilder).buildExecutorForIndexJoinInternal\n\t/Users/pingcap/workspace/build-common@4/go/src/github.com/pingcap/tidb/executor/builder.go:4062\ngithub.com/pingcap/tidb/executor.(*dataReaderBuilder).buildExecutorForIndexJoin\n\t/Users/pingcap/workspace/build-common@4/go/src/github.com/pingcap/tidb/executor/builder.go:4055\ngithub.com/pingcap/tidb/executor.(*innerWorker).fetchInnerResults\n\t/Users/pingcap/workspace/build-common@4/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:690\ngithub.com/pingcap/tidb/executor.(*innerWorker).handleTask\n\t/Users/pingcap/workspace/build-common@4/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:528\ngithub.com/pingcap/tidb/executor.(*innerWorker).run\n\t/Users/pingcap/workspace/build-common@4/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:502

4. What is your TiDB version? (Required)

master

[wjhuang2016]

The hint may not work, and got

 desc select    /*+ inl_join( `ba2bc2d8-3932-44fa-8bd9-f7c6822020dd` , `9aed23b9-9772-40dc-951c-7263c605c776` ) */ avg(   `9aed23b9-9772-40dc-951c-7263c605c776`.`c0083600-1ec7-4176-bbe2-b8a4a71596d7` ) as r0 , repeat( `9aed23b9-9772-40dc-951c-7263c605c776`.`90c28415-53f7-4443-b5e1-4177e42f0f39` , `9aed23b9-9772-40dc-951c-7263c605c776`.`c0083600-1ec7-4176-bbe2-b8a4a71596d7` ) as r1 , locate( `9aed23b9-9772-40dc-951c-7263c605c776`.`90c28415-53f7-4443-b5e1-4177e42f0f39` ,
`9aed23b9-9772-40dc-951c-7263c605c776`.`90c28415-53f7-4443-b5e1-4177e42f0f39` ) as r2 , unhex( `ba2bc2d8-3932-44fa-8bd9-f7c6822020dd`.`791be5bd-3a41-434f-b194-0a8f7f5fa27b` ) as r3 from `ba2bc2d8-3932-44fa-8bd9-f7c6822020dd` right join `9aed23b9-9772-40dc-951c-7263c605c776` on `ba2bc2d8-3932-44fa-8bd9-f7c6822020dd`.`cb64194a-f141-4bfb-a2dd-dd78b6cf9f91` = `9aed23b9-9772-40dc-951c-7263c605c776`.`c0083600-1ec7-4176-bbe2-b8a4a71596d7` where not( `9aed23b9-9772-40dc-951c-7263c605c776`.`c0083600-1ec7-4176-bbe2-b8a4a71596d7` in ( -7860 ,-13384 ,-12940 ) ) and not( `ba2bc2d8-3932-44fa-8bd9-f7c6822020dd`.`cb64194a-f141-4bfb-a2dd-dd78b6cf9f91` between '4s7ht' and 'mj' );
+--------------------------------------+----------+-----------+----------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                   | estRows  | task      | access object                                            | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+--------------------------------------+----------+-----------+----------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_15                        | 1.00     | root      |                                                          | Column#8, repeat(test1.9aed23b9-9772-40dc-951c-7263c605c776.90c28415-53f7-4443-b5e1-4177e42f0f39, test1.9aed23b9-9772-40dc-951c-7263c605c776.c0083600-1ec7-4176-bbe2-b8a4a71596d7)->Column#9, locate(test1.9aed23b9-9772-40dc-951c-7263c605c776.90c28415-53f7-4443-b5e1-4177e42f0f39, test1.9aed23b9-9772-40dc-951c-7263c605c776.90c28415-53f7-4443-b5e1-4177e42f0f39)->Column#10, unhex(cast(test1.ba2bc2d8-3932-44fa-8bd9-f7c6822020dd.791be5bd-3a41-434f-b194-0a8f7f5fa27b, var_string(5)))->Column#11 |
| └─StreamAgg_17                       | 1.00     | root      |                                                          | funcs:avg(Column#17)->Column#8, funcs:firstrow(Column#18)->test1.ba2bc2d8-3932-44fa-8bd9-f7c6822020dd.791be5bd-3a41-434f-b194-0a8f7f5fa27b, funcs:firstrow(Column#19)->test1.9aed23b9-9772-40dc-951c-7263c605c776.c0083600-1ec7-4176-bbe2-b8a4a71596d7, funcs:firstrow(Column#20)->test1.9aed23b9-9772-40dc-951c-7263c605c776.90c28415-53f7-4443-b5e1-4177e42f0f39                                                                                                                                        |
|   └─Projection_41                    | 7.00     | root      |                                                          | cast(test1.9aed23b9-9772-40dc-951c-7263c605c776.c0083600-1ec7-4176-bbe2-b8a4a71596d7, decimal(5,0) BINARY)->Column#17, test1.ba2bc2d8-3932-44fa-8bd9-f7c6822020dd.791be5bd-3a41-434f-b194-0a8f7f5fa27b, test1.9aed23b9-9772-40dc-951c-7263c605c776.c0083600-1ec7-4176-bbe2-b8a4a71596d7, test1.9aed23b9-9772-40dc-951c-7263c605c776.90c28415-53f7-4443-b5e1-4177e42f0f39                                                                                                                                  |
|     └─HashJoin_40                    | 7.00     | root      |                                                          | inner join, equal:[eq(Column#15, test1.9aed23b9-9772-40dc-951c-7263c605c776.c0083600-1ec7-4176-bbe2-b8a4a71596d7)]                                                                                                                                                                                                                                                                                                                                                                                        |
|       ├─Projection_20(Build)         | 5.60     | root      |                                                          | test1.ba2bc2d8-3932-44fa-8bd9-f7c6822020dd.791be5bd-3a41-434f-b194-0a8f7f5fa27b, cast(test1.ba2bc2d8-3932-44fa-8bd9-f7c6822020dd.cb64194a-f141-4bfb-a2dd-dd78b6cf9f91, bigint(39) BINARY)->Column#15                                                                                                                                                                                                                                                                                                      |
|       │ └─Selection_21               | 5.60     | root      |                                                          | not(and(ge(test1.ba2bc2d8-3932-44fa-8bd9-f7c6822020dd.cb64194a-f141-4bfb-a2dd-dd78b6cf9f91, "4s7ht"), le(test1.ba2bc2d8-3932-44fa-8bd9-f7c6822020dd.cb64194a-f141-4bfb-a2dd-dd78b6cf9f91, "mj")))                                                                                                                                                                                                                                                                                                         |
|       │   └─TableReader_23           | 7.00     | root      |                                                          | data:TableFullScan_22                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|       │     └─TableFullScan_22       | 7.00     | cop[tikv] | table:ba2bc2d8-3932-44fa-8bd9-f7c6822020dd               | keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|       └─PartitionUnion_30(Probe)     | 12250.41 | root      |                                                          |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|         ├─TableReader_32             | 7166.67  | root      |                                                          | data:TableRangeScan_31                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|         │ └─TableRangeScan_31        | 7166.67  | cop[tikv] | table:9aed23b9-9772-40dc-951c-7263c605c776, partition:p0 | range:[-inf,-13384), (-13384,-12940), (-12940,-7860), (-7860,+inf], keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                        |
|         ├─TableReader_34             | 7166.67  | root      |                                                          | data:TableRangeScan_33                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|         │ └─TableRangeScan_33        | 7166.67  | cop[tikv] | table:9aed23b9-9772-40dc-951c-7263c605c776, partition:p1 | range:[-inf,-13384), (-13384,-12940), (-12940,-7860), (-7860,+inf], keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                        |
|         ├─TableReader_36             | 1.00     | root      |                                                          | data:TableRangeScan_35                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|         │ └─TableRangeScan_35        | 1.00     | cop[tikv] | table:9aed23b9-9772-40dc-951c-7263c605c776, partition:p2 | range:[-inf,-13384), (-13384,-12940), (-12940,-7860), (-7860,+inf], keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                        |
|         └─TableReader_38             | 7166.67  | root      |                                                          | data:TableRangeScan_37                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|           └─TableRangeScan_37        | 7166.67  | cop[tikv] | table:9aed23b9-9772-40dc-951c-7263c605c776, partition:p3 | range:[-inf,-13384), (-13384,-12940), (-12940,-7860), (-7860,+inf], keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                        |
+--------------------------------------+----------+-----------+----------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

If so, please analyze these two tables.