MySQL Bugs: #59449: Inconsistent use of column alias In ORDER BY clause that specified collation

Bug #59449	Inconsistent use of column alias In ORDER BY clause that specified collation
Submitted:	12 Jan 2011 16:01	Modified:	22 Jan 2014 20:50
Reporter:	Brandon Liles	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.1.54, 5.0, 5.1, 5.6.2	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	Contribution, ORDER BY SORT COLLATE COLUMN ALIAS

Description:
MySQL allows you to use a column alias when specifying a sort order for a query.  Even if the column alias used is the same as the name of a real column in the query, MySQL will use the value of the column alias instead of the value of any column with that same name for the order by clause.  However, if it is necessary to specify the collation for the order by clause, MySQL will at times use a real column in the query for the order by clause instead of the column alias.

How to repeat:
The purpose of this query is to retrieve a list of languages with the language names listed in the language of the user (and sorted correctly for that language)

SELECT l.LanguageID,
       COALESCE(tl.TranslatedText, '') AS LanguageName,
       l.LatestChangeStamp
  FROM Language l
  LEFT OUTER JOIN TranslatedText tl
    ON tl.TranslatedTextID = l.LanguageNameTextID
   AND tl.LanguageID = 2
 ORDER BY LanguageName COLLATE utf8_spanish_ci;

Assuming Language contains a column named 'LanguageName', MySQL will order the results by that column whereas if the collate clause is not included, MySQL will order the results by the column alias.

Adding a union to the query seems to force the server to use the column alias rather than the base column name.

Thank you for the report.

I can not repeat described behavior. Additionally version 5.0.77 is old. Please try with current version 5.0.91 and if problem still exists send us output of SHOW CREATE TABLE of both tables and minimal dump problem is repeatable with.

I've now tested and found the same behavior with MySQL version 5.1.49.

here are the schema objects and data:

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;

Here are the queries:
SELECT l.LanguageID,
       l.LanguageCode,
       l.LanguageNameTextID,
       COALESCE(t1.TranslatedText, tm1.MasterText) AS LanguageName,
       l.LatestChangeStamp
  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
 WHERE l.LanguageName LIKE '%Sign Language%'
 ORDER BY LanguageName COLLATE utf8_spanish_ci;

SELECT l.LanguageID,
       l.LanguageCode,
       l.LanguageNameTextID,
       COALESCE(t1.TranslatedText, tm1.MasterText) AS LanguageName,
       l.LatestChangeStamp
  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
 WHERE l.LanguageName LIKE '%Sign Language%'
 ORDER BY LanguageName;

When the collate clause exists in the query, MySQL appears to order by the column Language.LanguageName.  When the clause is not in the query, MySQL orders by the expression COALESCE(t1.TranslatedText, tm1.MasterText)

I've now tested with 5.1.54... same thing.

I tried on Windows.

On 5.1.45 and 5.5.8 I experience same as Brandon.  The 2 queries return rows in different order. 

On 5.1.54 I get for both queries: 

Error Code : 11
Can't unlock file (Errcode: 11)

Should a separate bug report be opened for this?

Output in command line client on Windows:

..
mysql> SELECT l.LanguageID,
    ->       l.LanguageCode,
    ->       l.LanguageNameTextID,
    ->       COALESCE(t1.TranslatedText, tm1.MasterText) AS LanguageName,
    ->       l.LatestChangeStamp
    ->  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
    ->  WHERE l.LanguageName LIKE '%Sign Language%'
    ->  ORDER BY LanguageName COLLATE utf8_spanish_ci;
ERROR 11 (HY000): Can't unlock file (Errcode: 11)
mysql>
mysql> SELECT l.LanguageID,
    ->       l.LanguageCode,
    ->       l.LanguageNameTextID,
    ->       COALESCE(t1.TranslatedText, tm1.MasterText) AS LanguageName,
    ->       l.LatestChangeStamp
    ->  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
    ->  WHERE l.LanguageName LIKE '%Sign Language%'
    ->  ORDER BY LanguageName;
ERROR 11 (HY000): Can't unlock file (Errcode: 11)
mysql>

However if tables are created as InnoDB tables only the last SELECT returns the error:

mysql> SELECT l.LanguageID,
    ->       l.LanguageCode,
    ->       l.LanguageNameTextID,
    ->       COALESCE(t1.TranslatedText, tm1.MasterText) AS LanguageName,
    ->       l.LatestChangeStamp
    ->  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
    ->  WHERE l.LanguageName LIKE '%Sign Language%'
    ->  ORDER BY LanguageName COLLATE utf8_spanish_ci;
+------------+--------------+--------------------+------------------------------
+---------------------+
| LanguageID | LanguageCode | LanguageNameTextID | LanguageName
| LatestChangeStamp   |
+------------+--------------+--------------------+------------------------------
+---------------------+
|          3 | ASL          |                  3 | Lenguaje de se├▒as
americano  | 2011-01-21 17:00:31 |
|          4 | LSA          |                  4 | Lengua de se├▒as argentina
 | 2011-01-21 17:00:31 |
+------------+--------------+--------------------+------------------------------
+---------------------+
2 rows in set (0.00 sec)

