Bug #101574 Cost on sort iterator, does not contain sort cost
Submitted: 12 Nov 2020 7:04 Modified: 9 Mar 7:55
Reporter: Xavier Zhang Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[12 Nov 2020 7:04] Xavier Zhang
Description:
Cost on sort iterator, does not contain sort cost

The two query only different in with rollup.

If query without rollup, the table scan cost is correct.
If query with rollup, the table scan cost displayed in sort iterator, and not contain the sort cost.

How to repeat:
create table t1(id int not null AUTO_INCREMENT  primary key, a1 int, a2 int, a3 int);

insert into t1 (a1, a2, a3) values(rand() * 100, rand() * 1000, rand() * 10000);
insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1;
insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1;
insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1;
insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1;
insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1;
insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1;
insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1;
insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1;
insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1;

mysql> explain  format = tree select id, a1, count(*) from t1 group by id, a1 \G
*************************** 1. row ***************************
EXPLAIN: -> Table scan on <temporary>
    -> Aggregate using temporary table
        -> Table scan on t1  (cost=51.95 rows=512)

1 row in set (0.00 sec)

mysql> explain  format = tree select id, a1, count(*) from t1 group by id, a1 with rollup\G
*************************** 1. row ***************************
EXPLAIN: -> Group aggregate with rollup: count(0)
    -> Sort: t1.id, t1.a1  (cost=51.95 rows=512)
        -> Table scan on t1

1 row in set (0.00 sec)
[12 Nov 2020 13:44] MySQL Verification Team
Hi Mr. Zhang,

Thank you for your bug report.

However, this is not a bug.

Since 8.0, MySQL does not do automatically ORDER BY after GROUP BY clause. Hence, there is no sorting involved. Sorting has to be involved with ROLLUP in some cases, like in your example.

This is all explained in our Reference Manual.

Not a bug.
[9 Mar 7:55] Xavier Zhang
The focus is,if push sort to table, the table cost will set to sort iterator。