# [Known Issues] TiDB 查询 partition table，返回报错 “UnionExec chunk column count mismatch”

## Key Words

TiDB, partition table, UnionExec chunk column count mismatch, tidb_partition_prune_mode

## Description

业务原地升级从 v4.0.9 升级到 v5.2.0 以后，SQL 执行报错，主要报错是 “UnionExec chunk column count mismatch”，导致业务无法继续执行。

查询结果预期为 1 rows

```sql
mysql> SELECT id FROM `report` WHERE (`create_date` >= '2021-08-27') AND (`create_date` <= '2021-09-02') AND (`ad_user_id` = 2549648315) GROUP BY `create_date`, `ad_plan_id`;
+-----------+
| id        |
+-----------+
| 741696903 |
+-----------+
1 row in set (0.00 sec)
```

查询 count 结果报错，“UnionExec chunk column count mismatch”

```sql
mysql> select count(1) from (SELECT id FROM `report` WHERE (`create_date` >= '2021-08-27') AND (`create_date` <= '2021-09-02') AND (`ad_user_id` = 2549648315) GROUP BY `create_date`, `ad_plan_id`)c;
ERROR 1105 (HY000): Internal error: UnionExec chunk column count mismatch, req: 3, result: 2
mysql>

mysql> explain analyze SELECT COUNT(*) FROM (SELECT create_date,id FROM `report` WHERE (`create_date` >= '2021-08-27') AND (`create_date` <= '2021-09-02') AND (`ad_user_id` = 2549648315) GROUP BY `create_date`, `ad_plan_id`) `c`;
ERROR 1105 (HY000): Internal error: UnionExec chunk column count mismatch, req: 3, result: 2
```

SQL MODE 没有做特殊设置

```sql
mysql> show variables like '%sql_mode%';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.01 sec)
```

SQL 对应的 Table schema

```sql
CREATE TABLE `report` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ad_user_id` bigint(20) DEFAULT '0',
  `ad_plan_id` int(11) unsigned NOT NULL,
  `ad_group_id` int(11) unsigned NOT NULL,
  `ad_advert_id` bigint(20) unsigned DEFAULT '0',
  `user_name` char(64) NOT NULL,
  `ad_plan_name` varchar(128) NOT NULL ,
  `ad_group_name` varchar(128) NOT NULL,
  `ad_advert_name` varchar(128) NOT NULL,
  `data_type` tinyint(4) DEFAULT '1',
  `adspace_id` int(11) unsigned DEFAULT '0',
  `views` int(11) DEFAULT '0',
  `clicks` int(11) DEFAULT '0',
  `views2` int(11) DEFAULT '0',
  `total_cost` decimal(20,5) NOT NULL DEFAULT '0.00000' ,
  `download_counts` int(11) DEFAULT '0',
  `conversion_1` int(11) DEFAULT '0',
  `conversion_2` int(11) DEFAULT '0',
  `conversion_3` int(11) DEFAULT '0',
  `conversion_4` int(11) DEFAULT '0',
  `conversion_5` int(11) DEFAULT '0',
  `conversion_6` int(11) DEFAULT '0',
  `conversion_7` int(11) DEFAULT '0',
  `create_date` date NOT NULL ,
  PRIMARY KEY (`id`,`create_date`) /*T![clustered_index] NONCLUSTERED */,
  KEY `updateap` (`create_date`,`ad_user_id`,`ad_plan_id`,`ad_group_id`,`ad_advert_id`),
  KEY `selectreport` (`ad_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=743410082
PARTITION BY RANGE COLUMNS(create_date) (
  PARTITION `p202102` VALUES LESS THAN ("2021-03-01"),
  PARTITION `p202103` VALUES LESS THAN ("2021-04-01"),
  PARTITION `p202104` VALUES LESS THAN ("2021-05-01"),
  PARTITION `p202105` VALUES LESS THAN ("2021-06-01"),
  PARTITION `p202106` VALUES LESS THAN ("2021-07-01"),
  PARTITION `p202107` VALUES LESS THAN ("20210801"),
  PARTITION `p202108` VALUES LESS THAN ("20210901"),
  PARTITION `p202109` VALUES LESS THAN ("20211001"),
  PARTITION `p202110` VALUES LESS THAN ("20211101"),
  PARTITION `p202111` VALUES LESS THAN ("20211201"),
  PARTITION `p202112` VALUES LESS THAN ("20220101"),
  PARTITION `p202201` VALUES LESS THAN ("20220201"),
  PARTITION `p202202` VALUES LESS THAN ("20220301")
)
```

## Root Cause

优化器一个优化步骤中将执行计划的 schema 信息改写错误导致的，这个 bug 从 5.0 开始出现。

issue: [https://github.com/pingcap/tidb/issues/26554](https://github.com/pingcap/tidb/issues/26554)

## Diagnostic Steps

1. 查看是否是分区表
2. 查看报错日志包含  **UnionExec chunk column count mismatch** 
3. 尝试设置参数 tidb_partition_prune_mode='dynamic' 能否正常运行

## Resolution

Upgrade to the unaffected versions.

PR: [https://github.com/pingcap/tidb/pull/27798](https://github.com/pingcap/tidb/pull/27798)

## Workaround

可以尝试设置 [tidb_partition_prune_mode](https://docs.pingcap.com/zh/tidb/stable/system-variables/#tidb_partition_prune_mode-%E4%BB%8E-v51-%E7%89%88%E6%9C%AC%E5%BC%80%E5%A7%8B%E5%BC%95%E5%85%A5) 参数

```sql
set tidb_partition_prune_mode='dynamic'
```

## Affected Versions

v5.1.0 - v5.1.1
v5.0.0 - v5.0.5
v4.0.0 - v4.0.14