MySQL Bugs: #34044: UNION ALL query wrong result order

Bug #34044	UNION ALL query wrong result order
Submitted:	24 Jan 2008 21:22	Modified:	29 Feb 2008 17:49
Reporter:	Roman P	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	4.1.16, 4.1, 5.0, 5.1, 6.0 BK	OS:	Any (MS Windows SP2, Linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	distinct, order by, UNION ALL

Description:
Before sorry for my English. ORDER BY not work in this query:

(SELECT MAX(sequence) AS sequence FROM test_table) UNION ALL (SELECT DISTINCT sequence FROM test_table WHERE id IN (1,2,4) ORDER BY sequence)

Result is: [4, 4, 2, 1]
But right result is: [4, 1, 2, 4]
Right?

How to repeat:
CREATE TABLE `test_table` (
  `id` int(32) unsigned NOT NULL auto_increment,
  `sequence` int(32) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `sequence` (`sequence`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

INSERT INTO `test_table` VALUES (1, 1);
INSERT INTO `test_table` VALUES (2, 2);
INSERT INTO `test_table` VALUES (3, 3);
INSERT INTO `test_table` VALUES (4, 4);

ALTER TABLE `test_table` ORDER BY `id` DESC;

Thank you for the report.

Verified as described. All versions are affected.

This is the documented behaviour:
http://dev.mysql.com/doc/refman/5.0/en/union.html
> Use of ORDER BY for individual SELECT statements implies nothing
> about the order in which the rows appear in the final result
> because UNION by default produces an unordered set of rows. 

So the same doc-page is misleading, as it states just one pareagraph above:
> To apply ORDER BY or LIMIT to an individual SELECT, place the clause
> inside the parentheses that enclose the SELECT: 

"ORDER BY or LIMIT"
 *OR*, while only the combination "ORDER BY *and* LIMIT" makes sense to apply.

But what a sens of this if it work if ORDER BY replace to GROUP BY?

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 products.

I did not change *OR* to *AND* because the grammar does not require both. I revised this paragraph to make it clearer that it's usual to use them both:

However, use of ORDER BY for individual SELECT statements implies
nothing about the order in which the rows appear in the final result
because UNION by default produces an unordered set of rows.
Therefore, the use of ORDER BY in this context is typically in
conjunction with LIMIT, so that it is used to determine the subset of
the selected rows to retrieve for the SELECT, even though it does not
necessarily affect the order of those rows in the final UNION result.
If ORDER BY appears without LIMIT in a SELECT, it is optimized away
because it will have no effect anyway.