Description:
When performing a UNION that ORDER BYs only the second SELECT clause, a LIMIT value is required inside the parenthesis in order for the ORDER BY to return the correct result.
The MySQL Manual for 13.2.7.2. UNION Syntax states "UNION is used to combine the result from a number of SELECT statements into one result set."
The Manual later states, "ORDER BY for individual SELECT statements within parentheses has an effect only when combined with LIMIT. Otherwise, the ORDER BY is optimized away." This restriction is not listed at the top of the Manual page in the paragraph beginning, " The SELECT statements are normal select statements, but with the following restrictions:..."
The only other explanation for this behavior that I can find is MySQL Bug: #11877, where Sinisa Milivojevic states, "Unfortunately ORDER BY within parentheses of a select node in UNION's is no longer supported as it is not ANSI SQL."
The statement "optimized away" is rather nonchalant for a technical manual addressing an illogical oddity. Fundamentally altering the behavior of a SELECT statement is a restriction. It is in fact a literal restriction: the developer must restrict the number of rows with LIMIT in order to achieve an ordered result for only one of the UNION's SELECT statements.
Further, I ask that this limitation be considered as a critical known bug that requires fixing. While it may not be ANSI SQL, it is a logical impairment that merely the presence or absence of the LIMIT keyword should affect the order of your result set. LIMIT should be unrelated to ORDER BY. It does not make any more sense that LIMIT should order rows than that ORDER BY should limit rows. Imagine if using ORDER BY automatically limited the result set to 10 rows in a UNION. This would prove quite an obstacle to developers who use MySQL; so to does the current connection between LIMIT and ORDER BY when used in a UNION. LIMIT should limit rows and ORDER BY should order rows, and that is that.
The current connection between the keywords begs the obvious question: What if the developer does not want to, or can not, limit his result? The Manual dismisses this topic with the blasé answer that his result set is "optimized away". Is this analogous to meaning the very fabric of logic merely vanishes in thin air, because a man wearing a suit wrote an illogical standard? Remember 'Donkey Kong' is alleged to originally have been named ‘Monkey Kong’, but was released as ‘Donkey Kong’ due to an illegibly faxed document.
When using a UNION to combine SELECT statements to return separately ordered results in succession (not merged) through one result set, SELECT statements are abstract entities, like functions: i.e. combine f(x) and g(x). If the behavior of an abstract entity changes merely because it is used as an abstraction, then it is not abstract at all. In the case where UNION is used to merge multiple SELECT results, the merge itself necessitates polygamous functionality. However, in the case of using UNION to combine multiple SELECT statements, altering the SELECTs' behavior is arbitrary and counterintuitive. By arbitrarily and illogically altering the abstraction's behavior, the SELECT statement is no longer an abstraction, and the integrity of computer science has been undermined, if not destroyed. In order for the practice of abstraction to maintain integrous, a computer scientist must have faith that the abstraction works as expected.
Knowingly allowing the UNION / ODER BY / LIMIT connection destroys this faith; upon performing any MySQL query, a developer is forced wonder if there may be any supported exception to abstraction that may be lurking within the keyword relationships, such as UNION, ORDER BY, AND LIMIT.
While there are workarounds for this instance, the behavior begs a question of integrity, and I enjoy working with MySQL too much to let it go unstated. It is with this argument that I implore you to mark as a critical bug the behavior whereby a LIMIT is required to order a single result set in a UNION.
How to repeat:
Removing LIMIT 1000 from this query returns an unordered result set.
(SELECT 0 AS value, '-- All Clients --' AS label)
UNION ALL
(SELECT
sys_id AS value,
name AS label
FROM
tbl_clients
ORDER BY
name
LIMIT 1000)
Suggested fix:
This query should always return -- All Clients -- as the first row, and the rest of the rows as an ordered set.
(SELECT 0 AS value, '-- All Clients --' AS label)
UNION ALL
(SELECT
sys_id AS value,
name AS label
FROM
tbl_clients
ORDER BY
name)
