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
Triage: Needs Triage: D2 (Serious)

[20 Jun 2013 18:53] SHAWN HOGAN
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
[20 Jun 2013 18:55] SHAWN HOGAN
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.
[20 Jun 2013 19:01] SHAWN HOGAN
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...
[20 Jun 2013 20:27] SHAWN HOGAN
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)
[21 Jun 2013 15:42] Umesh Shastry
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
[21 Jun 2013 18:41] SHAWN HOGAN
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.
[22 Jun 2013 12:54] Umesh Shastry
Hello SHAWN,

Thank you for the report.
Verified as described.

Thanks,
Umesh
[22 Jun 2013 12:55] Umesh Shastry
/// 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)
[16 Oct 2013 15:02] Jon Stephens
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.
[17 Oct 2013 10:25] Jon Stephens
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.