MySQL Bugs: #69528: ORDER BY doesn't work with JOIN

Bug #69528	ORDER BY doesn't work with JOIN
Submitted:	20 Jun 2013 18:53	Modified:	16 Oct 2013 15:02
Reporter:	Shawn Hogan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S1 (Critical)
Version:	5.6.11-ndb-7.3.2	OS:	Linux (SLES 11.4)
Assigned to:		CPU Architecture:	Any

Description:
Tested this with ndbcluster, InnoDB and MyISAM storage engines, all yielding the same result.

From a 5.6.11 mysqld node, everything is as expected except the last query... as soon as you add a JOIN to it, it no longer ORDERs properly.

Tables have been dropped/reloaded, indexes dropped/reloaded, etc.

How to repeat:
From a 5.6.11 mysqld node, everything is as expected except the last query... as soon as you add a JOIN to it, it no longer ORDERs properly.

mysql> SELECT VERSION();
+----------------------------------+
| VERSION()                        |
+----------------------------------+
| 5.6.11-ndb-7.3.2-cluster-gpl-log |
+----------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT thread.thread_id, thread.last_post_date FROM xf_thread AS thread WHERE thread.node_id = 3 AND thread.sticky = 0 ORDER BY thread.last_post_date DESC LIMIT 5;
+----+-------------+--------+------+------------------------------------------------------+-------------------------------+---------+-------------+-------+-------------+
| id | select_type | table  | type | possible_keys                                        | key                           | key_len | ref         | rows  | Extra       |
+----+-------------+--------+------+------------------------------------------------------+-------------------------------+---------+-------------+-------+-------------+
|  1 | SIMPLE      | thread | ref  | node_id_last_post_date,node_id_sticky_last_post_date | node_id_sticky_last_post_date | 5       | const,const | 15744 | Using where |
+----+-------------+--------+------+------------------------------------------------------+-------------------------------+---------+-------------+-------+-------------+
1 row in set (0.00 sec)

mysql> SELECT thread.thread_id, thread.last_post_date FROM xf_thread AS thread WHERE thread.node_id = 3 AND thread.sticky = 0 ORDER BY thread.last_post_date DESC LIMIT 5;
+-----------+----------------+
| thread_id | last_post_date |
+-----------+----------------+
|   2662461 |     1371751943 |
|   2662457 |     1371750399 |
|   2662389 |     1371750102 |
|   2662313 |     1371742694 |
|   2628799 |     1371736621 |
+-----------+----------------+
5 rows in set (0.01 sec)

mysql> EXPLAIN SELECT thread.thread_id, thread.last_post_date FROM xf_thread AS thread LEFT JOIN xf_user AS user ON (user.user_id = thread.user_id) WHERE thread.node_id = 3 AND thread.sticky = 0 ORDER BY thread.last_post_date DESC LIMIT 5;
+----+-------------+--------+--------+------------------------------------------------------+-------------------------------+---------+------------------------+-------+----------------------------------------+
| id | select_type | table  | type   | possible_keys                                        | key                           | key_len | ref                    | rows  | Extra                                  |
+----+-------------+--------+--------+------------------------------------------------------+-------------------------------+---------+------------------------+-------+----------------------------------------+
|  1 | SIMPLE      | thread | ref    | node_id_last_post_date,node_id_sticky_last_post_date | node_id_sticky_last_post_date | 5       | const,const            | 15744 | Parent of 2 pushed join@1; Using where |
|  1 | SIMPLE      | user   | eq_ref | PRIMARY                                              | PRIMARY                       | 4       | xenforo.thread.user_id |     1 | Child of 'thread' in pushed join@1     |
+----+-------------+--------+--------+------------------------------------------------------+-------------------------------+---------+------------------------+-------+----------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT thread.thread_id, thread.last_post_date FROM xf_thread AS thread LEFT JOIN xf_user AS user ON (user.user_id = thread.user_id) WHERE thread.node_id = 3 AND thread.sticky = 0 ORDER BY thread.last_post_date LIMIT 5;
+-----------+----------------+
| thread_id | last_post_date |
+-----------+----------------+
|        14 |     1078449911 |
|        46 |     1078645510 |
|        25 |     1078657182 |
|        48 |     1078859156 |
|       138 |     1079942731 |
+-----------+----------------+
5 rows in set (0.02 sec)

As InnoDB storage engine and telling it to not use indexes... same result (this is a smaller set of records just for testing)... still ordered incorrectly if there's a JOIN (even if JOIN isn't used for anything).

