Bug #10032 Unexpected syntax error in UNION statement with ORDER BY
Submitted: 20 Apr 2005 15:04 Modified: 1 Jun 2005 3:15
Reporter: Victoria Reznichenko Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1.12 OS:Linux (linux)
Assigned to: CPU Architecture:Any

[20 Apr 2005 15:04] Victoria Reznichenko
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;
[21 Apr 2005 7:19] Alexander Keremidarski
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;
[26 Apr 2005 18:00] MySQL Verification Team
Committing now ...
[26 Apr 2005 18:13] 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/internals/24337
[1 Jun 2005 3:15] Paul DuBois
Noted in 4.1.12 changelog.