从6.1.2升级到7.1.1后，执行SQL报index out of range [0] with length 0

🪐 TiDB 技术问题
1

【 TiDB 使用环境】生产环境
【 TiDB 版本】7.1.1

【遇到的问题：问题现象及影响】
SQL:

select d.*,group_concat(wi.wc_person_name) 人才选择
from (
         select suser.realName                                         所属人,
                base.id                                                baseid,
                luser.realName                                         录入人,
                resuser.realName                                       申请人,
                sdept.d4 大区,
                sdept.d5 分公司,
                concat_ws('/', sdept.d4, sdept.d5, sdept.d6, sdept.d7) 部门,
                contract_no,
                price                                                  合同金额,
                kickback                                               咨询费,
                FROM_UNIXTIME(contract_signing_time)                   签约时间,
                case
                    when base.state = 2
                        then '已签约'
                    when base.state = 3
                        then '合同执行中'
                    end as                                             合同状态,
                ent_name                                               企业名称,
                ent_linkman                                            联系人,
                config.name                                            操作状态,
                config2.name                                           核款状态,
                FROM_UNIXTIME(xxf.create_time)                         申请时间,
                #        xxf.state                                                                                       操作状态,
                 xxf.transfer_price                                     转业绩金额,

                (select sum(transfer_price)
                 from kl_conm_account
                 where qconmid = base.id
                   and state in (12, 14)
                   and conmid = 0)                                     入账总金额,

                base.id
         from ods_oa.kl_conm_base base
                  left join ods_oa.kl_users suser on base.ouid = suser.ID
                  left join ods_oa.kl_users luser on base.uid = luser.ID
                  left join ods_oa.dept_ext sdept on sdept.id = suser.DEPARTMENTID
                  left join kl_conm_ycenterprise qy on qy.conmid = base.id
                  left join kl_conm_account_xxf xxf on xxf.conmid = base.id
                  left join kl_conm_account_result res on res.cid = base.id and is_zx = 2
                  left join kl_status_config config on config.id = xxf.state
                  left join kl_status_config config2 on config2.id = entry_status
                  left join ods_oa.kl_users resuser on resuser.id = xxf.uid
         where suser.realName in (
             select 所属人
             from (
                      select suser.realName 所属人
                      from ods_oa.kl_conm_base base
                               left join ods_oa.kl_users suser on base.ouid = suser.ID
                               left join kl_conm_account_xxf xxf on xxf.conmid = base.id
                      where contract_no like '%Q%'
                        and xxf.transfer_price > 0

                      group by contract_no
                  ) t
             group by t.所属人
             having count(t.所属人) > 1
         )
           and contract_no like '%Q%'
           and xxf.transfer_price > 0

         group by contract_no, suser.realName
         order by suser.realName) d
         left join kl_conm_ycenterprise_withcert wi on wi.conmid = d.baseid
group by d.contract_no, 所属人
order by 所属人

**如果我减少d.的字段数量，则不会报该错误，但d.的字段数量达到一定数量后，就又要报index out of range

2

麻烦提供下table define, 我们复现一下。

3

用mysql 客户端执行也报这个错吗，看看tidb日志里面有写吗

4

上个版本都是好的，这个是日志

[2023/08/14 08:54:28.753 +08:00] [ERROR] [aggregate.go:458] [“parallel hash aggregation panicked”] [error=“runtime error: index out of range [0] with length 0”] [stack=“github.com/pingcap/tidb/executor.recoveryHashAgg\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/aggregate.go:458\ngithub.com/pingcap/tidb/executor.(*HashAggPartialWorker).run.func1\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/aggregate.go:466\nruntime.gopanic\n\t/usr/local/go/src/runtime/panic.go:884\nruntime.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.com/pingcap/tidb/util/chunk/column.go:169\ngithub.com/pingcap/tidb/util/chunk.Row.IsNull\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/util/chunk/row.go:218\ngithub.com/pingcap/tidb/expression.(*Column).EvalString\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/expression/column.go:439\ngithub.com/pingcap/tidb/executor/aggfuncs.(*groupConcat).UpdatePartialResult\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/aggfuncs/func_group_concat.go:134\ngithub.com/pingcap/tidb/executor.(*HashAggPartialWorker).updatePartialResult\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/aggregate.go:526\ngithub.com/pingcap/tidb/executor.(*HashAggPartialWorker).run\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/aggregate.go:487”]

