pingcap / pingcap/tidb

STREAM_AGG hint doesn't take effect when there's function in the group-by items

Open
#33,293 7 comments 0 reactions 0 assignees View on GitHub
help wanted sig/planner type/enhancement
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)
```
create table tx (a timestamp, b int);
create table ty (d int, e int);

explain select /*+ STREAM_AGG() */
sa,
sum(se)
from
(
select
t1.a as sa,
t2.e as se
from tx t1
left join ty t2 on t1.b = t2.d
) tt
group by sa;

explain select /*+ STREAM_AGG() */
sa,
sum(se)
from
(
select
date(t1.a) as sa,
t2.e as se
from tx t1
left join ty t2 on t1.b = t2.d
) tt
group by sa;
```

### 2. What did you expect to see? (Required)
Both queries use stream agg.

### 3. What did you see instead (Required)
The first query uses stream agg while the second query uses hash agg and it doesn't have a warning on why the hint doesn't take effect.
```
mysql> explain select /*+ STREAM_AGG() */
-> sa,
-> sum(se)
-> from
-> (
-> select
-> t1.a as sa,
-> t2.e as se
-> from tx t1
-> left join ty t2 on t1.b = t2.d
-> ) tt
-> group by sa;
+--------------------------------------+----------+-----------+---------------+----------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------------+----------+-----------+---------------+----------------------------------------------------------------------------------------+
| Projection_7 | 8000.00 | root | | test.tx.a, Column#7 |
| └─StreamAgg_11 | 8000.00 | root | | group by:Column#10, funcs:sum(Column#8)->Column#7, funcs:firstrow(Column#9)->test.tx.a |
| └─Projection_21 | 12487.50 | root | | cast(test.ty.e, decimal(10,0) BINARY)->Column#8, test.tx.a, test.tx.a |
| └─Sort_19 | 12487.50 | root | | test.tx.a |
| └─HashJoin_12 | 12487.50 | root | | left outer join, equal:[eq(test.tx.b, test.ty.d)] |
| ├─TableReader_18(Build) | 9990.00 | root | | data:Selection_17 |
| │ └─Selection_17 | 9990.00 | cop[tikv] | | not(isnull(test.ty.d)) |
| │ └─TableFullScan_16 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─TableReader_15(Probe) | 10000.00 | root | | data:TableFullScan_14 |
| └─TableFullScan_14 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+--------------------------------------+----------+-----------+---------------+----------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

mysql> explain select /*+ STREAM_AGG() */
-> sa,
-> sum(se)
-> from
-> (
-> select
-> date(t1.a) as sa,
-> t2.e as se
-> from tx t1
-> left join ty t2 on t1.b = t2.d
-> ) tt
-> group by sa;
+------------------------------------+----------+-----------+---------------+----------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------+----------+-----------+---------------+----------------------------------------------------------------------------------------------------------+
| Projection_7 | 8000.00 | root | | Column#7, Column#8 |
| └─HashAgg_8 | 8000.00 | root | | group by:Column#12, funcs:sum(Column#10)->Column#8, funcs:firstrow(Column#11)->Column#7 |
| └─Projection_16 | 12487.50 | root | | cast(test.ty.e, decimal(10,0) BINARY)->Column#10, date(test.tx.a)->Column#11, date(test.tx.a)->Column#12 |
| └─HashJoin_9 | 12487.50 | root | | left outer join, equal:[eq(test.tx.b, test.ty.d)] |
| ├─TableReader_15(Build) | 9990.00 | root | | data:Selection_14 |
| │ └─Selection_14 | 9990.00 | cop[tikv] | | not(isnull(test.ty.d)) |
| │ └─TableFullScan_13 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─TableReader_12(Probe) | 10000.00 | root | | data:TableFullScan_11 |
| └─TableFullScan_11 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+------------------------------------+----------+-----------+---------------+----------------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)
```

### 4. What is your TiDB version? (Required)
I tried master and v5.3.0. Both have the bug.

Contributor guide

Open the contributing guide

Research direction

Start with the two provided EXPLAIN queries and compare how STREAM_AGG() is handled when GROUP BY uses a direct column versus date(t1.a). Trace the planner's stream-aggregation hint handling and identify how the function expression prevents the hint; done means both queries use stream aggregation or clearly report why the hint cannot apply.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.