mysql> EXPLAIN SELECT thread.thread_id, thread.last_post_date FROM xf_thread_test AS thread IGNORE INDEX (node_id_last_post_date, node_id_sticky_last_post_date, last_post_date) LEFT JOIN xf_user AS user ON (user.user_id = thread.user_id) WHERE thread.node_id = 3 AND thread.sticky = 0 ORDER BY thread.last_post_date DESC LIMIT 5;
+----+-------------+--------+--------+---------------+---------+---------+------------------------+------+-----------------------------+
| id | select_type | table  | type   | possible_keys | key     | key_len | ref                    | rows | Extra                       |
+----+-------------+--------+--------+---------------+---------+---------+------------------------+------+-----------------------------+
|  1 | SIMPLE      | thread | ALL    | NULL          | NULL    | NULL    | NULL                   | 1000 | Using where; Using filesort |
|  1 | SIMPLE      | user   | eq_ref | PRIMARY       | PRIMARY | 4       | xenforo.thread.user_id |    1 | NULL                        |
+----+-------------+--------+--------+---------------+---------+---------+------------------------+------+-----------------------------+
2 rows in set (0.00 sec)

mysql> SELECT thread.thread_id, thread.last_post_date FROM xf_thread_test AS thread IGNORE INDEX (node_id_last_post_date, node_id_sticky_last_post_date, last_post_date) LEFT JOIN xf_user AS user ON (user.user_id = thread.user_id) WHERE thread.node_id = 3 AND thread.sticky = 0 ORDER BY thread.last_post_date LIMIT 5;
+-----------+----------------+
| thread_id | last_post_date |
+-----------+----------------+
|    289274 |     1175785856 |
|    992286 |     1219422669 |
|   1006611 |     1220259938 |
|   1100903 |     1226249528 |
|   1113477 |     1227032902 |
+-----------+----------------+
5 rows in set (0.00 sec)

Suggested fix:
Downgrade to MySQL 5.5.x

ORDER BY seems to work if there's no WHERE... so seems to be the combination of a JOIN, WHERE and ORDER BY yields the inability to sort properly.

Scratch that last part... seems to work properly with InnoDB/MyISAM.  Not sure how my test lost the "DESC" part there.

But definitely happening with ndbcluster... maybe an issue with pushed joins?

Also tested it with mysqld 5.5.30 connected to the 5.6.11-ndb-7.3.2 data nodes, and it orders properly from the 5.5.30 SQL node, so seems to be an issue with just the 5.6.11 SQL node...

Simplified examples... everything is as expected except last one... the combination of a JOIN, WHERE and ORDER BY DESC seems to ignore the "DESC" part of the query.

mysql> SELECT thread.thread_id, thread.last_post_date FROM xf_thread_test AS thread WHERE node_id = 3 ORDER BY thread.last_post_date DESC LIMIT 5;
+-----------+----------------+
| thread_id | last_post_date |
+-----------+----------------+
|   2635550 |     1360686904 |
|   1934005 |     1296262363 |
|   1113477 |     1227032902 |
|   1100903 |     1226249528 |
|   1006611 |     1220259938 |
+-----------+----------------+
5 rows in set (0.00 sec)

mysql> SELECT thread.thread_id, thread.last_post_date FROM xf_thread_test AS thread LEFT JOIN xf_user AS user ON (user.user_id = thread.user_id) ORDER BY thread.last_post_date DESC LIMIT 5;
+-----------+----------------+
| thread_id | last_post_date |
+-----------+----------------+
|   2657809 |     1371234899 |
|   2657898 |     1371144249 |
|   2660961 |     1371131474 |
|   2655210 |     1367561407 |
|   2651109 |     1366248662 |
+-----------+----------------+
5 rows in set (0.01 sec)

mysql> SELECT thread.thread_id, thread.last_post_date FROM xf_thread_test AS thread LEFT JOIN xf_user AS user ON (user.user_id = thread.user_id) WHERE node_id = 3 ORDER BY thread.last_post_date LIMIT 5;
+-----------+----------------+
| thread_id | last_post_date |
+-----------+----------------+
|    289274 |     1175785856 |
|    992286 |     1219422669 |
|   1006611 |     1220259938 |
|   1100903 |     1226249528 |
|   1113477 |     1227032902 |
+-----------+----------------+
5 rows in set (0.01 sec)

mysql> SELECT thread.thread_id, thread.last_post_date FROM xf_thread_test AS thread LEFT JOIN xf_user AS user ON (user.user_id = thread.user_id) WHERE node_id = 3 ORDER BY thread.last_post_date DESC LIMIT 5;
+-----------+----------------+
| thread_id | last_post_date |
+-----------+----------------+
|    289274 |     1175785856 |
|    992286 |     1219422669 |
|   1006611 |     1220259938 |
|   1100903 |     1226249528 |
|   1113477 |     1227032902 |
+-----------+----------------+
5 rows in set (0.01 sec)