5

这个就不太方便提供了，这个是日志：

[2023/08/14 08:54:28.753 +08:00] [ERROR] [aggregate.go:458] [“parallel hash aggregation panicked”] [error=“runtime error: index out of range [0] with length 0”] [stack=“github.com/pingcap/tidb/executor.recoveryHashAgg\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/aggregate.go:458\ngithub.com/pingcap/tidb/executor.(*HashAggPartialWorker).run.func1\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/aggregate.go:466\nruntime.gopanic\n\t/usr/local/go/src/runtime/panic.go:884\nruntime.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.com/pingcap/tidb/util/chunk/column.go:169\ngithub.com/pingcap/tidb/util/chunk.Row.IsNull\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/util/chunk/row.go:218\ngithub.com/pingcap/tidb/expression.(*Column).EvalString\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/expression/column.go:439\ngithub.com/pingcap/tidb/executor/aggfuncs.(*groupConcat).UpdatePartialResult\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/aggfuncs/func_group_concat.go:134\ngithub.com/pingcap/tidb/executor.(*HashAggPartialWorker).updatePartialResult\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/aggregate.go:526\ngithub.com/pingcap/tidb/executor.(*HashAggPartialWorker).run\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/aggregate.go:487”]

6

目前我把SQL改了，可以运行了，我把left join kl_conm_ycenterprise_withcert wi on wi.conmid = d.baseid改到了子查询里面，和kl_conm_base关联，外面用group_concat(distinct wc_person_name)就能查出来了

select d.*,group_concat(distinct wc_person_name) 人才选择
from (
         select suser.realName                                         所属人,
                base.id                                                baseid,
                luser.realName                                         录入人,
                resuser.realName                                       申请人,
                sdept.d4 大区,
                sdept.d5 分公司,
                concat_ws('/', sdept.d4, sdept.d5, sdept.d6, sdept.d7) 部门,
                contract_no,
                price                                                  合同金额,
                kickback                                               咨询费,
                FROM_UNIXTIME(contract_signing_time)                   签约时间,
                case
                    when base.state = 2
                        then '已签约'
                    when base.state = 3
                        then '合同执行中'
                    end as                                             合同状态,
                ent_name                                               企业名称,
                ent_linkman                                            联系人,
                config.name                                            操作状态,
                config2.name                                           核款状态,
                FROM_UNIXTIME(xxf.create_time)                         申请时间,
                #        xxf.state                                                                                       操作状态,
                 xxf.transfer_price                                     转业绩金额,

                (select sum(transfer_price)
                 from kl_conm_account
                 where qconmid = base.id
                   and state in (12, 14)
                   and conmid = 0)                                     入账总金额,

                base.id,
								wi.wc_person_name
         from ods_oa.kl_conm_base base
                  left join ods_oa.kl_users suser on base.ouid = suser.ID
                  left join ods_oa.kl_users luser on base.uid = luser.ID
                  left join ods_oa.dept_ext sdept on sdept.id = suser.DEPARTMENTID
                  left join kl_conm_ycenterprise qy on qy.conmid = base.id
                  left join kl_conm_account_xxf xxf on xxf.conmid = base.id
                  left join kl_conm_account_result res on res.cid = base.id and is_zx = 2
                  left join kl_status_config config on config.id = xxf.state
                  left join kl_status_config config2 on config2.id = entry_status
                  left join ods_oa.kl_users resuser on resuser.id = xxf.uid
									left join kl_conm_ycenterprise_withcert wi on wi.conmid = base.id
         where suser.realName in (
             select 所属人
             from (
                      select suser.realName 所属人
                      from ods_oa.kl_conm_base base
                               left join ods_oa.kl_users suser on base.ouid = suser.ID
                               left join kl_conm_account_xxf xxf on xxf.conmid = base.id
                      where contract_no like '%Q%'
                        and xxf.transfer_price > 0

                      group by contract_no
                  ) t
             group by t.所属人
             having count(t.所属人) > 1
         )
           and contract_no like '%Q%'
           and xxf.transfer_price > 0

         group by contract_no, suser.realName
         order by suser.realName) d
         
