pingcap / pingcap/tidb

Agg cannot pushdown to tiflash when there are expression index

Open
#41,380 0 comments 0 reactions 1 assignee Claimed by @AilinKid View on GitHub
affects-7.0 affects-7.1 affects-7.5 affects-8.1 may-affects-4.0 may-affects-5.0 may-affects-5.1 may-affects-5.2 may-affects-5.3 may-affects-5.4 may-affects-6.0 may-affects-6.1 may-affects-6.2 may-affects-6.3 may-affects-6.4 may-affects-6.5 may-affects-6.6 severity/major sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)
1. run following sql
```
drop table if exists t2;
create table t2(c1 int, c2 varchar(100));
insert into t2 values(1, 'xhy'), (2, 'abc');
alter table t2 set tiflash replica 1;
```
2. check tiflash_replica is avaiable.
3. add expression index
```
alter table t2 add index idx2((lower(c2)));
```
4. Use `nth_paln` to check all available plans:
```
explain select /*+ nth_plan(1) */ count(c1) from t2 where lower(c2) = 'abc';
```

### 2. What did you expect to see? (Required)
Can generate plan that Agg can pushdown to tiflash.

### 3. What did you see instead (Required)
There is no plan that Selection and HashAgg pushdown to tiflash.
```
MySQL [test]> explain select /*+ nth_plan(2) */ count(c1) from t2 where lower(c2) = 'abc';
+------------------------------+----------+--------------+---------------+-----------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------+----------+--------------+---------------+-----------------------------------+
| HashAgg_7 | 1.00 | root | | funcs:count(test.t2.c1)->Column#5 |
| └─Selection_17 | 8000.00 | root | | eq(lower(test.t2.c2), "abc") |
| └─Projection_16 | 10000.00 | root | | test.t2.c1, lower(test.t2.c2) |
| └─TableReader_15 | 10000.00 | root | | data:TableFullScan_14 |
| └─TableFullScan_14 | 10000.00 | cop[tiflash] | table:t2 | keep order:false, stats:pseudo |
+------------------------------+----------+--------------+---------------+-----------------------------------+
5 rows in set (0.002 sec)
```
### 4. What is your TiDB version? (Required)
```
MySQL [test]> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.5.0
Edition: Community
Git Commit Hash: 77776e17159e9c6faf6aeedf3085a7a2524d3205
Git Branch: heads/refs/tags/v6.5.0
UTC Build Time: 2022-12-05 13:50:22
GoVersion: go1.19.3
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.