=== added file 'mysql-test/r/order_by_on_alias_with_collation.result'
--- old/mysql-test/r/order_by_on_alias_with_collation.result	1970-01-01 00:00:00 +0000
+++ new/mysql-test/r/order_by_on_alias_with_collation.result	2011-04-20 23:13:52 +0000
@@ -0,0 +1,107 @@
+drop table if exists Language, TextMaster, TextTranslation;
+CREATE TABLE Language (
+LanguageID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+LanguageCode CHAR(10) NOT NULL,
+LanguageName VARCHAR(40) NOT NULL,
+LanguageNameTextID INTEGER UNSIGNED NOT NULL,
+LatestChangeStamp TIMESTAMP NOT NULL,
+PRIMARY KEY (LanguageID),
+KEY LanguageCode (LanguageCode),
+KEY LanguageNameTextID (LanguageNameTextID),
+KEY LanguageName (LanguageName) )
+ENGINE=MyISAM
+DEFAULT CHARSET=utf8
+COLLATE=utf8_unicode_ci;
+INSERT Language SELECT 1, 'E', 'English', 1, null;
+INSERT Language SELECT 2, 'S', 'Spanish', 2, null;
+INSERT Language SELECT 3, 'ASL', 'American Sign Language', 3, null;
+INSERT Language SELECT 4, 'LSA', 'Argentinian Sign Language', 4, null;
+CREATE TABLE TextMaster (
+TextMasterID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+MasterText TEXT NOT NULL,
+LatestChangeStamp TIMESTAMP NOT NULL,
+PRIMARY KEY (TextMasterID) )
+ENGINE=MyISAM
+DEFAULT CHARSET=utf8
+COLLATE=utf8_unicode_ci;
+INSERT TextMaster SELECT 1, 'English', null;
+INSERT TextMaster SELECT 2, 'Spanish', null;
+INSERT TextMaster SELECT 3, 'American Sign Language', null;
+INSERT TextMaster SELECT 4, 'Argentinian Sign Language', null;
+CREATE TABLE TextTranslation (
+TextTranslationID INTEGER UNSIGNED AUTO_INCREMENT,
+TextMasterID INTEGER UNSIGNED NOT NULL,
+LanguageID INTEGER UNSIGNED NOT NULL,
+TranslatedText TEXT NOT NULL,
+LatestChangeStamp TIMESTAMP NOT NULL,
+PRIMARY KEY (TextTranslationID),
+KEY TextMasterID_LanguageID (TextMasterID, LanguageID) )
+ENGINE=MyISAM
+DEFAULT CHARSET=utf8
+COLLATE=utf8_unicode_ci;
+INSERT TextTranslation SELECT null, 1, 1, 'English', null;
+INSERT TextTranslation SELECT null, 2, 1, 'Spanish', null;
+INSERT TextTranslation SELECT null, 3, 1, 'American Sign Language', null;
+INSERT TextTranslation SELECT null, 4, 1, 'Argentinian Sign Language', null;
+INSERT TextTranslation SELECT null, 1, 2, 'Inglés', null;
+INSERT TextTranslation SELECT null, 2, 2, 'Español', null;
+INSERT TextTranslation SELECT null, 3, 2, 'Lenguaje de señas americano', null;
+INSERT TextTranslation SELECT null, 4, 2, 'Lengua de señas argentina', null;
+SELECT l.LanguageID,
+l.LanguageCode,
+l.LanguageNameTextID,
+COALESCE(t1.TranslatedText, tm1.MasterText) AS LanguageName,
+t1.TranslatedText,
+tm1.MasterText,
+l.LanguageName AS EnglishLanguageName
+FROM Language l
+INNER JOIN TextMaster tm1
+ON tm1.TextMasterID = l.LanguageNameTextID
+LEFT OUTER JOIN TextTranslation t1
+ON t1.TextMasterID = l.LanguageNameTextID
+AND t1.LanguageID = 2
+ORDER BY LanguageName;
+LanguageID	LanguageCode	LanguageNameTextID	LanguageName	TranslatedText	MasterText	EnglishLanguageName
+2	S	2	Español	Español	Spanish	Spanish
+1	E	1	Inglés	Inglés	English	English
+4	LSA	4	Lengua de señas argentina	Lengua de señas argentina	Argentinian Sign Language	Argentinian Sign Language
+3	ASL	3	Lenguaje de señas americano	Lenguaje de señas americano	American Sign Language	American Sign Language
+SELECT l.LanguageID,
+l.LanguageCode,
+l.LanguageNameTextID,
+COALESCE(t1.TranslatedText, tm1.MasterText) AS LanguageName,
+t1.TranslatedText,
+tm1.MasterText,
+l.LanguageName AS EnglishLanguageName
+FROM Language l
+INNER JOIN TextMaster tm1
+ON tm1.TextMasterID = l.LanguageNameTextID
+LEFT OUTER JOIN TextTranslation t1
+ON t1.TextMasterID = l.LanguageNameTextID
+AND t1.LanguageID = 2
+ORDER BY LanguageName COLLATE utf8_spanish_ci;
+LanguageID	LanguageCode	LanguageNameTextID	LanguageName	TranslatedText	MasterText	EnglishLanguageName
+2	S	2	Español	Español	Spanish	Spanish
+1	E	1	Inglés	Inglés	English	English
+4	LSA	4	Lengua de señas argentina	Lengua de señas argentina	Argentinian Sign Language	Argentinian Sign Language
+3	ASL	3	Lenguaje de señas americano	Lenguaje de señas americano	American Sign Language	American Sign Language
+SELECT l.LanguageID,
+l.LanguageCode,
+l.LanguageNameTextID,
+COALESCE(t1.TranslatedText, tm1.MasterText) AS CoalesceResult,
+t1.TranslatedText AS LanguageName,
+tm1.MasterText,
+l.LanguageName AS EnglishLanguageName
+FROM Language l
+INNER JOIN TextMaster tm1
+ON tm1.TextMasterID = l.LanguageNameTextID
+LEFT OUTER JOIN TextTranslation t1
+ON t1.TextMasterID = l.LanguageNameTextID
+AND t1.LanguageID = 2
+ORDER BY LanguageName COLLATE utf8_spanish_ci;
+LanguageID	LanguageCode	LanguageNameTextID	CoalesceResult	LanguageName	MasterText	EnglishLanguageName
+2	S	2	Español	Español	Spanish	Spanish
+1	E	1	Inglés	Inglés	English	English
+4	LSA	4	Lengua de señas argentina	Lengua de señas argentina	Argentinian Sign Language	Argentinian Sign Language
+3	ASL	3	Lenguaje de señas americano	Lenguaje de señas americano	American Sign Language	American Sign Language
+drop table if exists Language, TextMaster, TextTranslation;

