Fixing a bug in MDEV-8418 (utf16, utf16le) and MDEV-8417 (utf8mb4).

Fixing non-BMP characters to have the same weight, as it was before
MDEV-8418 and MDEV-8417.

8 files changed, 342 insertions, 132 deletions

diff --git a/mysql-test/include/ctype_utf8mb4.inc b/mysql-test/include/ctype_utf8mb4.inc
index a1b7d144c5d..152316e6158 100644
--- a/mysql-test/include/ctype_utf8mb4.inc
+++ b/mysql-test/include/ctype_utf8mb4.inc
@@ -1808,16 +1808,21 @@ DROP TABLE t1;
 --echo #
 --echo # MDEV-8417 utf8mb4: compare broken bytes as "greater than any non-broken character"
 --echo #
-CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8mb4, KEY(a));
-INSERT INTO t1 VALUES (0x61);
-INSERT INTO t1 VALUES (0xC280),(0xDFBF);
-INSERT INTO t1 VALUES (0xE0A080),(0xEFBFBF);
-INSERT INTO t1 VALUES (0xF0908080),(0xF48FBFBF);
-SELECT HEX(a) FROM t1 ORDER BY a;
-SELECT HEX(a) FROM t1 ORDER BY a DESC;
+CREATE TABLE t1 (
+  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+  a VARCHAR(10) CHARACTER SET utf8mb4, KEY(a,id)
+);
+INSERT INTO t1 (a) VALUES (0x61);
+INSERT INTO t1 (a) VALUES (0xC280),(0xDFBF);
+INSERT INTO t1 (a) VALUES (0xE0A080),(0xEFBFBF);
+INSERT INTO t1 (a) VALUES (0xF0908080),(0xF48FBFBF);
+SELECT id,HEX(a) FROM t1 ORDER BY a,id;
+SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC;
+SELECT COUNT(DISTINCT a) FROM t1;
 ALTER TABLE t1 MODIFY a VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
-SELECT HEX(a) FROM t1 ORDER BY a;
-SELECT HEX(a) FROM t1 ORDER BY a DESC;
+SELECT id,HEX(a) FROM t1 ORDER BY a;
+SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC;
+SELECT COUNT(DISTINCT a) FROM t1;
 DROP TABLE t1;
 
 --echo #
diff --git a/mysql-test/r/ctype_utf16.result b/mysql-test/r/ctype_utf16.result
index 2d91ce3dd6f..4d8f2d38a03 100644
--- a/mysql-test/r/ctype_utf16.result
+++ b/mysql-test/r/ctype_utf16.result
@@ -2127,3 +2127,64 @@ DEALLOCATE PREPARE stmt;
 #
 # End of 10.0 tests
 #
+#
+# Start of 10.1 tests
+#
+#
+# MDEV-8417 utf8mb4: compare broken bytes as "greater than any non-broken character"
+#
+CREATE TABLE t1 (
+id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+a VARCHAR(10) CHARACTER SET utf16, KEY(a,id)
+);
+INSERT INTO t1 (a) VALUES (_utf8mb4 0x61);
+INSERT INTO t1 (a) VALUES (_utf8mb4 0xC280),(_utf8mb4 0xDFBF);
+INSERT INTO t1 (a) VALUES (_utf8mb4 0xE0A080),(_utf8mb4 0xEFBFBF);
+INSERT INTO t1 (a) VALUES (_utf8mb4 0xF0908080),(_utf8mb4 0xF48FBFBF);
+SELECT id,HEX(a) FROM t1 ORDER BY a,id;
+id	HEX(a)
+1	0061
+2	0080
+3	07FF
+4	0800
+6	D800DC00
+7	DBFFDFFF
+5	FFFF
+SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC;
+id	HEX(a)
+5	FFFF
+7	DBFFDFFF
+6	D800DC00
+4	0800
+3	07FF
+2	0080
+1	0061
+SELECT COUNT(DISTINCT a) FROM t1;
+COUNT(DISTINCT a)
+6
+ALTER TABLE t1 MODIFY a VARCHAR(10) CHARACTER SET utf16 COLLATE utf16_bin;
+SELECT id,HEX(a) FROM t1 ORDER BY a;
+id	HEX(a)
+1	0061
+2	0080
+3	07FF
+4	0800
+5	FFFF
+6	D800DC00
+7	DBFFDFFF
+SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC;
+id	HEX(a)
+7	DBFFDFFF
+6	D800DC00
+5	FFFF
+4	0800
+3	07FF
+2	0080
+1	0061
+SELECT COUNT(DISTINCT a) FROM t1;
+COUNT(DISTINCT a)
+7
+DROP TABLE t1;
+#
+# End of 10.1 tests
+#
diff --git a/mysql-test/r/ctype_utf16le.result b/mysql-test/r/ctype_utf16le.result
index 8098b0d1666..c980743ce94 100644
--- a/mysql-test/r/ctype_utf16le.result
+++ b/mysql-test/r/ctype_utf16le.result
@@ -2319,3 +2319,64 @@ DFFFFFDFFFFF9CFFFF9DFFFF9EFFFF
 #
 # End of 5.6 tests
 #
