MySQL Bugs: #63922: Order by null dosn't change sorting of grouped result

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
Triage:	Needs Triage: D3 (Medium)

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.

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.

Yes, your queries work as expected. I'll try to find this crucial difference in two tables and comment on it later.

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.

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).

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.

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.

I accept this as a valid request for more detailed documentation.

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.