=== added file 'mysql-test/t/order_by_on_alias_with_collation.test'
--- old/mysql-test/t/order_by_on_alias_with_collation.test	1970-01-01 00:00:00 +0000
+++ new/mysql-test/t/order_by_on_alias_with_collation.test	2011-04-20 23:13:52 +0000
@@ -0,0 +1,115 @@
+# BUG 59449 - When adding a collation to an order by clause for an alias (rather 
+#     than an actual field name) that also happened to have the same name as a field
+#     in one of the tables being queried, MySQL was ordering by the field in the table
+#     being queried, rather than the alias or what the alias represented.
+#
+#     For more information, see http://bugs.mysql.com/bug.php?id=59449
+
+--disable_warnings
+drop table if exists Language, TextMaster, TextTranslation;
+--enable_warnings
+
+CREATE TABLE Language (
+   LanguageID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+   LanguageCode CHAR(10) NOT NULL,
+   LanguageName VARCHAR(40) NOT NULL,
+   LanguageNameTextID INTEGER UNSIGNED NOT NULL,
+   LatestChangeStamp TIMESTAMP NOT NULL,
+   PRIMARY KEY (LanguageID),
+   KEY LanguageCode (LanguageCode),
+   KEY LanguageNameTextID (LanguageNameTextID),
+   KEY LanguageName (LanguageName) )
+ENGINE=MyISAM
+DEFAULT CHARSET=utf8
+COLLATE=utf8_unicode_ci;
+
+INSERT Language SELECT 1, 'E', 'English', 1, null;
+INSERT Language SELECT 2, 'S', 'Spanish', 2, null;
+INSERT Language SELECT 3, 'ASL', 'American Sign Language', 3, null;
+INSERT Language SELECT 4, 'LSA', 'Argentinian Sign Language', 4, null;
+
+CREATE TABLE TextMaster (
+   TextMasterID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+   MasterText TEXT NOT NULL,
+   LatestChangeStamp TIMESTAMP NOT NULL,
+   PRIMARY KEY (TextMasterID) )
+ENGINE=MyISAM
+DEFAULT CHARSET=utf8
+COLLATE=utf8_unicode_ci;
+
+INSERT TextMaster SELECT 1, 'English', null;
+INSERT TextMaster SELECT 2, 'Spanish', null;
+INSERT TextMaster SELECT 3, 'American Sign Language', null;
+INSERT TextMaster SELECT 4, 'Argentinian Sign Language', null;
+
+CREATE TABLE TextTranslation (
+   TextTranslationID INTEGER UNSIGNED AUTO_INCREMENT,
+   TextMasterID INTEGER UNSIGNED NOT NULL,
+   LanguageID INTEGER UNSIGNED NOT NULL,
+   TranslatedText TEXT NOT NULL,
+   LatestChangeStamp TIMESTAMP NOT NULL,
+   PRIMARY KEY (TextTranslationID),
+   KEY TextMasterID_LanguageID (TextMasterID, LanguageID) )
+ENGINE=MyISAM
+DEFAULT CHARSET=utf8
+COLLATE=utf8_unicode_ci;
+
+INSERT TextTranslation SELECT null, 1, 1, 'English', null;
+INSERT TextTranslation SELECT null, 2, 1, 'Spanish', null;
+INSERT TextTranslation SELECT null, 3, 1, 'American Sign Language', null;
+INSERT TextTranslation SELECT null, 4, 1, 'Argentinian Sign Language', null;
+INSERT TextTranslation SELECT null, 1, 2, 'Inglés', null;
+INSERT TextTranslation SELECT null, 2, 2, 'Español', null;
+INSERT TextTranslation SELECT null, 3, 2, 'Lenguaje de señas americano', null;
+INSERT TextTranslation SELECT null, 4, 2, 'Lengua de señas argentina', null;
+
+# works as expected - no collation - sorted by alias to a function result (alias has name matching a real field from a queried table) 
+SELECT l.LanguageID,
+       l.LanguageCode,
+       l.LanguageNameTextID,
+       COALESCE(t1.TranslatedText, tm1.MasterText) AS LanguageName,
+       t1.TranslatedText,
+       tm1.MasterText,
+       l.LanguageName AS EnglishLanguageName
+  FROM Language l
+ INNER JOIN TextMaster tm1
+    ON tm1.TextMasterID = l.LanguageNameTextID
+  LEFT OUTER JOIN TextTranslation t1
+    ON t1.TextMasterID = l.LanguageNameTextID
+   AND t1.LanguageID = 2
+ ORDER BY LanguageName;
+
+# broken - collated - sorted by alias to a function (coalesce) result (alias has name matching a real field from a queried table) 
+SELECT l.LanguageID,
+       l.LanguageCode,
+       l.LanguageNameTextID,
+       COALESCE(t1.TranslatedText, tm1.MasterText) AS LanguageName,
+       t1.TranslatedText,
+       tm1.MasterText,
+       l.LanguageName AS EnglishLanguageName
+  FROM Language l
+ INNER JOIN TextMaster tm1
+    ON tm1.TextMasterID = l.LanguageNameTextID
+  LEFT OUTER JOIN TextTranslation t1
+    ON t1.TextMasterID = l.LanguageNameTextID
+   AND t1.LanguageID = 2
+ ORDER BY LanguageName COLLATE utf8_spanish_ci;
+
+# broken - collated - sorted by alias to a column (alias has name matching a real field from a queried table) 
+SELECT l.LanguageID,
+       l.LanguageCode,
+       l.LanguageNameTextID,
+       COALESCE(t1.TranslatedText, tm1.MasterText) AS CoalesceResult,
+       t1.TranslatedText AS LanguageName,
+       tm1.MasterText,
+       l.LanguageName AS EnglishLanguageName
+  FROM Language l
+ INNER JOIN TextMaster tm1
+    ON tm1.TextMasterID = l.LanguageNameTextID
+  LEFT OUTER JOIN TextTranslation t1
+    ON t1.TextMasterID = l.LanguageNameTextID
+   AND t1.LanguageID = 2
+ ORDER BY LanguageName COLLATE utf8_spanish_ci;
+
+drop table if exists Language, TextMaster, TextTranslation;
+