wrong results executing streamAgg with order by

[SunRunAway]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> drop table t;
Query OK, 0 rows affected (0.19 sec)

mysql> create table t(a int);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t values (1), (1), (2);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select /*+ hash_agg() */ count(*) c from t group by a order by c  limit 1;
+---+
| c |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> select /*+ stream_agg() */ count(*) c from t group by a order by c  limit 1;
+---+
| c |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

2. What did you expect to see? (Required)

mysql> select /*+ stream_agg() */ count(*) c from t group by a order by c  limit 1;
+---+
| c |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

3. What did you see instead (Required)

mysql> select /*+ stream_agg() */ count(*) c from t group by a order by c  limit 1;
+---+
| c |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

4. Affected version (Required)

master, do not know whether other versions are affected.

5. Root Cause Analysis

Shoud use TopN but not Limit here.

mysql> desc select /*+ stream_agg() */ count(*) c from t group by a order by c  limit 1;
+------------------------------+---------+-----------+---------------+---------------------------------------------+
| id                           | estRows | task      | access object | operator info                               |
+------------------------------+---------+-----------+---------------+---------------------------------------------+
| Limit_13                     | 1.00    | root      |               | offset:0, count:1                           |
| └─StreamAgg_26               | 1.00    | root      |               | group by:test.t.a, funcs:count(1)->Column#3 |
|   └─Sort_31                  | 1.25    | root      |               | test.t.a                                    |
|     └─TableReader_30         | 1.25    | root      |               | data:TableFullScan_29                       |
|       └─TableFullScan_29     | 1.25    | cop[tikv] | table:t       | keep order:false, stats:pseudo              |
+------------------------------+---------+-----------+---------------+---------------------------------------------+
5 rows in set (0.00 sec)

[SunRunAway]

still get wrong results without LIMIT

mysql> select /*+ hash_agg() */ count(*) c from t group by a order by c;
+---+
| c |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

mysql> select /*+ stream_agg() */ count(*) c from t group by a order by c;
+---+
| c |
+---+
| 2 |
| 1 |
+---+
2 rows in set (0.00 sec)

[eurekaka]

RC: getEnforcedStreamAggs didn't check the required order property from parent.

[ChenPeng2013]

verified on
master: v4.0.0-beta.2-632-g6c305e2a4
release-4.0: v4.0.0-66-gcf781a347
release-3.1: v3.1.2-31-g11cff9007