Downgrade mysqld to 5.5.30, and the last query yields expected results:

mysql> SELECT thread.thread_id, thread.last_post_date FROM xf_thread_test AS thread LEFT JOIN xf_user AS user ON (user.user_id = thread.user_id) WHERE node_id = 3 ORDER BY thread.last_post_date DESC LIMIT 5;
+-----------+----------------+
| thread_id | last_post_date |
+-----------+----------------+
|   2635550 |     1360686904 |
|   1934005 |     1296262363 |
|   1113477 |     1227032902 |
|   1100903 |     1226249528 |
|   1006611 |     1220259938 |
+-----------+----------------+
5 rows in set (0.01 sec)

Thank you for the report.

I cannot repeat reported behavior with the dummy data, could you please provide repeatable test case(schema, data)?

Thanks,
Umesh

Here's a simple test case...

-----

CREATE TABLE IF NOT EXISTS `ndb_order_test` (
  `node_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `sort_number` int(10) unsigned NOT NULL,
  KEY `node_id` (`node_id`,`sort_number`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8;

INSERT INTO `ndb_order_test` (`node_id`, `user_id`, `sort_number`) VALUES
(68, 1, 1398029),
(68, 1, 549053);

CREATE TABLE IF NOT EXISTS `ndb_user_test` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=ndbcluster  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

INSERT INTO `ndb_user_test` (`user_id`, `name`) VALUES
(1, 'Shawn');

-----

When it uses the index (as it should), it can only order ASC.

mysql> EXPLAIN SELECT * FROM ndb_order_test LEFT JOIN ndb_user_test ON (ndb_user_test.user_id = ndb_order_test.user_id) WHERE node_id = 68 ORDER BY sort_number DESC;
+----+-------------+----------------+--------+---------------+---------+---------+-----------------------------+------+--------------------------------------------+
| id | select_type | table          | type   | possible_keys | key     | key_len | ref                         | rows | Extra                                      |
+----+-------------+----------------+--------+---------------+---------+---------+-----------------------------+------+--------------------------------------------+
|  1 | SIMPLE      | ndb_order_test | ref    | node_id       | node_id | 4       | const                       |    2 | Parent of 2 pushed join@1; Using where     |
|  1 | SIMPLE      | ndb_user_test  | eq_ref | PRIMARY       | PRIMARY | 4       | test.ndb_order_test.user_id |    1 | Child of 'ndb_order_test' in pushed join@1 |
+----+-------------+----------------+--------+---------------+---------+---------+-----------------------------+------+--------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM ndb_order_test LEFT JOIN ndb_user_test ON (ndb_user_test.user_id = ndb_order_test.user_id) WHERE node_id = 68 ORDER BY sort_number DESC;
+---------+---------+-------------+---------+-------+
| node_id | user_id | sort_number | user_id | name  |
+---------+---------+-------------+---------+-------+
|      68 |       1 |      549053 |       1 | Shawn |
|      68 |       1 |     1398029 |       1 | Shawn |
+---------+---------+-------------+---------+-------+
2 rows in set (0.00 sec)

If you use a 5.5.x mysqld (API node), it can order properly.

Hello SHAWN,

Thank you for the report.
Verified as described.

Thanks,
Umesh

/// 7.3.2

mysql> select version();
+----------------------------------------------+
| version()                                    |
+----------------------------------------------+
| 5.6.11-ndb-7.3.2-cluster-commercial-advanced |
+----------------------------------------------+
1 row in set (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS `ndb_order_test` (
    ->   `node_id` int(10) unsigned NOT NULL,
    ->   `user_id` int(10) unsigned NOT NULL,
    ->   `sort_number` int(10) unsigned NOT NULL,
    ->   KEY `node_id` (`node_id`,`sort_number`)
    -> ) ENGINE=ndbcluster DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (1.09 sec)

mysql> INSERT INTO `ndb_order_test` (`node_id`, `user_id`, `sort_number`) VALUES
    -> (68, 1, 1398029),
    -> (68, 1, 549053);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE IF NOT EXISTS `ndb_user_test` (
    ->   `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(20) NOT NULL,
    ->   PRIMARY KEY (`user_id`)
    -> ) ENGINE=ndbcluster  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
Query OK, 0 rows affected (1.00 sec)

mysql> INSERT INTO `ndb_user_test` (`user_id`, `name`) VALUES
    -> (1, 'Shawn');
Query OK, 1 row affected (0.00 sec)

