Bug #63922 Order by null dosn't change sorting of grouped result
Submitted: 4 Jan 2012 15:05 Modified: 10 Apr 2013 1:41
Reporter: Kostya L, Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Documentation Severity:S1 (Critical)
Version:14.14 Distrib 5.5.16, for Linux (x86_64) OS:Linux (3.1.1)
Assigned to: Paul DuBois CPU Architecture:Any
Tags: GROUP BY, order by null, SELECT

[4 Jan 2012 15:05] Kostya L,
Description:
Table's in question engine is InnoDb.
http://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html says "If a query includes GROUP BY but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying ORDER BY NULL".
But "ORDER BY NULL" doesn't change anything, results are still sorted by "group by" column.

How to repeat:
Issue "select pk, column1 from table1 group by column1 order by null;" and see that data is ordered by column1 and not in "random" order which is by pk from what I've seen.
[4 Jan 2012 15:54] Valeriy Kravchuk
The following test case clearly demonstrates the difference:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.20-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table ti1(c1 int primary key, c2 int);
Query OK, 0 rows affected (0.70 sec)

mysql> insert into ti1 values (1,2), (2,2), (3,2), (4,1), (5,1);
Query OK, 5 rows affected (0.41 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select c1, c2 from ti1 group by c2;
+----+------+
| c1 | c2   |
+----+------+
|  4 |    1 |
|  1 |    2 |
+----+------+
2 rows in set (0.45 sec)

mysql> select c1, c2 from ti1 group by c2 order by null;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    2 |
|  4 |    1 |
+----+------+
2 rows in set (0.00 sec)

mysql> show create table ti1\G
*************************** 1. row ***************************
       Table: ti1
Create Table: CREATE TABLE `ti1` (
  `c1` int(11) NOT NULL,
  `c2` int(11) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.03 sec)

mysql> explain select c1, c2 from ti1 group by c2;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | ti1   | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.03 sec)

mysql> explain select c1, c2 from ti1 group by c2 order by null;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
|  1 | SIMPLE      | ti1   | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
1 row in set (0.00 sec)

Please, check.
[4 Jan 2012 17:57] Kostya L,
Yes, your queries work as expected. I'll try to find this crucial difference in two tables and comment on it later.
[4 Jan 2012 18:21] Kostya L,
Execute "ALTER TABLE `solar`.`ti1` ADD INDEX `c2` ( `c2` );" and see that "order by null" is not working now. It's not the case for MyISAM table.
[4 Jan 2012 18:29] Valeriy Kravchuk
OK, in this case new index is used and plan is the same for both queries (as well as results):

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.5.20-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter table ti1 add index(c2);
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select c1, c2 from ti1 group by c2;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | ti1   | index | NULL          | c2   | 5       | NULL |    5 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.02 sec)

mysql> explain select c1, c2 from ti1 group by c2 order by null;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | ti1   | index | NULL          | c2   | 5       | NULL |    5 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> select c1, c2 from ti1 group by c2;
+----+------+
| c1 | c2   |
+----+------+
|  4 |    1 |
|  1 |    2 |
+----+------+
2 rows in set (0.02 sec)

mysql> select c1, c2 from ti1 group by c2 order by null;
+----+------+
| c1 | c2   |
+----+------+
|  4 |    1 |
|  1 |    2 |
+----+------+
2 rows in set (0.00 sec)

But this is not a bug, as in any case there is no filesort step for ordering and there is nothing to avoid. 

And yes, with MyISAM index is not used and ORDER BY NULL matters again, so what? ORDER BY NULL prevents extra filesort step in ANY case. In some cases it does nothing (if filesort was not used because optimizer used index preventing it).
[5 Jan 2012 10:50] Kostya L,
Than I guess you should mention about index and engine differences in documentation, or add a suggestion to each and every clause in documentation to use "explain" to verify its intended use.
[5 Jan 2012 11:42] Kostya L,
Interesting thing is when I try to use "IGNORE INDEX" "ORDER BY NULL" works but only if it's "IGNORE INDEX (c2)". Neither of "FOR" forms seems to have any effect.
And this part is also missing from docs.
[5 Jan 2012 12:43] Valeriy Kravchuk
I accept this as a valid request for more detailed documentation.
[10 Apr 2013 1:41] Paul DuBois
Relying on this behavior has been deemed deprecated, so we won't attempt to specify when it might not occur.

http://dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html

Relying on implicit GROUP BY sorting in MySQL 5.6 is deprecated. To achieve a specific sort order of grouped results, it is preferable to use an explicit ORDER BY clause. GROUP BY sorting is a MySQL extension that may change in a future release; for example, to make it possible for the optimizer to order groupings in whatever manner it deems most efficient and to avoid the sorting overhead.