runtime error: index out of range [0] with length 0

[vivid392845427]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `tbl_19` (
  `col_0` binary(163) NOT NULL DEFAULT 'LSfT\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `col_1` varchar(446) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'GvVvyqoovQuXCwxO',
  `col_2` varchar(14) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `col_3` tinyint(1) DEFAULT NULL,
  `col_4` datetime NOT NULL DEFAULT '2028-11-20 00:00:00',
  `col_5` smallint(5) unsigned NOT NULL DEFAULT '34044',
  `col_6` time NOT NULL DEFAULT '17:29:24',
  `col_7` enum('Alice','Bob','Charlie','David') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'David',
  `col_8` time DEFAULT '14:37:55',
  `col_9` timestamp NOT NULL,
  PRIMARY KEY (`col_9`,`col_7`,`col_0`,`col_1`,`col_2`,`col_6`,`col_5`) /*T![clustered_index] NONCLUSTERED */,
  UNIQUE KEY `idx_1` (`col_0`,`col_5`),
  KEY `idx_2` (`col_9`,`col_8`,`col_4`,`col_6`,`col_3`,`col_2`,`col_5`,`col_7`,`col_0`)
);
CREATE TABLE `tbl_22` (
  `col_134` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`col_134`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_42` (`col_134`),
  UNIQUE KEY `idx_43` (`col_134`),
  UNIQUE KEY `idx_44` (`col_134`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY RANGE ( `col_134` ) (
  PARTITION `p0` VALUES LESS THAN (207),
  PARTITION `p1` VALUES LESS THAN (245),
  PARTITION `p2` VALUES LESS THAN (MAXVALUE)
);
insert into tbl_22 values(16),(18),(64),(113),(120),(145),(147),(240);
insert into tbl_19 values('mffzfkkdc','eb'              ,'jcvrals',1,'2009-08-01 00:00:00',17826,'14:22:51','David','00:48:07','1979-06-20 00:00:00'),('LSfT'     ,'GvVvyqoovQuXCwxO',''       ,0,'2028-11-20 00:00:00',34044,'17:29:24','David','14:37:55','1992-01-07 00:00:00');

select  /*+ inl_join(@sel_1 tbl_22@sel_2) */ tbl_19.* from tbl_19 where col_5 in ( select col_134 from tbl_22 where tbl_19.col_5 between 19961 and 64474 );

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

select excute success;

3. What did you see instead (Required)

mysql> select   /*+ inl_join(@sel_1 tbl_22@sel_2) */  tbl_19.* from tbl_19 where col_5 in ( select col_134 from tbl_22 where tbl_19.col_5 between 19961 and 64474 );
ERROR 1105 (HY000): runtime error: index out of range [0] with length 0

4. What is your TiDB version? (Required)

Release Version: v5.2.0-alpha-594-g542cce59b
Edition: Community
Git Commit Hash: 542cce59be58620af4ed5cd7369f7b901877a41f
Git Branch: HEAD
UTC Build Time: 2021-08-11 18:41:36
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[jingshanglu]

@vivid392845427

mysql> select /*+ hash_join( tbl_19 , tbl_22 */ tbl_19.* from tbl_19 where col_5 in ( select col_134 from tbl_22 where tbl_19.col_5 between 19961 and 64474 );

There is a syntax error in this sql, can you check it?

[vivid392845427]

select /*+ hash_join( tbl_19 , tbl_22 / tbl_19. from tbl_19 where col_5 in ( select col_134 from tbl_22 where tbl_19.col_5 between 19961 and 64474 );

i format the issue, the sql can excute

[jingshanglu]

tidb config

new_collations_enabled_on_first_bootstrap: true

error info

[2021/08/13 15:50:09.442 +08:00] [WARN] [2pc.go:1598] ["schemaLeaseChecker is not set for this transaction"] [sessionID=3] [startTS=426990897579163649] [commitTS=426990897579163650]
[2021/08/13 15:50:32.775 +08:00] [ERROR] [index_lookup_join.go:453] ["innerWorker panicked"] [conn=3] [stack="goroutine 2973 [running]:\ngithub.com/pingcap/tidb/executor.(*innerWorker).run.func1(0x104d715c8, 0x1400090a440, 0x140005aff28, 0x14012bbab80)\n\t/Users/jingshanglu/goworkspace/tidb/executor/index_lookup_join.go:451 +0xa8\npanic(0x104ad5ea0, 0x1401322ea20)\n\t/opt/homebrew/Cellar/go/1.16.3/libexec/src/runtime/panic.go:965 +0x14c\ngithub.com/pingcap/tidb/executor.(*dataReaderBuilder).buildTableReaderForIndexJoin(0x1401298b460, 0x104d715c8, 0x1400090a440, 0x1401104f8c0, 0x14012a4aa48, 0x0, 0x1, 0x106893340, 0x0, 0x0, ...)\n\t/Users/jingshanglu/goworkspace/tidb/executor/builder.go:3635 +0x1310\ngithub.com/pingcap/tidb/executor.(*dataReaderBuilder).buildExecutorForIndexJoinInternal(0x1401298b460, 0x104d715c8, 0x1400090a440, 0x104da1ad8, 0x1401104f8c0, 0x14012a4aa48, 0x0, 0x1, 0x106893340, 0x0, ...)\n\t/Users/jingshanglu/goworkspace/tidb/executor/builder.go:3504 +0x2fc\ngithub.com/pingcap/tidb/executor.(*dataReaderBuilder).buildExecutorForIndexJoin(...)\n\t/Users/jingshanglu/goworkspace/tidb/executor/builder.go:3497\ngithub.com/pingcap/tidb/executor.(*innerWorker).fetchInnerResults(0x14013c5b080, 0x104d715c8, 0x1400090a440, 0x140005c8300, 0x14012a4aa48, 0x0, 0x1, 0x0, 0x0)\n\t/Users/jingshanglu/goworkspace/tidb/executor/index_lookup_join.go:651 +0xe0\ngithub.com/pingcap/tidb/executor.(*innerWorker).handleTask(0x14013c5b080, 0x104d715c8, 0x1400090a440, 0x140005c8300, 0x0, 0x0)\n\t/Users/jingshanglu/goworkspace/tidb/executor/index_lookup_join.go:493 +0x108\ngithub.com/pingcap/tidb/executor.(*innerWorker).run(0x14013c5b080, 0x104d715c8, 0x1400090a440, 0x14012bbab80)\n\t/Users/jingshanglu/goworkspace/tidb/executor/index_lookup_join.go:470 +0xec\ncreated by github.com/pingcap/tidb/executor.(*IndexLookUpJoin).startWorkers\n\t/Users/jingshanglu/goworkspace/tidb/executor/index_lookup_join.go:183 +0x1c8\n"]
[2021/08/13 15:50:32.775 +08:00] [INFO] [conn.go:995] ["command dispatched failed"] [conn=3] [connInfo="id:3, addr:127.0.0.1:64431 status:10, collation:utf8mb4_general_ci, user:root"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="select  /*+ hash_join( tbl_19 , tbl_22 */ tbl_19.* from tbl_19 where col_5 in ( select col_134 from tbl_22 where tbl_19.col_5 between 19961 and 64474 )"] [txn_mode=OPTIMISTIC] [err="runtime error: index out of range [0] with length 0\ngithub.com/pingcap/tidb/executor.(*innerWorker).run.func1\n\t/Users/jingshanglu/goworkspace/tidb/executor/index_lookup_join.go:455\nruntime.gopanic\n\t/opt/homebrew/Cellar/go/1.16.3/libexec/src/runtime/panic.go:965\nruntime.goPanicIndex\n\t/opt/homebrew/Cellar/go/1.16.3/libexec/src/runtime/panic.go:88\ngithub.com/pingcap/tidb/executor.(*dataReaderBuilder).buildTableReaderForIndexJoin\n\t/Users/jingshanglu/goworkspace/tidb/executor/builder.go:3635\ngithub.com/pingcap/tidb/executor.(*dataReaderBuilder).buildExecutorForIndexJoinInternal\n\t/Users/jingshanglu/goworkspace/tidb/executor/builder.go:3504\ngithub.com/pingcap/tidb/executor.(*dataReaderBuilder).buildExecutorForIndexJoin\n\t/Users/jingshanglu/goworkspace/tidb/executor/builder.go:3497\ngithub.com/pingcap/tidb/executor.(*innerWorker).fetchInnerResults\n\t/Users/jingshanglu/goworkspace/tidb/executor/index_lookup_join.go:651\ngithub.com/pingcap/tidb/executor.(*innerWorker).handleTask\n\t/Users/jingshanglu/goworkspace/tidb/executor/index_lookup_join.go:493\ngithub.com/pingcap/tidb/executor.(*innerWorker).run\n\t/Users/jingshanglu/goworkspace/tidb/executor/index_lookup_join.go:470\nruntime.goexit\n\t/opt/homebrew/Cellar/go/1.16.3/libexec/src/runtime/asm_arm64.s:1130"]

[bb7133]

Hi @jingshanglu , I don't think this is critical: it doesn't lead to a wrong result. 'major' may be a better choice.

[XuHuaiyu]

Tested on 37e0dac, this problem has been fixed.

CREATE TABLE `tbl_19` (
  `col_0` binary(163) NOT NULL DEFAULT 'LSfT\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `col_1` varchar(446) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'GvVvyqoovQuXCwxO',
  `col_2` varchar(14) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `col_3` tinyint(1) DEFAULT NULL,
  `col_4` datetime NOT NULL DEFAULT '2028-11-20 00:00:00',
  `col_5` smallint(5) unsigned NOT NULL DEFAULT '34044',
  `col_6` time NOT NULL DEFAULT '17:29:24',
  `col_7` enum('Alice','Bob','Charlie','David') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'David',
  `col_8` time DEFAULT '14:37:55',
  `col_9` timestamp NOT NULL,
  PRIMARY KEY (`col_9`,`col_7`,`col_0`,`col_1`,`col_2`,`col_6`,`col_5`) /*T![clustered_index] NONCLUSTERED */,
  UNIQUE KEY `idx_1` (`col_0`,`col_5`),
  KEY `idx_2` (`col_9`,`col_8`,`col_4`,`col_6`,`col_3`,`col_2`,`col_5`,`col_7`,`col_0`)
);
CREATE TABLE `tbl_22` (
  `col_134` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`col_134`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_42` (`col_134`),
  UNIQUE KEY `idx_43` (`col_134`),
  UNIQUE KEY `idx_44` (`col_134`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY RANGE ( `col_134` ) (
  PARTITION `p0` VALUES LESS THAN (207),
  PARTITION `p1` VALUES LESS THAN (245),
  PARTITION `p2` VALUES LESS THAN (MAXVALUE)
);
insert into tbl_22 values(16),(18),(64),(113),(120),(145),(147),(240);
insert into tbl_19 values('mffzfkkdc','eb'              ,'jcvrals',1,'2009-08-01 00:00:00',17826,'14:22:51','David','00:48:07','1979-06-20 00:00:00'),('LSfT'     ,'GvVvyqoovQuXCwxO',''       ,0,'2028-11-20 00:00:00',34044,'17:29:24','David','14:37:55','1992-01-07 00:00:00');

tidb config:

new_collations_enabled_on_first_bootstrap: true

tidb> set @@tidb_partition_prune_mode = 'dynamic';
tidb> select  /*+ inl_join(@sel_1 tbl_22@sel_2) */ tbl_19.* from tbl_19 where col_5 in ( select col_134 from tbl_22 where tbl_19.col_5 between 19961 and 64474 );
Empty set (0.00 sec)
tidb> desc select  /*+ inl_join(@sel_1 tbl_22@sel_2) */ tbl_19.* from tbl_19 where col_5 in ( select col_134 from tbl_22 where tbl_19.col_5 between 19961 and 64474 );
+------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| id                           | estRows | task      | access object | operator info                                                                                                                                      |
+------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| IndexJoin_11                 | 0.04    | root      |               | semi join, inner:TableReader_10, outer key:test.tbl_19.col_5, inner key:test.tbl_22.col_134, equal cond:eq(test.tbl_19.col_5, test.tbl_22.col_134) |
| ├─TableReader_16(Build)      | 0.05    | root      |               | data:Selection_15                                                                                                                                  |
| │ └─Selection_15             | 0.05    | cop[tikv] |               | ge(test.tbl_19.col_5, 19961), le(test.tbl_19.col_5, 64474)                                                                                         |
| │   └─TableFullScan_14       | 2.00    | cop[tikv] | table:tbl_19  | keep order:false, stats:pseudo                                                                                                                     |
| └─TableReader_10(Probe)      | 1.00    | root      | partition:all | data:TableRangeScan_9                                                                                                                              |
|   └─TableRangeScan_9         | 1.00    | cop[tikv] | table:tbl_22  | range: decided by [test.tbl_19.col_5], keep order:false, stats:pseudo                                                                              |
+------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+

[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.