Bug #18767 Single resultset UNION doesn't sort correctly
Submitted: 4 Apr 2006 8:21 Modified: 4 May 2006 16:21
Reporter: Tobias Asplund Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.1.19/5.0.19/5.1.7 OS:Windows (Windows/FreeBSD)
Assigned to: Igor Babaev CPU Architecture:Any

[4 Apr 2006 8:21] Tobias Asplund 
Description:
eeyore> DROP TABLE IF EXISTS bugtest;
Query OK, 0 rows affected, 1 warning (0.00 sec)

eeyore> CREATE TABLE bugtest ( a INT );
Query OK, 0 rows affected (0.09 sec)

eeyore> INSERT INTO bugtest VALUES (1), (3), (2);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

eeyore> SELECT a FROM bugtest ORDER BY a;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

eeyore> (SELECT a FROM bugtest) ORDER BY a;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

eeyore> (SELECT a FROM bugtest ORDER BY a DESC) ORDER BY a;
+------+
| a    |
+------+
|    3 |
|    2 |
|    1 |
+------+
3 rows in set (0.00 sec)

I would expect 1,2,3 in ASC order, since ORDER BY a at the end doesn't have DESC specified.

How to repeat:
DROP TABLE IF EXISTS bugtest;

CREATE TABLE bugtest ( a INT );

INSERT INTO bugtest VALUES (1), (3), (2);

SELECT a FROM bugtest ORDER BY a;

(SELECT a FROM bugtest) ORDER BY a;

(SELECT a FROM bugtest ORDER BY a DESC) ORDER BY a;
[4 Apr 2006 8:22] Tobias Asplund 
eeyore> (SELECT a FROM bugtest ORDER BY a DESC) UNION (SELECT 4) ORDER BY a;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

Adding another resultset makes it work like intended, however.
[4 Apr 2006 10:30] Hartmut Holzgraefe 
test case

Attachment: bug18767.tar.gz (application/x-gzip, text), 667 bytes.
[6 Apr 2006 14:04] Tobias Asplund 
Played around with this some more, seems like there's more to this bug:

eeyore> (SELECT a FROM bugtest ORDER BY a DESC LIMIT 2) ORDER BY a;
+------+
| a    |
+------+
|    3 |
|    2 |
|    1 |
+------+
3 rows in set (0.00 sec)

eeyore> (SELECT a FROM bugtest ORDER BY a DESC LIMIT 2) UNION ALL (SELECT 4) ORDER BY a;
+------+
| a    |
+------+
|    2 |
|    3 |
|    4 |
+------+
3 rows in set (0.01 sec)
[12 Apr 2006 4:47] Igor Babaev 
This bug is present in 4.1 as well.

More striking examples can be demonstrated for this bug:

mysql> CREATE TABLE t1 (a int, b int);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (1,30), (1,20), (1,10), (2,30), (2,20), (2,10);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

For the following query the second ORDER BY list was completely ignored.

mysql> (SELECT * FROM t1 ORDER BY a,b) ORDER BY b,a;
+------+------+
| a    | b    |
+------+------+
|    1 |   10 |
|    1 |   20 |
|    1 |   30 |
|    2 |   10 |
|    2 |   20 |
|    2 |   30 |
+------+------+
6 rows in set (0.00 sec)

For this query we have ordering by concatenation of two lists from the ORDER BY clauses.

mysql> (SELECT b,a FROM t1 ORDER BY b) ORDER BY a;
+------+------+
| b    | a    |
+------+------+
|   10 |    1 |
|   10 |    2 |
|   20 |    1 |
|   20 |    2 |
|   30 |    1 |
|   30 |    2 |
+------+------+
6 rows in set (0.00 sec)
[15 Apr 2006 23:28] Igor Babaev 
Another related problem has been discovered by Sergey Petrunia: 
for queries of the form
   (SELECT * FROM ... LIMIT n) ORDER BY order_list
LIMIT values are completely ignored.

Here are some examples for the tables from the previous comment.
 
 mysql> (SELECT b,a FROM t1 ORDER by b LIMIT 3) ORDER by a;
+------+------+
| b    | a    |
+------+------+
|   10 |    1 |
|   10 |    2 |
|   20 |    1 |
|   20 |    2 |
|   30 |    1 |
|   30 |    2 |
+------+------+
6 rows in set (0.00 sec)

mysql> (SELECT a,b FROM t1 LIMIT 2) ORDER by a,b;
+------+------+
| a    | b    |
+------+------+
|    1 |   10 |
|    1 |   20 |
|    1 |   30 |
|    2 |   10 |
|    2 |   20 |
|    2 |   30 |
+------+------+
6 rows in set (0.00 sec)
[15 Apr 2006 23:28] Igor Babaev 
Another related problem has been discovered by Sergey Petrunia: 
for queries of the form
   (SELECT * FROM ... LIMIT n) ORDER BY order_list
LIMIT values are completely ignored.

Here are some examples for the tables from the previous comment.
 
 mysql> (SELECT b,a FROM t1 ORDER by b LIMIT 3) ORDER by a;
+------+------+
| b    | a    |
+------+------+
|   10 |    1 |
|   10 |    2 |
|   20 |    1 |
|   20 |    2 |
|   30 |    1 |
|   30 |    2 |
+------+------+
6 rows in set (0.00 sec)

mysql> (SELECT a,b FROM t1 LIMIT 2) ORDER by a,b;
+------+------+
| a    | b    |
+------+------+
|    1 |   10 |
|    1 |   20 |
|    1 |   30 |
|    2 |   10 |
|    2 |   20 |
|    2 |   30 |
+------+------+
6 rows in set (0.00 sec)
[17 Apr 2006 9:58] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5006
[21 Apr 2006 5:16] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5250
[30 Apr 2006 5:32] Igor Babaev 
ChangeSet
  1.2462 06/04/17 02:58:13 igor@rurik.mysql.com +7 -0
  Fixed bug #18767.
  The bug caused wrong result sets for union constructs of the form
  (SELECT ... ORDER BY order_list1 [LIMIT n]) ORDER BY order_list2.
  For such queries order lists were concatenated and limit clause was
  completely neglected. 

This fix will appear in 4.1.19, 5.0.21 and 5.1.11
[4 May 2006 16:21] Paul DuBois 
Noted in 4.1.19, 5.0.21, 5.1.10 changelogs.