7.1.1 Cluster execution SQL ERROR 1105 (HY000): runtime error: index out of range [0] with length 0

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 7.1.1 集群执行sql ERROR 1105 (HY000): runtime error: index out of range [0] with length 0

| username: TiDBer_yyy

[TiDB Usage Environment] Test/PoC
[TiDB Version] 7.1.1

[Encountered Issue: Problem Phenomenon and Impact] The query executes without issues in version 5.0.4, but it reports an error in version 7.1.1.

  • SQL
SELECT `estimate_year_month`,
       `a_manager_yid`                                               AS `manager_yid`,
       `real_t1_volume`,
       `real_t0_volume`,
       `a_estimate_volume` + IFNULL(`b_estimate_volume`, 0)          AS `estimate_volume`,
       IFNULL(`a`.`increase_sum`, 0) + IFNULL(`b`.`increase_sum`, 0) AS `increase_sum`,
       IFNULL(`a`.`decrease_sum`, 0) + IFNULL(`b`.`decrease_sum`, 0) AS `decrease_sum`
FROM (SELECT `estimate_year_month`,
             `manager_yid`          AS `a_manager_yid`,
             SUM(`real_t1_volume`)  AS `real_t1_volume`,
             SUM(`real_t0_volume`)  AS `real_t0_volume`,
             SUM(`estimate_volume`) AS `a_estimate_volume`,
             SUM(IF(`estimate_volume` > `real_t1_volume`,
                    `estimate_volume` - `real_t1_volume`,
                    0))             AS `increase_sum`,
             SUM(IF(`estimate_volume` < `real_t1_volume`,
                    `estimate_volume` - `real_t1_volume`,
                    0))             AS `decrease_sum`
      FROM `rd_crm`.`signed_estimate_volume`
      WHERE `estimate_year_month` = DATE_FORMAT(NOW(), _UTF8MB4'%Y%m')
        AND `is_dropped` = 0
        AND `data_type` = 1
      GROUP BY `manager_yid`) AS `a`
         LEFT JOIN (SELECT `manager_yid`          AS `b_manager_yid`,
                           SUM(`estimate_volume`) AS `b_estimate_volume`,
                           SUM(`estimate_volume`) AS `increase_sum`,
                           0                      AS `decrease_sum`
                    FROM `rd_crm`.`unsigned_estimate_volume`
                    WHERE `estimate_year_month` = DATE_FORMAT(NOW(), _UTF8MB4'%Y%m')
                      AND `is_dropped` = 0
                      AND `data_type` = 1
                    GROUP BY `manager_yid`) AS `b`
                   ON a.`a_manager_yid` = b.`b_manager_yid`

[Attachment: Screenshot/Log/Monitoring]
Error Log:

[2023/08/02 16:46:46.037 +08:00] [ERROR] [adapter.go:148] ["execute sql panic"] [conn=844026906930753967] [sql="SELECT `estimate_year_month`,\n       `a_manager_yid`
                                       AS `manager_yid`,\n       `real_t1_volume`,\n       `real_t0_volume`,\n       `a_estimate_volume` + IFNULL(`b_estimate_volume`, 0)
      AS `estimate_volume`,\n       IFNULL(`a`.`increase_sum`, 0) + IFNULL(`b`.`increase_sum`, 0) AS `increase_sum`,\n       IFNULL(`a`.`decrease_sum`, 0) + IFNULL(`b`.`decr
ease_sum`, 0) AS `decrease_sum`\nFROM (SELECT `estimate_year_month`,\n             `manager_yid`          AS `a_manager_yid`,\n             SUM(`real_t1_volume`)  AS `real_t
1_volume`,\n             SUM(`real_t0_volume`)  AS `real_t0_volume`,\n             SUM(`estimate_volume`) AS `a_estimate_volume`,\n             SUM(IF(`estimate_volume` > `r
eal_t1_volume`,\n                    `estimate_volume` - `real_t1_volume`,\n                    0))             AS `increase_sum`,\n             SUM(IF(`estimate_volume` <`
real_t1_volume`,\n                    `estimate_volume` - `real_t1_volume`,\n                    0))             AS `decrease_sum`\n      FROM `rd_crm`.`signed_estimate_volu
me`\n      WHERE `estimate_year_month` = DATE_FORMAT(NOW(), _UTF8MB4'%Y%m')\n        AND `is_dropped` = 0\n        AND `data_type` = 1\n      GROUP BY `manager_yid`) AS `a`\
n         LEFT JOIN (SELECT `manager_yid`          AS `b_manager_yid`,\n                           SUM(`estimate_volume`) AS `b_estimate_volume`,\n
 SUM(`estimate_volume`) AS `increase_sum`,\n                           0                      AS `decrease_sum`\n                    FROM `rd_crm`.`unsigned_estimate_volume`
\n                    WHERE `estimate_year_month` = DATE_FORMAT(NOW(), _UTF8MB4'%Y%m')\n                      AND `is_dropped` = 0\n                      AND `data_type` = 1
\n                    GROUP BY `manager_yid`) AS `b`\n                   ON a.`a_manager_yid` = b.`b_manager_yid`"] [stack="github.com/pingcap/tidb/executor.(*recordSet).Nex
t.func1\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:148\nruntime.gopanic\n\t/usr/local/go/src/runtime/panic.go:884\nrunt
ime.goPanicIndex\n\t/usr/local/go/src/runtime/panic.go:113\ngithub.com/pingcap/tidb/util/chunk.(*Column).IsNull\n\t/home/jenkins/agent/workspace/build-common/go/src/github.c
om/pingcap/tidb/util/chunk/column.go:169\ngithub.com/pingcap/tidb/expression.(*builtinIfNullIntSig).vecEvalInt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.co
m/pingcap/tidb/expression/builtin_control_vec_generated.go:846\ngithub.com/pingcap/tidb/expression.(*ScalarFunction).VecEvalInt\n\t/home/jenkins/agent/workspace/build-common
/go/src/github.com/pingcap/tidb/expression/scalar_function.go:48\ngithub.com/pingcap/tidb/expression.(*builtinCastIntAsDecimalSig).vecEvalDecimal\n\t/home/jenkins/agent/work
space/build-common/go/src/github.com/pingcap/tidb/expression/builtin_cast_vec.go:1081\ngithub.com/pingcap/tidb/expression.(*ScalarFunction).VecEvalDecimal\n\t/home/jenkins/a
gent/workspace/build-common/go/src/github.com/pingcap/tidb/expression/scalar_function.go:63\ngithub.com/pingcap/tidb/expression.(*builtinArithmeticPlusDecimalSig).vecEvalDec
imal\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/expression/builtin_arithmetic_vec.go:994\ngithub.com/pingcap/tidb/expression.(*ScalarFuncti
on).VecEvalDecimal\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/expression/scalar_function.go:63\ngithub.com/pingcap/tidb/expression.evalOneV
ec\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/expression/chunk_executor.go:163\ngithub.com/pingcap/tidb/expression.(*defaultEvaluator).run\
n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/expression/evaluator.go:53\ngithub.com/pingcap/tidb/expression.(*EvaluatorSuite).Run\n\t/home/je
nkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/expression/evaluator.go:125\ngithub.com/pingcap/tidb/executor.(*ProjectionExec).unParallelExecute\n\t/home/
jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/projection.go:205\ngithub.com/pingcap/tidb/executor.(*ProjectionExec).Next\n\t/home/jenkins/agen
t/workspace/build-common/go/src/github.com/pingcap/tidb/executor/projection.go:183\ngithub.com/pingcap/tidb/executor.Next\n\t/home/jenkins/agent/workspace/build-common/go/sr
c/github.com/pingcap/tidb/executor/executor.go:326\ngithub.com/pingcap/tidb/executor.(*ExecStmt).next\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap
/tidb/executor/adapter.go:1202\ngithub.com/pingcap/tidb/executor.(*recordSet).Next\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adap
ter.go:151\ngithub.com/pingcap/tidb/server.(*tidbResultSet).Next\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/driver_tidb.go:469\ngith
ub.com/pingcap/tidb/server.(*clientConn).writeChunks\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:2313\ngithub.com/pingcap/tid
b/server.(*clientConn).writeResultSet\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:2256\ngithub.com/pingcap/tidb/server.(*clie
ntConn).handleStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:2124\ngithub.com/pingcap/tidb/server.(*clientConn).handleQuery
\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1885\ngithub.com/pingcap/tidb/server.(*clientConn).dispatch\n\t/home/jenkins/age
nt/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1372\ngithub.com/pingcap/tidb/server.(*clientConn).Run\n\t/home/jenkins/agent/workspace/build-common/
go/src/github.com/pingcap/tidb/server/conn.go:1153\ngithub.com/pingcap/tidb/server.(*Server).onConn\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/t
idb/server/server.go:677"]
2
| username: Billmay表妹 | Original post link

Based on the error information you provided, this error is usually caused by a runtime error when TiDB executes an SQL statement, leading to an index out of bounds. This error can be caused by various reasons, such as SQL syntax errors, table not existing, column not existing, etc.

To better pinpoint the issue, it is recommended that you first check whether the SQL statement is correct, including syntax, table names, column names, etc. You can use the explain command to view the execution plan of the SQL statement to better understand the execution process of the SQL statement. For example:

explain select * from my_table where id = 1;

If the SQL statement is correct, then you can try to check TiDB’s logs to better understand the cause of the error. You can set the log level in TiDB’s configuration file and then check TiDB’s log files. For example, you can add the following configuration in the configuration file:

[log]
level = "debug"

Then restart TiDB and check TiDB’s log files to better understand the cause of the error.

3
| username: xfworld | Original post link

The core bug has appeared again, but we need to rule out environmental and data issues… :rofl:

4
| username: TiDBer_yyy | Original post link

There are no obvious additional errors.

5
| username: TiDBer_yyy | Original post link

Removing part of the SQL will execute successfully:

SUM(IF(`estimate_volume` < `real_t1_volume`, `estimate_volume` - `real_t1_volume`, 0)) AS `decrease_sum`
6
| username: 大飞飞呀 | Original post link

Is there a bug address?

7
| username: 大飞飞呀 | Original post link

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

8
| username: TiDBer_yyy | Original post link

Additional note: 7.1.0 can execute successfully.

9
| username: 大飞飞呀 | Original post link

The SQL itself does have some redundancy. Version 7.1.1 might have introduced some kind of optimization, which brought in this bug.

10
| username: TiDBer_yyy | Original post link

Removing the outermost query’s IFNULL(b.increase_sum, 0) can also execute successfully.

11
| username: redgame | Original post link

Well, this prompt indicates that the data in the database is inconsistent.

12
| username: ShawnYan | Original post link

Could you please provide the definitions of these two tables?
FROM rd_crm.signed_estimate_volume
FROM rd_crm.unsigned_estimate_volume

13
| username: TiDBer_yyy | Original post link

An empty table can execute SQL successfully.

CREATE TABLE `signed_estimate_volume` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '',
  `estimate_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `broker_id` varchar(40) NOT NULL DEFAULT '' COMMENT '',
  `broker_name` varchar(128) NOT NULL DEFAULT '' COMMENT '',
  `dealer_id` varchar(40) NOT NULL DEFAULT '' COMMENT '',
  `dealer_name` varchar(128) NOT NULL DEFAULT '' COMMENT '',
  `signed_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
  `is_t0_signed` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
  `service_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
  `virtual_tag_id` varchar(128) NOT NULL DEFAULT '' COMMENT '',
  `virtual_tag_name` varchar(256) NOT NULL DEFAULT '' COMMENT '',
  `virtual_tag_label` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
  `origin_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '',
  `origin_level2` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `origin_level3` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `broker_enterprise_id` varchar(40) NOT NULL DEFAULT '' COMMENT '',
  `business_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `customer_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `real_t0_volume` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `real_t1_volume` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `real_t2_volume` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `real_t3_volume` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `estimate_volume` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `estimate_real_t1_percent_rate` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `estimate_diff` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `estimate_real_t1_diff` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `estimate_real_t1_diff_reason` varchar(256) NOT NULL DEFAULT '' COMMENT '',
  `leader_estimate_volume` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `leader_manager_diff_reason` varchar(256) NOT NULL DEFAULT '' COMMENT '',
  `real_t0_estimate_percent_rate` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `estimate_cnt` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `estimate_updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
  `manager_yid` varchar(32) NOT NULL DEFAULT '' COMMENT '',
  `manager_percent` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `estimate_year_month` varchar(8) NOT NULL DEFAULT '' COMMENT '',
  `is_dropped` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
  `data_type` tinyint(4) NOT NULL DEFAULT '1' COMMENT '',
  `ycrm_estimate_id` varchar(64) NOT NULL DEFAULT '' COMMENT '',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
  `created_user` varchar(32) NOT NULL DEFAULT '' COMMENT '',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
  `updated_user` varchar(32) NOT NULL DEFAULT '' COMMENT '',
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  UNIQUE KEY `UNIQ_ESTIMATE_ID` (`estimate_id`),
  UNIQUE KEY `UNIQ_MONTH_YID_BROKER_DEALER_DROPPED` (`estimate_year_month`,`manager_yid`,`broker_id`,`dealer_id`,`is_dropped`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=630002 COMMENT='';

CREATE TABLE `unsigned_estimate_volume` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '',
  `estimate_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `estimate_year_month` varchar(8) NOT NULL DEFAULT '' COMMENT '',
  `business_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `customer_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `virtual_tag_id` varchar(128) NOT NULL DEFAULT '' COMMENT '',
  `virtual_tag_name` varchar(256) NOT NULL DEFAULT '' COMMENT '',
  `broker_enterprise_id` varchar(40) NOT NULL DEFAULT '' COMMENT '',
  `broker_id` varchar(40) NOT NULL DEFAULT '' COMMENT '',
  `broker_name` varchar(128) NOT NULL DEFAULT '' COMMENT '',
  `estimate_volume` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `estimate_diff` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `leader_estimate_volume` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `leader_manager_diff_reason` varchar(256) NOT NULL DEFAULT '' COMMENT '',
  `estimate_cnt` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `manager_yid` varchar(32) NOT NULL DEFAULT '' COMMENT '',
  `manager_percent` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `remark` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  `is_dropped` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
  `data_type` tinyint(4) NOT NULL DEFAULT '1' COMMENT '',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
  `created_user` varchar(32) NOT NULL DEFAULT '' COMMENT '',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '',
  `updated_user` varchar(32) NOT NULL DEFAULT '' COMMENT '',
  `estimate_updated_at` datetime DEFAULT NULL COMMENT '',
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  UNIQUE KEY `UNIQ_SIGNED_ESTIMATE_ID` (`estimate_id`),
  UNIQUE KEY `UNIQ_BUSINESS_DEALER_MONTH_YID` (`estimate_year_month`,`manager_yid`,`broker_id`,`business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=390525 COMMENT='Pending signed relationship estimated volume table';
14
| username: gcworkerishungry | Original post link

I also encountered this error. In my case, it was caused by the repeated occurrence of fields (and aliases). Currently, the production and research team is investigating. You can temporarily resolve it by modifying the SQL.

15
| username: TiDBer_yyy | Original post link

Boss, the same table structure and the same data have no issues in version 7.1.0. Can you confirm if this situation exists?

16
| username: Billmay表妹 | Original post link

I will provide feedback on this issue~

17
| username: aytrack | Original post link

It’s a bug, this issue is being tracked here: merge join report runtime error index out of range [0] with length · Issue #45805 · pingcap/tidb · GitHub
Related post: select 单表报错 SQL 错误 [1105] [HY000]: runtime error: index out of range [0] with length 0 - TiDB 的问答社区

18
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.