mysql>
mysql> SELECT l.LanguageID,
    ->       l.LanguageCode,
    ->       l.LanguageNameTextID,
    ->       COALESCE(t1.TranslatedText, tm1.MasterText) AS LanguageName,
    ->       l.LatestChangeStamp
    ->  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
    ->  WHERE l.LanguageName LIKE '%Sign Language%'
    ->  ORDER BY LanguageName;
ERROR 11 (HY000): Can't unlock file (Errcode: 11)
mysql>

(all examples here with MySQL 5.1.54 64 bit server on Windows)

I posted another report about the "Error Code : 11 -- Can't unlock file" problem. 
http://bugs.mysql.com/bug.php?id=59663

Thank you for the feedback.

Verified as described.

For those who are researching this, I would like to add a bit more information.  When I started taking a preliminary look at it, I found that there are two broken states - not just one (although they likely both have the same fix).

The state Brandon describes is where your alias is referencing the result of a function (COALESCE in his example).  A second state is where your alias is really just a "simple" alias to a column.  Both are broken (when using collation on your order by alias).  These are both demonstrated in the three queries below (slightly modified versions of Brandon's original queries).  

/* 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;

Test case to replicate this bug in the test suite

Attachment: fix_mysql_59449_testcase.patch (application/octet-stream, text), 9.64 KiB.

I uploaded a test case (fix_mysql_59449_testcase.patch) that should be committed for this bug.  If it's committed before a fix is committed, this will need to be added to one of the "disabled" files, which I did not do in this patch.

Sorry for not using bzr send.  You can easily apply with the following commands:

# do from within your branch checkout (written against trunk), substitute correct path to patch:
patch -p1 -N < ~/fix_mysql_59449_testcase.patch 
bzr add mysql-test/r/order_by_on_alias_with_collation.result 
bzr add mysql-test/t/order_by_on_alias_with_collation.test

fixes four spelling typos I encountered while debugging this issue. can be applied same way that previous patch can be applied

Attachment: fix_mysql_59449_spelling_typos.patch (application/octet-stream, text), 1.91 KiB.

see next comment for description

Attachment: fix_mysql_59449_version_1.patch (application/octet-stream, text), 767 bytes.

I just attached fix_mysql_59449_version_1.patch as a preliminary attempt to fix the issue.  The bug seems to be in sql/sql_base.cc:find_item_in_list.  When collation is added to the order by clause, the item that is passed into find_item_in_list is of type Item::FUNC_ITEM and item->functype() is Item_func::COLLATE_FUNC.  When not adding collation in the order by clause, the type is Item::FIELD_ITEM, even where the field is really an alias to the result of the coalesce function.

In the attached patch, I have added another code branch to sql_base.cc:find_item_in_list to find items that are collation functions, and return that item.  It was previously only searching for direct field and reference items.

This patch fixes this issue, but breaks a few other collation functions.  I am attaching it to checkpoint my work and in hopes that perhaps it will be a useful start for someone experienced with this codebase to look into.  If not, I hope to have time in the next couple weeks to look at it some more.

"make clean && make && make test" runs fine.  However, running "cd mysql-test && ./mysql-test-run --force | tee ~/testing.log" shows that my patch results in breaking the following three tests:

main.ctype_collate sys_vars.collation_database_func sys_vars.collation_server_func

Please note that on my machine, the following three tests fail with or without the patch (in other words, they fail from a clean checkout and build):
binlog.binlog_bug23533 main.mysql main.mysql_upgrade

Fully working patch ready for review - see comment below

Attachment: mysql_bug_59449-WORKS-20110610-2055.patch (application/octet-stream, text), 1.60 KiB.

I just finished and attached a patch that works with the provided test case(s) as well as all other existing test cases.  Note that before submitting, I have run the following commands:

make clean && make && make test
# it succeeded

./mtr --force --parallel 4 --mem | tee /home/jrthomerson/testing.log
# this succeeded (see note below)

mysql-test-run (mtr) passed all tests except for one: binlog.binlog_bug23533.  However, this same test fails on my machine even with a clean checkout and build.  Running this test on a clean checkout and build has the same log output as running with my patch.  Log output for this single test can be found at http://pastebin.com/t4jK0E05

FINAL NOTE: There are three patches from this bug that I would like to see reviewed and committed into the project:
fix_mysql_59449_testcase.patch - this is a testcase that reproduces the bug in a clean tree and verifies my fix.  This patch is *only* the testcase (and expected results to add to the test suite), and does not modify any MySQL code

fix_mysql_59449_spelling_typos.patch - this is just some simple fixes to typos that appear in comments.  No code is changed with this patch.

mysql_bug_59449-WORKS-20110610-2055.patch - this is the (hopefully final) patch that fixes the problem.  

Please follow up with any questions and I will do whatever I can to help this patch get through all the reviews / tests and into the source tree.

Ping.  Just seeing if anyone has had an opportunity to look at this issue yet.  I'd like to see the three patches mentioned in the previous comment reviewed for inclusion.  If there's something I'm missing to make this happen, please let me know.  Thanks a bunch.

Just checking to see if there is any hope of is getting worked on.