Bug #6728 Incorrect sort order with UNION

• Submitted: 20 Nov 2004 0:04
• Modified: 18 Jan 2005 17:09
• Reporter: Daniel Grace
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.7-standard
• OS: Linux (Linux)
• CPU Architecture: Any

[20 Nov 2004 0:04] Daniel Grace

Description:
This might be related to bug #6699 (which has to deal with MERGE tables), but since I don't know how MySQL internally handles merge tables I can't say for sure.  (It would seem logical to me that MERGE tables are internally handled as unions, so this might be the underlying problem.)

When using multiple SELECT statements in a UNION, each with its own ORDER BY clause, the end results are not sorted properly:

How to repeat:
CREATE TABLE buggy (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	v1 CHAR(16) NOT NULL,
	v2 CHAR(16) NOT NULL,
	union_fodder INT NOT NULL,

	PRIMARY KEY(id),
	KEY(v1, v2),
	KEY(v2, v1)
);

INSERT INTO buggy (v1, v2) VALUES 
	("Red", "Blue"),
	("Green", "Yellow"),
	("Orange", "White"),
	("Black", "Grey"),
	("Silver", "Magenta"),
	("Pink", "Purple"),
	("Brown", "Crimson")
;

UPDATE buggy SET union_fodder=1 LIMIT 3;

SELECT * FROM buggy ORDER BY v2, v1; 

/* Returns rows sorted by v2 first, v1 second
id 	v1 	v2 	union_fodder
1 	Red 	Blue 	1
7 	Brown 	Crimson 	0
4 	Black 	Grey 	0
5 	Silver 	Magenta 	0
6 	Pink 	Purple 	0
3 	Orange 	White 	1
2 	Green 	Yellow 	1
*/

(SELECT * FROM buggy WHERE union_fodder=0 ORDER BY v2, v1) UNION ALL (SELECT * FROM buggy WHERE union_fodder=1 ORDER BY v2, v1);

 /* Sorts by incorrect key (in this case, primary) within each query.  I've had another test case sort by a different key entirely that was NOT the primary key.

id 	v1 	v2 	union_fodder
4 	Black 	Grey 	0
5 	Silver 	Magenta 	0
6 	Pink 	Purple 	0
7 	Brown 	Crimson 	0
1 	Red 	Blue 	1
2 	Green 	Yellow 	1
3 	Orange 	White 	1
*/

DROP TABLE buggy;

[29 Dec 2004 18:14] Danny Grasse

I have just downloaded 4.1.8 for Windows XP and am experiencing the same behavior.  The hack I've introduced to my sql code to get the 'order by' clause working is to add a 'limit' clause.  The caveat, of course, is to make sure the limit value is greater than the number of returned recorder.

[17 Jan 2005 18:33] MySQL Verification Team

It has been decided that this behaviour will remain, as changes have been done to mix
freely select nodes with and without braces, leading to this behaviour as one of the consequence.

[18 Jan 2005 7:22] Daniel Grace

Even though the how to reproduce code posted disregards ALL of the order by clauses?

[18 Jan 2005 17:09] MySQL Verification Team

Exactly ...

Separate sorting is no longer supported.

This will be updated in the manual soon, if not done already.