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: | |
Category: | MySQL Server: Documentation | Severity: | S1 (Critical) |
Version: | 4.1.9-standard | OS: | Solaris (Solaris 8 (x86)) |
Assigned to: | Paul DuBois | CPU Architecture: | Any |
[5 Apr 2005 11:53]
Nico Gianniotis
[5 Apr 2005 11:57]
Nico Gianniotis
Forgot to add, problem also happens with UNION ALL type statements: (SELECT ... ) UNION ALL (SELECT ... ORDER BY ...)
[5 Apr 2005 17:55]
MySQL Verification Team
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)
[5 Apr 2005 19:34]
Sergei Golubchik
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>
[5 Apr 2005 22:14]
Nico Gianniotis
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.
[6 Apr 2005 18:01]
Sergei Golubchik
You're right, thanks for pointing this out! (reopening this bugreport as the documentation issue)
[7 Apr 2005 16:59]
Paul DuBois
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).