STREAM_AGG hint doesn't take effect when there's function in the group-by items
- 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
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