+#
+# Start of 10.1 tests
+#
+#
+# MDEV-8417 utf8mb4: compare broken bytes as "greater than any non-broken character"
+#
+CREATE TABLE t1 (
+id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+a VARCHAR(10) CHARACTER SET utf16le, KEY(a,id)
+);
+INSERT INTO t1 (a) VALUES (_utf8mb4 0x61);
+INSERT INTO t1 (a) VALUES (_utf8mb4 0xC280),(_utf8mb4 0xDFBF);
+INSERT INTO t1 (a) VALUES (_utf8mb4 0xE0A080),(_utf8mb4 0xEFBFBF);
+INSERT INTO t1 (a) VALUES (_utf8mb4 0xF0908080),(_utf8mb4 0xF48FBFBF);
+SELECT id,HEX(a) FROM t1 ORDER BY a,id;
+id	HEX(a)
+1	6100
+2	8000
+3	FF07
+4	0008
+6	00D800DC
+7	FFDBFFDF
+5	FFFF
+SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC;
+id	HEX(a)
+5	FFFF
+7	FFDBFFDF
+6	00D800DC
+4	0008
+3	FF07
+2	8000
+1	6100
+SELECT COUNT(DISTINCT a) FROM t1;
+COUNT(DISTINCT a)
+6
+ALTER TABLE t1 MODIFY a VARCHAR(10) CHARACTER SET utf16le COLLATE utf16le_bin;
+SELECT id,HEX(a) FROM t1 ORDER BY a;
+id	HEX(a)
+1	6100
+2	8000
+3	FF07
+4	0008
+5	FFFF
+6	00D800DC
+7	FFDBFFDF
+SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC;
+id	HEX(a)
+7	FFDBFFDF
+6	00D800DC
+5	FFFF
+4	0008
+3	FF07
+2	8000
+1	6100
+SELECT COUNT(DISTINCT a) FROM t1;
+COUNT(DISTINCT a)
+7
+DROP TABLE t1;
+#
+# End of 10.1 tests
+#
diff --git a/mysql-test/r/ctype_utf8mb4_heap.result b/mysql-test/r/ctype_utf8mb4_heap.result
index 78cfe1da597..f60bf124a9f 100644
--- a/mysql-test/r/ctype_utf8mb4_heap.result
+++ b/mysql-test/r/ctype_utf8mb4_heap.result
@@ -2500,48 +2500,57 @@ DROP TABLE t1;
 #
 # MDEV-8417 utf8mb4: compare broken bytes as "greater than any non-broken character"
 #
-CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8mb4, KEY(a));
-INSERT INTO t1 VALUES (0x61);
-INSERT INTO t1 VALUES (0xC280),(0xDFBF);
-INSERT INTO t1 VALUES (0xE0A080),(0xEFBFBF);
-INSERT INTO t1 VALUES (0xF0908080),(0xF48FBFBF);
-SELECT HEX(a) FROM t1 ORDER BY a;
-HEX(a)
-61
-C280
-DFBF
-E0A080
-EFBFBF
-F0908080
-F48FBFBF
-SELECT HEX(a) FROM t1 ORDER BY a DESC;
-HEX(a)
-F48FBFBF
-F0908080
-EFBFBF
-E0A080
-DFBF
-C280
-61
+CREATE TABLE t1 (
+id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+a VARCHAR(10) CHARACTER SET utf8mb4, KEY(a,id)
+);
+INSERT INTO t1 (a) VALUES (0x61);
+INSERT INTO t1 (a) VALUES (0xC280),(0xDFBF);
+INSERT INTO t1 (a) VALUES (0xE0A080),(0xEFBFBF);
+INSERT INTO t1 (a) VALUES (0xF0908080),(0xF48FBFBF);
+SELECT id,HEX(a) FROM t1 ORDER BY a,id;
+id	HEX(a)
+1	61
+2	C280
+3	DFBF
+4	E0A080
+6	F0908080
+7	F48FBFBF
+5	EFBFBF
+SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC;
+id	HEX(a)
+5	EFBFBF
+7	F48FBFBF
+6	F0908080
+4	E0A080
+3	DFBF
+2	C280
+1	61
+SELECT COUNT(DISTINCT a) FROM t1;
+COUNT(DISTINCT a)
+6
 ALTER TABLE t1 MODIFY a VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
-SELECT HEX(a) FROM t1 ORDER BY a;
-HEX(a)
-61
-C280
-DFBF
-E0A080
-EFBFBF
-F0908080
-F48FBFBF
-SELECT HEX(a) FROM t1 ORDER BY a DESC;
-HEX(a)
-F48FBFBF
-F0908080
-EFBFBF
-E0A080
-DFBF
-C280
-61
+SELECT id,HEX(a) FROM t1 ORDER BY a;
+id	HEX(a)
+1	61
+2	C280
+3	DFBF
+4	E0A080
+5	EFBFBF
+6	F0908080
+7	F48FBFBF
+SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC;
+id	HEX(a)
+7	F48FBFBF
+6	F0908080
+5	EFBFBF
+4	E0A080
+3	DFBF
+2	C280
+1	61
+SELECT COUNT(DISTINCT a) FROM t1;
+COUNT(DISTINCT a)
+7
 DROP TABLE t1;
 #
 # ctype_utf8mb4.inc: End of 10.1 tests
diff --git a/mysql-test/r/ctype_utf8mb4_innodb.result b/mysql-test/r/ctype_utf8mb4_innodb.result
index 722c03bdff9..f904ff6f99e 100644
--- a/mysql-test/r/ctype_utf8mb4_innodb.result
+++ b/mysql-test/r/ctype_utf8mb4_innodb.result
@@ -2647,48 +2647,57 @@ DROP TABLE t1;
 #
 # MDEV-8417 utf8mb4: compare broken bytes as "greater than any non-broken character"
 #
-CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8mb4, KEY(a));
-INSERT INTO t1 VALUES (0x61);
-INSERT INTO t1 VALUES (0xC280),(0xDFBF);
-INSERT INTO t1 VALUES (0xE0A080),(0xEFBFBF);
-INSERT INTO t1 VALUES (0xF0908080),(0xF48FBFBF);
-SELECT HEX(a) FROM t1 ORDER BY a;
-HEX(a)
-61
-C280
-DFBF
-E0A080
-EFBFBF
-F0908080
-F48FBFBF
-SELECT HEX(a) FROM t1 ORDER BY a DESC;
-HEX(a)
-F48FBFBF
-F0908080
-EFBFBF
-E0A080
-DFBF
-C280
-61
+CREATE TABLE t1 (
+id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+a VARCHAR(10) CHARACTER SET utf8mb4, KEY(a,id)
+);
+INSERT INTO t1 (a) VALUES (0x61);
+INSERT INTO t1 (a) VALUES (0xC280),(0xDFBF);
+INSERT INTO t1 (a) VALUES (0xE0A080),(0xEFBFBF);
+INSERT INTO t1 (a) VALUES (0xF0908080),(0xF48FBFBF);
+SELECT id,HEX(a) FROM t1 ORDER BY a,id;
+id	HEX(a)
+1	61
+2	C280
+3	DFBF
+4	E0A080
+6	F0908080
+7	F48FBFBF
+5	EFBFBF
+SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC;
+id	HEX(a)
+5	EFBFBF
+7	F48FBFBF
+6	F0908080
+4	E0A080
+3	DFBF
+2	C280
+1	61
+SELECT COUNT(DISTINCT a) FROM t1;
+COUNT(DISTINCT a)
+6
 ALTER TABLE t1 MODIFY a VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
-SELECT HEX(a) FROM t1 ORDER BY a;
-HEX(a)
-61
-C280
-DFBF
-E0A080
-EFBFBF
-F0908080
-F48FBFBF
-SELECT HEX(a) FROM t1 ORDER BY a DESC;
-HEX(a)
-F48FBFBF
-F0908080
-EFBFBF
-E0A080
-DFBF
-C280
-61
+SELECT id,HEX(a) FROM t1 ORDER BY a;
+id	HEX(a)
+1	61
+2	C280
+3	DFBF
+4	E0A080
+5	EFBFBF
+6	F0908080
+7	F48FBFBF
+SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC;
+id	HEX(a)
+7	F48FBFBF
+6	F0908080
+5	EFBFBF
+4	E0A080
+3	DFBF
+2	C280
+1	61
+SELECT COUNT(DISTINCT a) FROM t1;
+COUNT(DISTINCT a)
+7
 DROP TABLE t1;
 #
 # ctype_utf8mb4.inc: End of 10.1 tests
diff --git a/mysql-test/r/ctype_utf8mb4_myisam.result b/mysql-test/r/ctype_utf8mb4_myisam.result
index f391f3fbba1..e44421d1410 100644
--- a/mysql-test/r/ctype_utf8mb4_myisam.result
+++ b/mysql-test/r/ctype_utf8mb4_myisam.result
@@ -2647,48 +2647,57 @@ DROP TABLE t1;
 #
 # MDEV-8417 utf8mb4: compare broken bytes as "greater than any non-broken character"
 #
-CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8mb4, KEY(a));
-INSERT INTO t1 VALUES (0x61);
-INSERT INTO t1 VALUES (0xC280),(0xDFBF);
-INSERT INTO t1 VALUES (0xE0A080),(0xEFBFBF);
-INSERT INTO t1 VALUES (0xF0908080),(0xF48FBFBF);
-SELECT HEX(a) FROM t1 ORDER BY a;
-HEX(a)
-61
-C280
-DFBF
-E0A080
-EFBFBF
-F0908080
-F48FBFBF
-SELECT HEX(a) FROM t1 ORDER BY a DESC;
-HEX(a)
-F48FBFBF
-F0908080
-EFBFBF
-E0A080
-DFBF
-C280
-61
+CREATE TABLE t1 (
+id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+a VARCHAR(10) CHARACTER SET utf8mb4, KEY(a,id)
+);
+INSERT INTO t1 (a) VALUES (0x61);
+INSERT INTO t1 (a) VALUES (0xC280),(0xDFBF);
+INSERT INTO t1 (a) VALUES (0xE0A080),(0xEFBFBF);
+INSERT INTO t1 (a) VALUES (0xF0908080),(0xF48FBFBF);
+SELECT id,HEX(a) FROM t1 ORDER BY a,id;
+id	HEX(a)
+1	61
+2	C280
+3	DFBF
+4	E0A080
+6	F0908080
+7	F48FBFBF
+5	EFBFBF
+SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC;
+id	HEX(a)
+5	EFBFBF
+7	F48FBFBF
+6	F0908080
+4	E0A080
+3	DFBF
+2	C280
+1	61
+SELECT COUNT(DISTINCT a) FROM t1;
+COUNT(DISTINCT a)
+6
 ALTER TABLE t1 MODIFY a VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
