# [Know Issue] TiKV 与 TiFlash 执行结果不一致

## Key Words

TiKV, TiFlash, 查询结果不一致

## Description

在 7.1.1 版本中，TiKV 和 TiFlash 执行相同的 SQL 查询，返回的结果存在不一致性。

最小复现步骤，建表语句如下：

```SQL
create table t (a int, b int);
alter table t set tiflash replica 1;
insert into t values (1, 1);
insert into t values (1, 1);
insert into t values (1, 1);
insert into t values (1, 1);
insert into t values (1, 1);
```

TiKV 的查询结果如下：

```SQL
select a, count(*) from (
select a, b from t
union all
select a, b from t
) t group by a order by a limit 10;
+------+----------+
| a    | count(*) |
+------+----------+
|    1 |       10 |
+------+----------+
```

TiFlash 的查询结果如下：

```SQL
set @@tidb_allow_mpp=1;
set @@tidb_enforce_mpp=1;
select a, count(*) from (
select a, b from t
union all
select a, b from t
) t group by a order by a limit 10;
+------+----------+
| a    | count(*) |
+------+----------+
|    1 |        2 |
+------+----------+

mysql> explain select a, count(*) from ( select a, b from t union all select a, b from t ) t group by a;
+----------------------------------------------+---------+--------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| id                                           | estRows | task         | access object | operator info                                                                                                                           |
+----------------------------------------------+---------+--------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| TableReader_98                               | 8.00    | root         |               | MppVersion: 1, data:ExchangeSender_97                                                                                                   |
| └─ExchangeSender_97                          | 8.00    | mpp[tiflash] |               | ExchangeType: PassThrough                                                                                                               |
|   └─Projection_14                            | 8.00    | mpp[tiflash] |               | Column#7, Column#9                                                                                                                      |
|     └─Projection_96                          | 8.00    | mpp[tiflash] |               | Column#9, Column#7                                                                                                                      |
|       └─HashAgg_95                           | 8.00    | mpp[tiflash] |               | group by:Column#7, funcs:count(Column#10)->Column#9, funcs:firstrow(Column#11)->Column#7, stream_count: 16                              |
|         └─ExchangeReceiver_91                | 8.00    | mpp[tiflash] |               | stream_count: 16                                                                                                                        |
|           └─ExchangeSender_90                | 8.00    | mpp[tiflash] |               | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: Column#7, collate: binary], stream_count: 16                          |
|             └─Union_89                       | 8.00    | mpp[tiflash] |               |                                                                                                                                         |
|               ├─HashAgg_77                   | 4.00    | mpp[tiflash] |               | group by:test.t.a, funcs:count(1)->Column#10, funcs:firstrow(test.t.a)->Column#11, funcs:firstrow(test.t.a)->Column#7, stream_count: 16 |
|               │ └─ExchangeReceiver_37        | 5.00    | mpp[tiflash] |               | stream_count: 16                                                                                                                        |
|               │   └─ExchangeSender_36        | 5.00    | mpp[tiflash] |               | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: test.t.a, collate: binary], stream_count: 16                          |
|               │     └─TableFullScan_35       | 5.00    | mpp[tiflash] | table:t       | keep order:false, stats:pseudo                                                                                                          |
|               └─HashAgg_82                   | 4.00    | mpp[tiflash] |               | group by:test.t.a, funcs:count(1)->Column#10, funcs:firstrow(test.t.a)->Column#11, funcs:firstrow(test.t.a)->Column#7, stream_count: 16 |
|                 └─ExchangeReceiver_65        | 5.00    | mpp[tiflash] |               | stream_count: 16                                                                                                                        |
|                   └─ExchangeSender_64        | 5.00    | mpp[tiflash] |               | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: test.t.a, collate: binary], stream_count: 16                          |
|                     └─TableFullScan_63       | 5.00    | mpp[tiflash] | table:t       | keep order:false, stats:pseudo                                                                                                          |
+----------------------------------------------+---------+--------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------+
16 rows in set (0.00 sec)
```

## Root Cause

此问题的根本原因在于优化器的 Bug。在优化器将 Count Agg Func 推过 Union 的过程中，打破了 TiFlash 执行器的假设，如果在最终选择了 MPP 计划，将会出现结果正确性问题。

## Workaround

解决方案是设置 `tidb_opt_agg_push_down` 参数为 0。这个操作可以通过执行以下 SQL 语句完成：

```SQL
set tidb_opt_agg_push_down=0;
```

执行上述语句后，`tidb_opt_agg_push_down` 参数的值将被设置为 0。

## GitHub Issue

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

## Affect Version

v7.1.1