planner: invalid Agg mode on MPP plans with sub-queries accessing partitioning tables under static partition mode

[qw4990]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t (
  d date,
  v int,
  primary key(d, v)
) partition by range columns (d) (
  partition p1 values less than ('2023-07-02'),
  partition p2 values less than ('2023-07-03')
);

alter table t set tiflash replica 1;
set tidb_partition_prune_mode='static';
select * from information_schema.tiflash_replica; -- wait until the replication progress finish

select 1 from (
  select /*+ read_from_storage(tiflash[t]) */ sum(1)
  from t where d BETWEEN '2023-07-01' and '2023-07-03' group by d
) total;
ERROR 1105 (HY000): other error for mpp stream: Code: 0, e.displayText() = DB::TiFlashException: Different aggregation mode detected, e.what() = DB::TiFlashException,

2. What did you expect to see? (Required)

No error

3. What did you see instead (Required)

ERROR 1105 (HY000): other error for mpp stream: Code: 0, e.displayText() = DB::TiFlashException: Different aggregation mode detected, e.what() = DB::TiFlashException,

4. What is your TiDB version? (Required)

[qw4990]

The root cause is that for this query, the optimizer uses partial AggMode and final AggMode together in a single HashAgg operator (HashAgg_58 in this case), but TiFlash doesn't support this use case, it doesn't allow user to use final and non-final mode in the same Agg operator (see the snippet below):

TiKV can support this usage:

[kennedy8312]

/type regression

[kennedy8312]

Regression Analysis
The issue was seen on v7.5.0 but not v6.1.0.

mysql> select * from information_schema.tiflash_replica;
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| test | t | 77 | 1 | | 1 | 1 |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
1 row in set (0.01 sec)

mysql> select 1 from (
-> select /*+ read_from_storage(tiflash[t]) */ sum(1)
-> from t where d BETWEEN '2023-07-01' and '2023-07-03' group by d
-> ) total;
Empty set (0.03 sec)

mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.1.0
Edition: Community
Git Commit Hash: 1a89dec
Git Branch: heads/refs/tags/v6.1.0
UTC Build Time: 2022-06-05 05:09:33
GoVersion: go1.18.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[kennedy8312]

mysql> select * from information_schema.tiflash_replica; -- wait until the replication progress finish
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| test | t | 102 | 1 | | 1 | 1 |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
1 row in set (0.02 sec)

mysql> select 1 from (
-> select /*+ read_from_storage(tiflash[t]) */ sum(1)
-> from t where d BETWEEN '2023-07-01' and '2023-07-03' group by d
-> ) total;
ERROR 1105 (HY000): other error for mpp stream: Code: 0, e.displayText() = DB::TiFlashException: Different aggregation mode detected, e.what() = DB::TiFlashException,
mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v7.5.0
Edition: Community
Git Commit Hash: 069631e
Git Branch: heads/refs/tags/v7.5.0
UTC Build Time: 2023-11-24 08:41:10
GoVersion: go1.21.3
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[kennedy8312]

Regression Analysis
The testcase started to fail on v7.3.0.
Start to analyze regression behavior among releases for issue 51362
Version v5.4.0 : Success
Version v6.0.0 : Success
Version v6.1.0 : Success
Version v6.2.0 : Success
Version v6.3.0 : Success
Version v6.4.0 : Success
Version v6.5.0 : Success
Version v6.6.0 : Success
Version v7.0.0 : Success
Version v7.1.0 : Success
Version v7.2.0 : Success
Version v7.3.0 : Failure
Version v7.4.0 : Failure
Version v7.5.0 : Failure
Version v7.6.0 : Failure

[seiya-annie]

/found customer