Internal error: UnionExec chunk column count mismatch, req: 9, result: 10, Time: 0.025000s

YangZhuo970325 · 2021 年12 月 8 日 02:21

【 TiDB 版本】
tidb 5.3.0

【概述】场景 + 问题概述
由于部分sql需要下推至tiflash，在数据库连接中新增sessionVariables=tidb_opt_agg_push_down=1配置，导致其他sql报错

sql如下：

set @@tidb_opt_agg_push_down=1;

EXPLAIN ANALYZE SELECT
count( 0 )
FROM
(
SELECT
grid_display_plan_id,
grid_code AS grid,
grid_display_type,
display_owner,
grid_display_name,
max( edit_flag ) AS edit_flag,
max( delete_flag ) AS delete_flag,
share_flag,
max( share_flag2 ) AS share_flag2,
default_flag,
creation_date,
object_version_number
FROM
(
SELECT
hgdp.grid_display_plan_id,
hgdp.grid_code,
hgdp.grid_display_type,
hgdp.display_owner,
hgdpt.grid_display_name,
‘0’ AS edit_flag,
‘0’ AS delete_flag,
hgdp.share_flag,
‘0’ AS share_flag2,
hgdp.default_flag,
hgdp.creation_date,
hgdp.object_version_number
FROM
hgl_grid_display_plan hgdp
LEFT JOIN hgl_grid_display_plan_tl hgdpt ON hgdp.grid_display_plan_id = hgdpt.grid_display_plan_id
AND hgdpt.lang = ‘zh_CN’
LEFT JOIN hgl_grid_plan_permission hgdpp ON hgdpp.grid_display_plan_id = hgdp.grid_display_plan_id
WHERE
hgdp.grid_display_type = ‘GLOBAL’ UNION ALL
SELECT
hgdp.grid_display_plan_id,
hgdp.grid_code,
hgdp.grid_display_type,
hgdp.display_owner,
hgdpt.grid_display_name,
‘1’ AS edit_flag,
‘1’ AS delete_flag,
hgdp.share_flag,
‘0’ AS share_flag2,
hgdp.default_flag,
hgdp.creation_date,
hgdp.object_version_number
FROM
hgl_grid_display_plan hgdp
LEFT JOIN hgl_grid_display_plan_tl hgdpt ON hgdp.grid_display_plan_id = hgdpt.grid_display_plan_id
AND hgdpt.lang = ‘zh_CN’
LEFT JOIN hgl_grid_plan_permission hgdpp ON hgdpp.grid_display_plan_id = hgdp.grid_display_plan_id
WHERE
hgdp.grid_display_type = ‘TENANT’
AND hgdp.tenant_id = 0 UNION ALL
SELECT
hgdp.grid_display_plan_id,
hgdp.grid_code,
hgdp.grid_display_type,
hgdp.display_owner,
hgdpt.grid_display_name,
‘1’ AS edit_flag,
‘1’ AS delete_flag,
hgdp.share_flag,
‘1’ AS share_flag2,
hgdp.default_flag,
hgdp.creation_date,
hgdp.object_version_number
FROM
hgl_grid_display_plan hgdp
LEFT JOIN hgl_grid_display_plan_tl hgdpt ON hgdp.grid_display_plan_id = hgdpt.grid_display_plan_id
AND hgdpt.lang = ‘zh_CN’
LEFT JOIN hgl_grid_plan_permission hgdpp ON hgdpp.grid_display_plan_id = hgdp.grid_display_plan_id
WHERE
hgdp.grid_display_type = ‘USER’
AND hgdp.tenant_id = 0) a
WHERE
1 = 1
GROUP BY
grid_display_plan_id,
grid_code,
grid_display_type,
display_owner,
grid_display_name,
share_flag,
default_flag,
creation_date,
object_version_number
) table_count

执行计划如下：

这道题我不会 · 2021 年12 月 8 日 10:41

我想确认下不设置 tidb_opt_agg_push_down=1 的话 SQL 查询是不是都是正常的？

YangZhuo970325 · 2021 年12 月 9 日 06:36

是的，不设置就没问题，但是我的有些函数是需要下推到tiflash的

这道题我不会 · 2021 年12 月 9 日 07:44

麻烦提供下具体需要下推的函数列表吧，我去核实下这些函数目前 TiFlash 是否都支持下推。

YangZhuo970325 · 2021 年12 月 10 日 03:48

我这边需要用到tidb_opt_agg_push_down主要是因为这条sql在设置和不设置的情况下，区别非常大

set @@tidb_opt_agg_push_down=1;
EXPLAIN ANALYZE SELECT
/*+ read_from_storage(tiflash[l]),agg_to_cop() */
count( l.je_header_id )
FROM
hgl_je_line l
JOIN hgl_je_header hjh ON l.je_header_id = hjh.je_header_id
AND hjh.reversed_flag = 0
WHERE
l.tenant_id = 0
AND l.ledger_code = ‘0L’
AND l.company_code = ‘1000’
AND l.partners_num IS NOT NULL
AND l.settle_je_line_id IS NULL
AND l.settle_flag = 1

这道题我不会 · 2021 年12 月 10 日 06:07

这里的区别非常大具体是指哪方面的区别？

YangZhuo970325 · 2021 年12 月 10 日 06:37

性能，如果不加我们这边是查不出来的，还有就是sql内存占用