mysql> EXPLAIN SELECT * FROM ndb_order_test LEFT JOIN ndb_user_test ON (ndb_user_test.user_id = ndb_order_test.user_id) WHERE node_id = 68 ORDER BY sort_number DESC;
+----+-------------+----------------+--------+---------------+---------+---------+-----------------------------+------+--------------------------------------------+
| id | select_type | table          | type   | possible_keys | key     | key_len | ref                         | rows | Extra                                      |
+----+-------------+----------------+--------+---------------+---------+---------+-----------------------------+------+--------------------------------------------+
|  1 | SIMPLE      | ndb_order_test | ref    | node_id       | node_id | 4       | const                       |    2 | Parent of 2 pushed join@1; Using where     |
|  1 | SIMPLE      | ndb_user_test  | eq_ref | PRIMARY       | PRIMARY | 4       | test.ndb_order_test.user_id |    1 | Child of 'ndb_order_test' in pushed join@1 |
+----+-------------+----------------+--------+---------------+---------+---------+-----------------------------+------+--------------------------------------------+
2 rows in set (0.03 sec)

mysql> SELECT * FROM ndb_order_test LEFT JOIN ndb_user_test ON (ndb_user_test.user_id = ndb_order_test.user_id) WHERE node_id = 68 ORDER BY sort_number DESC;
+---------+---------+-------------+---------+-------+
| node_id | user_id | sort_number | user_id | name  |
+---------+---------+-------------+---------+-------+
|      68 |       1 |      549053 |       1 | Shawn |
|      68 |       1 |     1398029 |       1 | Shawn |
+---------+---------+-------------+---------+-------+
2 rows in set (0.00 sec)

// 7.2.13

mysql> select version();
+-----------------------------------------------+
| version()                                     |
+-----------------------------------------------+
| 5.5.31-ndb-7.2.13-cluster-commercial-advanced |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `ndb_order_test` (
    ->   `node_id` int(10) unsigned NOT NULL,
    ->   `user_id` int(10) unsigned NOT NULL,
    ->   `sort_number` int(10) unsigned NOT NULL,
    ->   KEY `node_id` (`node_id`,`sort_number`)
    -> ) ENGINE=ndbcluster DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (1.12 sec)

mysql> INSERT INTO `ndb_order_test` (`node_id`, `user_id`, `sort_number`) VALUES
    -> (68, 1, 1398029),
    -> (68, 1, 549053);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE IF NOT EXISTS `ndb_user_test` (
    ->   `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(20) NOT NULL,
    ->   PRIMARY KEY (`user_id`)
    -> ) ENGINE=ndbcluster  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
Query OK, 0 rows affected (0.86 sec)

mysql> INSERT INTO `ndb_user_test` (`user_id`, `name`) VALUES
    -> (1, 'Shawn');
Query OK, 1 row affected (0.02 sec)

mysql> EXPLAIN SELECT * FROM ndb_order_test LEFT JOIN ndb_user_test ON (ndb_user_test.user_id = ndb_order_test.user_id) WHERE node_id = 68 ORDER BY sort_number DESC;
+----+-------------+----------------+--------+---------------+---------+---------+-----------------------------+------+--------------------------------------------+
| id | select_type | table          | type   | possible_keys | key     | key_len | ref                         | rows | Extra                                      |
+----+-------------+----------------+--------+---------------+---------+---------+-----------------------------+------+--------------------------------------------+
|  1 | SIMPLE      | ndb_order_test | ref    | node_id       | node_id | 4       | const                       |    2 | Parent of 2 pushed join@1; Using where     |
|  1 | SIMPLE      | ndb_user_test  | eq_ref | PRIMARY       | PRIMARY | 4       | test.ndb_order_test.user_id |    1 | Child of 'ndb_order_test' in pushed join@1 |
+----+-------------+----------------+--------+---------------+---------+---------+-----------------------------+------+--------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM ndb_order_test LEFT JOIN ndb_user_test ON (ndb_user_test.user_id = ndb_order_test.user_id) WHERE node_id = 68 ORDER BY sort_number DESC;
+---------+---------+-------------+---------+-------+
| node_id | user_id | sort_number | user_id | name  |
+---------+---------+-------------+---------+-------+
|      68 |       1 |     1398029 |       1 | Shawn |
|      68 |       1 |      549053 |       1 | Shawn |
+---------+---------+-------------+---------+-------+
2 rows in set (0.00 sec)

Fix documented in the NDB 7.3.3 changelog as follows:

        Results from joins using a WHERE with an ORDER BY clause were not
        sorted properly.

Closed.

Updated changelog entry per developer comments to make it clear what the problem with the sort actually was:

        Results from joins using a WHERE with an ORDER BY ... DESC clause 
        were not sorted properly; the DESC keyword in such cases was 
        effectively ignored.

Bug report status unchanged.