pingcap/tidb
Vedi su GitHubSTREAM_AGG hint doesn't take effect when there's function in the group-by items
Open
#33.293 aperta il 21 mar 2022
help wantedsig/plannertype/enhancement
Metriche repository
- Star
- (40.090 star)
- Metriche merge PR
- (Merge medio 14g 4h) (346 PR mergiate in 30 g)
Descrizione
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.