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: | |
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,
[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.