Optimizer removes order by when it's sub-part of group by (test_if_subpart) too early, before it decides how to execute group by. Order by can only be removed if group by naturally delivers sorted results. it's not the case, for example, if group by is pushed down to the engine.
Here is the example query that triggers the bug:
select a,b,sum(c) from opportunities group by a,b order by a desc;
Note (Code 1003): select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,sum(`test`.`t`.`c`) AS `sum(c)` from `test`.`t` group by `test`.`t`.`a` desc,`test`.`t`.`b` order by `test`.`t`.`a` desc
Alice Sherepa
added a comment - Hi Roman, do you mean bug - results in a wrong order? Please add an example.
create or replace table t (a int, b int, c int);
insert into t values (2,2,4), (2,1,5), (2,1,4),(2,2,6),(8,3,4),(1,6,1),(2,3,4),(5,3,2);
select a,b,sum(c) from t group by a,b order by a desc;
explain extended select a,b,sum(c) from t group by a,b order by a desc;
MariaDB [test]> select a,b,sum(c) from t group by a,b order by a desc;
+------+------+--------+
| a | b | sum(c) |
+------+------+--------+
| 8 | 3 | 4 |
| 5 | 3 | 2 |
| 2 | 1 | 9 |
| 2 | 2 | 10 |
| 2 | 3 | 4 |
| 1 | 6 | 1 |
+------+------+--------+
6 rows in set (0.000 sec)
MariaDB [test]> explain extended select a,b,sum(c) from t group by a,b order by a desc;
+------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using temporary; Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.000 sec)
Note (Code 1003): select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,sum(`test`.`t`.`c`) AS `sum(c)` from `test`.`t` group by `test`.`t`.`a` desc,`test`.`t`.`b` order by `test`.`t`.`a` desc
Hi Roman, do you mean bug - results in a wrong order? Please add an example.
create or replace table t (a int, b int, c int);
insert into t values (2,2,4), (2,1,5), (2,1,4),(2,2,6),(8,3,4),(1,6,1),(2,3,4),(5,3,2);
select a,b,sum(c) from t group by a,b order by a desc;
explain extended select a,b,sum(c) from t group by a,b order by a desc;
MariaDB [test]> select a,b,sum(c) from t group by a,b order by a desc;
+------+------+--------+
| a | b | sum(c) |
+------+------+--------+
| 8 | 3 | 4 |
| 5 | 3 | 2 |
| 2 | 1 | 9 |
| 2 | 2 | 10 |
| 2 | 3 | 4 |
| 1 | 6 | 1 |
+------+------+--------+
6 rows in set (0.000 sec)
MariaDB [test]> explain extended select a,b,sum(c) from t group by a,b order by a desc;
+------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using temporary; Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.000 sec)
Note (Code 1003): select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,sum(`test`.`t`.`c`) AS `sum(c)` from `test`.`t` group by `test`.`t`.`a` desc,`test`.`t`.`b` order by `test`.`t`.`a` desc