MySQL Bugs: #9642: Inner ORDER BY clause doesn't work for SELECT inside UNION

Bug #9642	Inner ORDER BY clause doesn't work for SELECT inside UNION
Submitted:	5 Apr 2005 11:53	Modified:	7 Apr 2005 16:59
Reporter:	Nico Gianniotis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S1 (Critical)
Version:	4.1.9-standard	OS:	Solaris (Solaris 8 (x86))
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Statements of the following form have a problem with ordering:

(SELECT ... )
UNION
(SELECT ... ORDER BY ...);

The result does not have the rows of the second SELECT ordered as specified. (In fact, it appears the ORDER BY column names are completely ignored; if an illegitimate column name is specified, no error results). If the second SELECT is executed by itself (i.e. outside the UNION) it works correctly.

If it is relevant, the tables I am seeing this problem on are all of type InnoDB.
mysql> show table status;
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+--------------------------------------------------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment                                          |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+--------------------------------------------------+
| test1  | InnoDB |       9 | Fixed      |    3 |           5461 |       16384 |            NULL |            0 |         0 |              4 | 2005-04-05 21:14:01 | NULL                | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 29696 kB                            |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+--------------------------------------------------+

How to repeat:
create table test1 (
   id bigint not null auto_increment,
   f1 bigint,
   f2 bigint,
   f3 bigint,
   f4 bigint,
   primary key (id)
);

insert into test1 values(0, 1, 1, 1, 1);
insert into test1 values(0, 2, 3, 4, 5);
insert into test1 values(0, 6, 7, 8, 9);

Tests:

mysql> SELECT * FROM test1 ORDER BY f3 DESC;
+----+------+------+------+------+
| id | f1   | f2   | f3   | f4   |
+----+------+------+------+------+
|  3 |    6 |    7 |    8 |    9 |
|  2 |    2 |    3 |    4 |    5 |
|  1 |    1 |    1 |    1 |    1 |
+----+------+------+------+------+
3 rows in set (0.00 sec)

[Above demonstrates correct behaviour for SELECT statement standalone]

mysql> (SELECT * FROM test1)
    -> UNION ALL
    -> (SELECT * FROM test1 ORDER BY f3 DESC);
+----+------+------+------+------+
| id | f1   | f2   | f3   | f4   |
+----+------+------+------+------+
|  1 |    1 |    1 |    1 |    1 |
|  2 |    2 |    3 |    4 |    5 |
|  3 |    6 |    7 |    8 |    9 |
|  1 |    1 |    1 |    1 |    1 |
|  2 |    2 |    3 |    4 |    5 |
|  3 |    6 |    7 |    8 |    9 |
+----+------+------+------+------+
6 rows in set (0.01 sec)

[Above demonstrates incorrect behaviour. Ordering of rows from second SELECT statement should be 8, 4, 1 under column f3. The second SELECT statement has not been sorted.]

mysql> (SELECT * FROM test1)
    -> UNION ALL
    -> (SELECT * FROM test1 ORDER BY foobar DESC);
+----+------+------+------+------+
| id | f1   | f2   | f3   | f4   |
+----+------+------+------+------+
|  1 |    1 |    1 |    1 |    1 |
|  2 |    2 |    3 |    4 |    5 |
|  3 |    6 |    7 |    8 |    9 |
|  1 |    1 |    1 |    1 |    1 |
|  2 |    2 |    3 |    4 |    5 |
|  3 |    6 |    7 |    8 |    9 |
+----+------+------+------+------+
6 rows in set (0.00 sec)

[Above demostrates that ORDER BY column names are actually being ignored; "foobar" is not a valid column of table test1, yet no error is returned. This may be a clue as to why sorting is not working - the column name is being ignored even in the case where it is valid]

Suggested fix:
No workaround discovered.

Forgot to add, problem also happens with UNION ALL type statements:

(SELECT ... )
UNION ALL
(SELECT ... ORDER BY ...)

mysql> (SELECT * FROM test1)
    -> UNION ALL
    ->  (SELECT * FROM test1 ORDER BY f3 DESC);
+----+------+------+------+------+
| id | f1   | f2   | f3   | f4   |
+----+------+------+------+------+
|  1 |    1 |    1 |    1 |    1 |
|  2 |    2 |    3 |    4 |    5 |
|  3 |    6 |    7 |    8 |    9 |
|  1 |    1 |    1 |    1 |    1 |
|  2 |    2 |    3 |    4 |    5 |
|  3 |    6 |    7 |    8 |    9 |
+----+------+------+------+------+
6 rows in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.11-debug-log |
+------------------+
1 row in set (0.01 sec)

mysql>  (SELECT * FROM test1)
    -> UNION ALL
    ->  (SELECT * FROM test1 ORDER BY f3 DESC);
+----+------+------+------+------+
| id | f1   | f2   | f3   | f4   |
+----+------+------+------+------+
|  1 |    1 |    1 |    1 |    1 |
|  2 |    2 |    3 |    4 |    5 |
|  3 |    6 |    7 |    8 |    9 |
|  3 |    6 |    7 |    8 |    9 |
|  2 |    2 |    3 |    4 |    5 |
|  1 |    1 |    1 |    1 |    1 |
+----+------+------+------+------+
6 rows in set (0.01 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.0.24-debug-log |
+------------------+
1 row in set (0.00 sec)

In the SQL standard, UNION operates on *tables* - on both sides on the UNION there is <simple table> which is <query specification> that "specifies a table derived from the result of a <table expression>" And table is *unordered* set or rows.

That's why ORDER BY inside a UNION is illegal in the SQL standard.

MySQL supports it, because it makes sense if LIMIT - another non-standard extension - is used.
But without LIMIT, ORDER BY is optimized away - because it does not change <table expression>

I understand. Thanks for clarifying this. I was a little misled by the current documentation, which says:

"To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

(SELECT a FROM tbl_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM tbl_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10);"

Perhaps a statement to the effect that the ORDER BY is only effective when used together with LIMIT would help make this behaviour more understandable.

Thanks again.

You're right, thanks for pointing this out!

(reopening this bugreport as the documentation issue)

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).