group by d.contract_no, 所属人
order by 所属人
7

能不用中文作别名不

8

感觉非预期 反馈区反馈下。

9

不是真的需要你原版的ddl和query，而是建议提供一个最小测试用例，这样便于大家进一步帮你发掘、定位问题。

10

改了SQL能跑出来的话，子查询的结果集小了呗，是不性能有瓶颈呢

11

https://github.com/pingcap/tidb/issues/45805

感觉像是这个bug。

12 
SELECT d.*, GROUP_CONCAT(DISTINCT wc_person_name) AS 人才选择
FROM (
    SELECT
        suser.realName AS 所属人,
        base.id AS baseid,
        luser.realName AS 录入人,
        resuser.realName AS 申请人,
        sdept.d4 AS 大区,
        sdept.d5 AS 分公司,
        CONCAT_WS('/', sdept.d4, sdept.d5, sdept.d6, sdept.d7) AS 部门,
        contract_no,
        price AS 合同金额,
        kickback AS 咨询费,
        FROM_UNIXTIME(contract_signing_time) AS 签约时间,
        CASE
            WHEN base.state = 2 THEN '已签约'
            WHEN base.state = 3 THEN '合同执行中'
        END AS 合同状态,
        ent_name AS 企业名称,
        ent_linkman AS 联系人,
        config.name AS 操作状态,
        config2.name AS 核款状态,
        FROM_UNIXTIME(xxf.create_time) AS 申请时间,
        xxf.transfer_price AS 转业绩金额,
        (
            SELECT SUM(transfer_price)
            FROM kl_conm_account
            WHERE qconmid = base.id
                AND state IN (12, 14)
                AND conmid = 0
        ) AS 入账总金额,
        base.id,
        wi.wc_person_name
    FROM ods_oa.kl_conm_base base
    LEFT JOIN ods_oa.kl_users suser ON base.ouid = suser.ID
    LEFT JOIN ods_oa.kl_users luser ON base.uid = luser.ID
    LEFT JOIN ods_oa.dept_ext sdept ON sdept.id = suser.DEPARTMENTID
    LEFT JOIN kl_conm_ycenterprise qy ON qy.conmid = base.id
    LEFT JOIN kl_conm_account_xxf xxf ON xxf.conmid = base.id
    LEFT JOIN kl_conm_account_result res ON res.cid = base.id AND is_zx = 2
    LEFT JOIN kl_status_config config ON config.id = xxf.state
    LEFT JOIN kl_status_config config2 ON config2.id = entry_status
    LEFT JOIN ods_oa.kl_users resuser ON resuser.id = xxf.uid
    LEFT JOIN kl_conm_ycenterprise_withcert wi ON wi.conmid = base.id
    WHERE suser.realName IN (
        SELECT 所属人
        FROM (
            SELECT suser.realName 所属人
            FROM ods_oa.kl_conm_base base
            LEFT JOIN ods_oa.kl_users suser ON base.ouid = suser.ID
            LEFT JOIN kl_conm_account_xxf xxf ON xxf.conmid = base.id
            WHERE contract_no LIKE '%Q%'
                AND xxf.transfer_price > 0
            GROUP BY contract_no
        ) t
        GROUP BY t.所属人
        HAVING COUNT(t.所属人) > 1
    )
    AND contract_no LIKE '%Q%'
    AND xxf.transfer_price > 0
    GROUP BY contract_no, suser.realName
) d
GROUP BY d.contract_no, 所属人
ORDER BY 所属人;
13

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。