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

【 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

执行计划如下:


1赞

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

1赞

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

1赞

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

1赞

我这边需要用到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

1赞

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

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

这个是不加的sql分析

这个是加了之后的sql分析

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

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

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

好的,麻烦了

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

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

主要涉及到三张表: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=‘表格显示方案权限表’

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

ok~

:call_me_hand::call_me_hand: