Cherry-picking patch for Bug#55980.

Original changeset:
------------------------------------------------------------
revno: 3197
revision-id: alik@sun.com-20100831135426-h5a4s2w6ih1d8q2x
parent: magnus.blaudd@sun.com-20100830120632-u3xzy002mdwueli8
committer: Alexander Nozdrin <alik@sun.com>
branch nick: mysql-5.5-bugfixing
timestamp: Tue 2010-08-31 17:54:26 +0400
message:
  Bug#55980 Character sets: supplementary character _bin ordering is wrong
  
  Problem:
  - ORDER BY for utf8mb4_bin, utf16_bin and utf32_bin returned
    results in a wrong order, because old functions
    (supporting only BMP range) were used to handle these collations.
  - Additionally, utf16_bin did not sort supplementary characters
    between U+D700 and U+E000, as WL#1213 specification specified.
------------------------------------------------------------

7 files changed, 94 insertions, 0 deletions

diff --git a/mysql-test/include/ctype_filesort2.inc b/mysql-test/include/ctype_filesort2.inc
new file mode 100644
index 00000000000..7b09eb482a5
--- /dev/null
+++ b/mysql-test/include/ctype_filesort2.inc
@@ -0,0 +1,16 @@
+#
+# Testing filesort for full Unicode character sets
+# with supplementary characters.
+#
+
+--echo #
+--echo # Bug#55980 Character sets: supplementary character _bin ordering is wrong
+--echo #
+CREATE TABLE t1 AS SELECT REPEAT('a',1) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES (_utf8mb4 0xEFBE9D),(_utf8mb4 0xF0908E84);
+INSERT INTO t1 VALUES (_utf8mb4 0xCE85),(_utf8mb4 0xF4808080);
+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;
diff --git a/mysql-test/r/ctype_utf16.result b/mysql-test/r/ctype_utf16.result
index c5fd7ef1439..7cee15aecef 100644
--- a/mysql-test/r/ctype_utf16.result
+++ b/mysql-test/r/ctype_utf16.result
@@ -611,6 +611,31 @@ utf16_bin	00610009
 utf16_bin	0061
 utf16_bin	00610020
 drop table t1;
+#
+# Bug#55980 Character sets: supplementary character _bin ordering is wrong
+#
+CREATE TABLE t1 AS SELECT REPEAT('a',1) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(1) CHARACTER SET utf16 COLLATE utf16_bin NOT NULL DEFAULT ''
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (_utf8mb4 0xEFBE9D),(_utf8mb4 0xF0908E84);
+INSERT INTO t1 VALUES (_utf8mb4 0xCE85),(_utf8mb4 0xF4808080);
+SELECT HEX(a), HEX(CONVERT(a USING utf8mb4)) FROM t1 ORDER BY a;
+HEX(a)	HEX(CONVERT(a USING utf8mb4))
+0385	CE85
+D800DF84	F0908E84
+DBC0DC00	F4808080
+FF9D	EFBE9D
+ALTER TABLE t1 ADD KEY(a);
+SELECT HEX(a), HEX(CONVERT(a USING utf8mb4)) FROM t1 ORDER BY a;
+HEX(a)	HEX(CONVERT(a USING utf8mb4))
+0385	CE85
+D800DF84	F0908E84
+DBC0DC00	F4808080
+FF9D	EFBE9D
+DROP TABLE IF EXISTS 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 79e714eab47..94a73093154 100644
--- a/mysql-test/r/ctype_utf32.result
+++ b/mysql-test/r/ctype_utf32.result
@@ -610,6 +610,31 @@ utf32_bin	0000006100000009
 utf32_bin	00000061
 utf32_bin	0000006100000020
 drop table t1;
+#
+# Bug#55980 Character sets: supplementary character _bin ordering is wrong
+#
+CREATE TABLE t1 AS SELECT REPEAT('a',1) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(1) CHARACTER SET utf32 COLLATE utf32_bin NOT NULL DEFAULT ''
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (_utf8mb4 0xEFBE9D),(_utf8mb4 0xF0908E84);
+INSERT INTO t1 VALUES (_utf8mb4 0xCE85),(_utf8mb4 0xF4808080);
+SELECT HEX(a), HEX(CONVERT(a USING utf8mb4)) FROM t1 ORDER BY a;
+HEX(a)	HEX(CONVERT(a USING utf8mb4))
+00000385	CE85
+0000FF9D	EFBE9D
+00010384	F0908E84
+00100000	F4808080
+ALTER TABLE t1 ADD KEY(a);
+SELECT HEX(a), HEX(CONVERT(a USING utf8mb4)) FROM t1 ORDER BY a;
+HEX(a)	HEX(CONVERT(a USING utf8mb4))
+00000385	CE85
+0000FF9D	EFBE9D
+00010384	F0908E84
+00100000	F4808080
+DROP TABLE IF EXISTS 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 454c9d4bfbb..3b9abbc5412 100644
--- a/mysql-test/r/ctype_utf8mb4.result
+++ b/mysql-test/r/ctype_utf8mb4.result
@@ -987,6 +987,31 @@ utf8mb4_bin	6109
 utf8mb4_bin	61
 utf8mb4_bin	6120
 drop table t1;
+#
+# Bug#55980 Character sets: supplementary character _bin ordering is wrong
+#
+CREATE TABLE t1 AS SELECT REPEAT('a',1) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT ''
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (_utf8mb4 0xEFBE9D),(_utf8mb4 0xF0908E84);
+INSERT INTO t1 VALUES (_utf8mb4 0xCE85),(_utf8mb4 0xF4808080);
+SELECT HEX(a), HEX(CONVERT(a USING utf8mb4)) FROM t1 ORDER BY a;
+HEX(a)	HEX(CONVERT(a USING utf8mb4))
+CE85	CE85
+EFBE9D	EFBE9D
+F0908E84	F0908E84
+F4808080	F4808080
+ALTER TABLE t1 ADD KEY(a);
+SELECT HEX(a), HEX(CONVERT(a USING utf8mb4)) FROM t1 ORDER BY a;
+HEX(a)	HEX(CONVERT(a USING utf8mb4))
+CE85	CE85
+EFBE9D	EFBE9D
+F0908E84	F0908E84
+F4808080	F4808080
+DROP TABLE IF EXISTS t1;
 select @@collation_connection;
 @@collation_connection
 utf8mb4_bin
diff --git a/mysql-test/t/ctype_utf16.test b/mysql-test/t/ctype_utf16.test
index e9c7e569250..ef705474eee 100644
--- a/mysql-test/t/ctype_utf16.test
+++ b/mysql-test/t/ctype_utf16.test
@@ -326,6 +326,7 @@ SET collation_connection='utf16_general_ci';
 SET NAMES latin1;
 SET collation_connection='utf16_bin';
 -- source include/ctype_filesort.inc
+-- source include/ctype_filesort2.inc
 -- source include/ctype_like_escape.inc
 
 #
diff --git a/mysql-test/t/ctype_utf32.test b/mysql-test/t/ctype_utf32.test
index 668b3b033bd..b113403944a 100644
--- a/mysql-test/t/ctype_utf32.test
+++ b/mysql-test/t/ctype_utf32.test
@@ -328,6 +328,7 @@ SET collation_connection='utf32_general_ci';
 SET NAMES latin1;
 SET collation_connection='utf32_bin';
 -- source include/ctype_filesort.inc
+-- source include/ctype_filesort2.inc
 -- source include/ctype_like_escape.inc
 
 #
diff --git a/mysql-test/t/ctype_utf8mb4.test b/mysql-test/t/ctype_utf8mb4.test
index 8fcba92ff47..03696f385b5 100644
--- a/mysql-test/t/ctype_utf8mb4.test
+++ b/mysql-test/t/ctype_utf8mb4.test
@@ -733,6 +733,7 @@ SET collation_connection='utf8mb4_general_ci';
 -- source include/ctype_german.inc
 SET collation_connection='utf8mb4_bin';
 -- source include/ctype_filesort.inc
+-- source include/ctype_filesort2.inc
 -- source include/ctype_like_escape.inc
 
 #