pingcap/tidb
在 GitHub 查看STREAM_AGG hint doesn't take effect when there's function in the group-by items
Open
#33,293 创建于 2022年3月21日
help wantedsig/plannertype/enhancement
仓库指标
- Star
- (40,090 star)
- PR 合并指标
- (平均合并 14天 4小时) (30 天内合并 346 个 PR)
描述
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.