MySQL Bugs: #10032: Unexpected syntax error in UNION statement with ORDER BY

Bug #10032	Unexpected syntax error in UNION statement with ORDER BY
Submitted:	20 Apr 2005 17:04	Modified:	1 Jun 2005 5:15
Reporter:	Victoria Reznichenko
Status:	Closed
Category:	Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.12	OS:	Linux (linux)
Assigned to:	Sinisa Milivojevic	Target Version:

Description:
The following UNION statement doesn't work:

mysql> select id from t1 union select 99 order by 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'order by 1' at
line 1

though this one works fine:
mysql> select 99 union select id from t1 order by 1;
+------+
| 99   |
+------+
|    1 |
|   99 |
+------+
2 rows in set (0.12 sec)

If I add brackets around SELECTs it also works fine:

mysql> (select id from t1) union (select 99) order by 1;
+------+
| id   |
+------+
|    1 |
|   99 |
+------+
2 rows in set (0.00 sec)

How to repeat:
create table t1(id int);
insert into t1 values(1);
select id from t1 union select 99 order by 1;
select 99 union select id from t1 order by 1;
(select id from t1) union (select 99) order by 1;

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is well documented behaviour of UNION in MySQL

http://dev.mysql.com/doc/mysql/en/union.html
13.1.7.2. UNION Syntax

 If you want to use an ORDER BY or LIMIT clause to sort or limit the entire UNION result,
parenthesize the individual SELECT  statements and place the ORDER BY or LIMIT after the
last one. The following example uses both clauses:

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

Committing now ...

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/internals/24337

Noted in 4.1.12 changelog.