-SELECT HEX(a) FROM t1 ORDER BY a;
-HEX(a)
-61
-C280
-DFBF
-E0A080
-EFBFBF
-F0908080
-F48FBFBF
-SELECT HEX(a) FROM t1 ORDER BY a DESC;
-HEX(a)
-F48FBFBF
-F0908080
-EFBFBF
-E0A080
-DFBF
-C280
-61
+SELECT id,HEX(a) FROM t1 ORDER BY a;
+id	HEX(a)
+1	61
+2	C280
+3	DFBF
+4	E0A080
+5	EFBFBF
+6	F0908080
+7	F48FBFBF
+SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC;
+id	HEX(a)
+7	F48FBFBF
+6	F0908080
+5	EFBFBF
+4	E0A080
+3	DFBF
+2	C280
+1	61
+SELECT COUNT(DISTINCT a) FROM t1;
+COUNT(DISTINCT a)
+7
 DROP TABLE t1;
 #
 # ctype_utf8mb4.inc: End of 10.1 tests
diff --git a/mysql-test/t/ctype_utf16.test b/mysql-test/t/ctype_utf16.test
index e4305ed9879..8ea6ea67f1f 100644
--- a/mysql-test/t/ctype_utf16.test
+++ b/mysql-test/t/ctype_utf16.test
@@ -860,3 +860,31 @@ DEALLOCATE PREPARE stmt;
 --echo #
 --echo # End of 10.0 tests
 --echo #
+
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+--echo #
+--echo # MDEV-8417 utf8mb4: compare broken bytes as "greater than any non-broken character"
+--echo #
+CREATE TABLE t1 (
+  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+  a VARCHAR(10) CHARACTER SET utf16, KEY(a,id)
+);
+INSERT INTO t1 (a) VALUES (_utf8mb4 0x61);
+INSERT INTO t1 (a) VALUES (_utf8mb4 0xC280),(_utf8mb4 0xDFBF);
+INSERT INTO t1 (a) VALUES (_utf8mb4 0xE0A080),(_utf8mb4 0xEFBFBF);
+INSERT INTO t1 (a) VALUES (_utf8mb4 0xF0908080),(_utf8mb4 0xF48FBFBF);
+SELECT id,HEX(a) FROM t1 ORDER BY a,id;
+SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC;
+SELECT COUNT(DISTINCT a) FROM t1;
+ALTER TABLE t1 MODIFY a VARCHAR(10) CHARACTER SET utf16 COLLATE utf16_bin;
+SELECT id,HEX(a) FROM t1 ORDER BY a;
+SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC;
+SELECT COUNT(DISTINCT a) FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.1 tests
+--echo #
diff --git a/mysql-test/t/ctype_utf16le.test b/mysql-test/t/ctype_utf16le.test
index a8326900847..f5998fec18c 100644
--- a/mysql-test/t/ctype_utf16le.test
+++ b/mysql-test/t/ctype_utf16le.test
@@ -744,3 +744,31 @@ SET NAMES utf8, collation_connection=utf16le_bin;
 --echo #
 --echo # End of 5.6 tests
 --echo #
+
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+--echo #
+--echo # MDEV-8417 utf8mb4: compare broken bytes as "greater than any non-broken character"
+--echo #
+CREATE TABLE t1 (
+  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+  a VARCHAR(10) CHARACTER SET utf16le, KEY(a,id)
+);
+INSERT INTO t1 (a) VALUES (_utf8mb4 0x61);
+INSERT INTO t1 (a) VALUES (_utf8mb4 0xC280),(_utf8mb4 0xDFBF);
+INSERT INTO t1 (a) VALUES (_utf8mb4 0xE0A080),(_utf8mb4 0xEFBFBF);
+INSERT INTO t1 (a) VALUES (_utf8mb4 0xF0908080),(_utf8mb4 0xF48FBFBF);
+SELECT id,HEX(a) FROM t1 ORDER BY a,id;
+SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC;
+SELECT COUNT(DISTINCT a) FROM t1;
+ALTER TABLE t1 MODIFY a VARCHAR(10) CHARACTER SET utf16le COLLATE utf16le_bin;
+SELECT id,HEX(a) FROM t1 ORDER BY a;
+SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC;
+SELECT COUNT(DISTINCT a) FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.1 tests
+--echo #