这个是不加的sql分析

这个是加了之后的sql分析

spc_monkey · 2021 年12 月 13 日 02:54

set @@session.tidb_partition_prune_mode=‘dynamic’; 打开这个参数试试呢

YangZhuo970325 · 2021 年12 月 16 日 02:41

开启这个参数也是一样的报错

spc_monkey · 2021 年12 月 17 日 06:30

这个我们确认一下吧，github 有类似issue，但和咱们可能不太一样https://github.com/pingcap/tidb/issues/26554

YangZhuo970325 · 2021 年12 月 17 日 07:00

好的，麻烦了

spc_monkey · 2021 年12 月 17 日 08:01

好的，到时候我这里反馈吧

JaySon-Huang · 2021 年12 月 20 日 07:21

@YangZhuo970325 请问可以提供下这个查询涉及到表的 show create table 语句结果，供我们尝试复现定位问题吗？

YangZhuo970325 · 2021 年12 月 20 日 07:45

主要涉及到三张表：hgl_grid_display_plan，hgl_grid_display_plan_tl，hgl_grid_plan_permission

CREATE TABLE hgl_grid_display_plan (
grid_display_plan_id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘表ID，主键’,
grid_code varchar(30) NOT NULL COMMENT ‘表格代码’,
grid_display_name varchar(255) NOT NULL COMMENT ‘表格显示方案名称’,
grid_display_type varchar(30) NOT NULL COMMENT ‘显示方案类型’,
display_owner bigint(20) NOT NULL COMMENT ‘所有者用户id’,
share_flag tinyint(1) NOT NULL COMMENT ‘是否可分享’,
default_flag tinyint(1) NOT NULL COMMENT ‘是否默认’,
tenant_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘租户id’,
object_version_number bigint(20) NOT NULL DEFAULT ‘1’ COMMENT ‘行版本号，用来处理锁’,
creation_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_by bigint(20) NOT NULL DEFAULT ‘-1’,
last_updated_by bigint(20) NOT NULL DEFAULT ‘-1’,
last_update_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
ATTRIBUTE_CATEGORY varchar(30) DEFAULT NULL,
ATTRIBUTE1 varchar(150) DEFAULT NULL,
ATTRIBUTE2 varchar(150) DEFAULT NULL,
ATTRIBUTE3 varchar(150) DEFAULT NULL,
ATTRIBUTE4 varchar(150) DEFAULT NULL,
ATTRIBUTE5 varchar(150) DEFAULT NULL,
PRIMARY KEY (grid_display_plan_id) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY hgl_grid_display_plan_u1 (grid_display_plan_id),
KEY hgl_grid_display_plan_n1 (grid_display_type,tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=30049 COMMENT=‘表格显示方案表’

CREATE TABLE hgl_grid_display_plan_tl (
grid_display_plan_id bigint(20) NOT NULL COMMENT ‘表格显示方案id’,
lang varchar(30) NOT NULL COMMENT ‘多语言’,
grid_display_name varchar(255) NOT NULL COMMENT ‘表格显示方案名称’,
UNIQUE KEY hgl_grid_display_plan_tl_u1 (grid_display_plan_id,lang)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=‘表格显示方案多语言表’

CREATE TABLE hgl_grid_plan_permission (
grid_plan_permission_id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘表ID，主键’,
grid_display_plan_id bigint(20) NOT NULL COMMENT ‘表格显示方案id’,
user_id bigint(20) NOT NULL COMMENT ‘用户id’,
view_flag tinyint(1) NOT NULL COMMENT ‘查看标识’,
edit_flag tinyint(1) NOT NULL COMMENT ‘编辑标识’,
delete_flag tinyint(1) NOT NULL COMMENT ‘删除标识’,
share_flag tinyint(1) NOT NULL COMMENT ‘分享标识’,
tenant_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘租户id’,
object_version_number bigint(20) NOT NULL DEFAULT ‘1’ COMMENT ‘行版本号，用来处理锁’,
creation_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_by bigint(20) NOT NULL DEFAULT ‘-1’,
last_updated_by bigint(20) NOT NULL DEFAULT ‘-1’,
last_update_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
ATTRIBUTE_CATEGORY varchar(30) DEFAULT NULL,
ATTRIBUTE1 varchar(150) DEFAULT NULL,
ATTRIBUTE2 varchar(150) DEFAULT NULL,
ATTRIBUTE3 varchar(150) DEFAULT NULL,
ATTRIBUTE4 varchar(150) DEFAULT NULL,
ATTRIBUTE5 varchar(150) DEFAULT NULL,
PRIMARY KEY (grid_plan_permission_id) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY hgl_grid_display_plan_permission_u1 (grid_plan_permission_id),
UNIQUE KEY hgl_grid_display_plan_permission_u2 (grid_display_plan_id,user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=30015 COMMENT=‘表格显示方案权限表’

JaySon-Huang · 2021 年12 月 20 日 09:39

@YangZhuo970325 你好～经过测试，确认跟 https://github.com/pingcap/tidb/issues/29705 是同一个问题。目前在 master 上修复了 https://github.com/pingcap/tidb/pull/30231 ，预计 v5.3.1 会带上这个修复。

YangZhuo970325 · 2021 年12 月 20 日 10:35

ok~

spc_monkey · 2021 年12 月 21 日 07:18

system · 2022 年10 月 31 日 19:05

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