Bug#16691598 - ORDER BY LOWER(COLUMN) PRODUCES OUT-OF-ORDER RESULTS

Problem:-
We have created a table with UTF8_BIN collation.
In case, when in our query we have ORDER BY clause over a function 
call we are getting result in incorrect order.
Note:the bug is not there in 5.5.

Analysis:
In 5.5, for UTF16_BIN, we have min and max multi-byte length is 2 and 4 
respectively.In make_sortkey(),for 2 byte character character we are 
assuming that the resultant length will be 2 byte/character. But when we 
use my_strnxfrm_unicode_full_bin(), we store sorting weights using 3 bytes 
per character.This result in truncated result.

Same thing happen for UTF8MB4, where we have 1 byte min multi-byte and 
4 byte max multi-byte.We will accsume resultant data as 1 byte/character, 
which result in truncated result.

Solution:-
use strnxfrm(means use of MY_CS_STRNXFRM macro) is used for sort, in 
which the resultant length is not dependent on source length.

4 files changed, 54 insertions, 0 deletions

diff --git a/mysql-test/include/ctype_filesort2.inc b/mysql-test/include/ctype_filesort2.inc
index 7b09eb482a5..7b576034136 100644
--- a/mysql-test/include/ctype_filesort2.inc
+++ b/mysql-test/include/ctype_filesort2.inc
@@ -14,3 +14,12 @@ SELECT HEX(a), HEX(CONVERT(a USING utf8mb4)) FROM t1 ORDER BY a;
 ALTER TABLE t1 ADD KEY(a);
 SELECT HEX(a), HEX(CONVERT(a USING utf8mb4)) FROM t1 ORDER BY a;
 DROP TABLE IF EXISTS t1;
+--echo #
+--echo # BUG#16691598 - ORDER BY LOWER(COLUMN) PRODUCES
+--echo #                OUT-OF-ORDER RESULTS
+--echo #
+CREATE TABLE  t1 SELECT ('a a') as n;
+INSERT INTO t1 VALUES('a b');
+SELECT * FROM t1 ORDER BY LOWER(n) ASC;
+SELECT * FROM t1 ORDER BY LOWER(n) DESC;
+DROP TABLE t1;
diff --git a/mysql-test/r/ctype_utf16.result b/mysql-test/r/ctype_utf16.result
index 42897ca580f..70b1ddde5c7 100644
--- a/mysql-test/r/ctype_utf16.result
+++ b/mysql-test/r/ctype_utf16.result
@@ -636,6 +636,21 @@ FF9D	EFBE9D
 D800DF84	F0908E84
 DBC0DC00	F4808080
 DROP TABLE IF EXISTS t1;
+#
+# BUG#16691598 - ORDER BY LOWER(COLUMN) PRODUCES
+#                OUT-OF-ORDER RESULTS
+#
+CREATE TABLE  t1 SELECT ('a a') as n;
+INSERT INTO t1 VALUES('a b');
+SELECT * FROM t1 ORDER BY LOWER(n) ASC;
+n
+a a
+a b
+SELECT * FROM t1 ORDER BY LOWER(n) DESC;
+n
+a b
+a a
+DROP TABLE t1;
 select @@collation_connection;
 @@collation_connection
 utf16_bin
diff --git a/mysql-test/r/ctype_utf32.result b/mysql-test/r/ctype_utf32.result
index 2433f2426a4..85e6220f5e8 100644
--- a/mysql-test/r/ctype_utf32.result
+++ b/mysql-test/r/ctype_utf32.result
@@ -635,6 +635,21 @@ HEX(a)	HEX(CONVERT(a USING utf8mb4))
 00010384	F0908E84
 00100000	F4808080
 DROP TABLE IF EXISTS t1;
+#
+# BUG#16691598 - ORDER BY LOWER(COLUMN) PRODUCES
+#                OUT-OF-ORDER RESULTS
+#
+CREATE TABLE  t1 SELECT ('a a') as n;
+INSERT INTO t1 VALUES('a b');
+SELECT * FROM t1 ORDER BY LOWER(n) ASC;
+n
+a a
+a b
+SELECT * FROM t1 ORDER BY LOWER(n) DESC;
+n
+a b
+a a
+DROP TABLE t1;
 select @@collation_connection;
 @@collation_connection
 utf32_bin
diff --git a/mysql-test/r/ctype_utf8mb4.result b/mysql-test/r/ctype_utf8mb4.result
index c7084134cd3..fa5abc5425f 100644
--- a/mysql-test/r/ctype_utf8mb4.result
+++ b/mysql-test/r/ctype_utf8mb4.result
@@ -1012,6 +1012,21 @@ EFBE9D	EFBE9D
 F0908E84	F0908E84
 F4808080	F4808080
 DROP TABLE IF EXISTS t1;
+#
+# BUG#16691598 - ORDER BY LOWER(COLUMN) PRODUCES
+#                OUT-OF-ORDER RESULTS
+#
+CREATE TABLE  t1 SELECT ('a a') as n;
+INSERT INTO t1 VALUES('a b');
+SELECT * FROM t1 ORDER BY LOWER(n) ASC;
+n
+a a
+a b
+SELECT * FROM t1 ORDER BY LOWER(n) DESC;
+n
+a b
+a a
+DROP TABLE t1;
 select @@collation_connection;
 @@collation_connection
 utf8mb4_bin