Merge branch '10.1' into 10.2

66 files changed, 6256 insertions, 74 deletions

diff --git a/mysql-test/r/alter_table_online.result b/mysql-test/r/alter_table_online.result
index 05d03aeae11..e3f285437a7 100644
--- a/mysql-test/r/alter_table_online.result
+++ b/mysql-test/r/alter_table_online.result
@@ -1,4 +1,3 @@
-drop table if exists t1,t2,t3;
 create table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b'));
 insert into t1 (a) values (1),(2),(3);
 alter online table t1 modify b int default 5;
@@ -62,6 +61,18 @@ create table t3 (a int not null primary key, b int, c varchar(80)) engine=merge
 alter online table t3 union=(t1,t2);
 ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE.
 drop table t1,t2,t3;
+create table t1 (i int) partition by hash(i) partitions 2;
+alter online table t1 comment 'test';
+drop table t1;
+create table t1 (a int);
+alter online table t1 modify a int comment 'test';
+drop table t1;
+create table t1 (a int) engine=innodb;
+alter online table t1 modify a int comment 'test';
+drop table t1;
+create table t1 (a int) partition by hash(a) partitions 2;
+alter online table t1 modify a int comment 'test';
+drop table t1;
 #
 # MDEV-8948 ALTER ... INPLACE does work for BINARY, BLOB
 #
diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result
index fd613525aa1..2c8b0b484ae 100644
--- a/mysql-test/r/bigint.result
+++ b/mysql-test/r/bigint.result
@@ -502,3 +502,9 @@ a
 SELECT * FROM t1 WHERE a IN (0.8,0.9);
 a
 DROP TABLE t1;
+#
+# MDEV-9372 select 100 between 1 and 9223372036854775808 returns false
+#
+SELECT 100 BETWEEN 1 AND 9223372036854775808;
+100 BETWEEN 1 AND 9223372036854775808
+1
diff --git a/mysql-test/r/cache_temporal_4265.result b/mysql-test/r/cache_temporal_4265.result
index b8f13e465de..980bb957e19 100644
--- a/mysql-test/r/cache_temporal_4265.result
+++ b/mysql-test/r/cache_temporal_4265.result
@@ -8,4 +8,17 @@ a
 Warnings:
 Note	1003	2000-01-01
 Note	1003	2000-01-06
+set debug_dbug='';
+drop table t1;
+create table t1 (id int not null, ut timestamp(6) not null);
+insert into t1 values(1, '2001-01-01 00:00:00.2');
+insert into t1 values(1, '2001-01-01 00:00:00.1');
+select * from t1;
+id	ut
+1	2001-01-01 00:00:00.200000
+1	2001-01-01 00:00:00.100000
+select (select max(m2.ut) from t1 m2 where m1.id <> 0) from t1 m1;
+(select max(m2.ut) from t1 m2 where m1.id <> 0)
+2001-01-01 00:00:00.200000
+2001-01-01 00:00:00.200000
 drop table t1;
diff --git a/mysql-test/r/case.result b/mysql-test/r/case.result
index b1e928c5e30..9ceb7efde64 100644
--- a/mysql-test/r/case.result
+++ b/mysql-test/r/case.result
@@ -232,6 +232,19 @@ case t1.f1 when '00:00:00' then 1 end
 NULL
 drop table t1;
 #
+# MDEV-9745 Crash with CASE WHEN TRUE THEN COALESCE(CAST(NULL AS UNSIGNED)) ELSE 4 END
+#
+CREATE TABLE t1 SELECT CASE WHEN TRUE THEN COALESCE(CAST(NULL AS UNSIGNED)) ELSE 4 END AS a;
+DESCRIBE t1;
+Field	Type	Null	Key	Default	Extra
+a	decimal(1,0)	YES		NULL	
+DROP TABLE t1;
+CREATE TABLE t1 SELECT CASE WHEN TRUE THEN COALESCE(CAST(NULL AS UNSIGNED)) ELSE 40 END AS a;
+DESCRIBE t1;
+Field	Type	Null	Key	Default	Extra
+a	decimal(2,0)	YES		NULL	
+DROP TABLE t1;
+#
 # Start of 10.1 test
 #
 #
diff --git a/mysql-test/r/contributors.result b/mysql-test/r/contributors.result
index 1820c0a5f06..1e01ca81990 100644
--- a/mysql-test/r/contributors.result
+++ b/mysql-test/r/contributors.result
@@ -7,7 +7,7 @@ Visma	http://visma.com	Member of the MariaDB Foundation
 Nexedi	http://www.nexedi.com	Member of the MariaDB Foundation
 Acronis	http://www.acronis.com	Member of the MariaDB Foundation
 Verkkokauppa.com	Finland	Sponsor of the MariaDB Foundation
-Webyog	Bangalore	Sponsor of the MariaDB Foundation
+Virtuozzo	https://virtuozzo.com/	Sponsor of the MariaDB Foundation
 Google	USA	Sponsoring encryption, parallel replication and GTID
 Facebook	USA	Sponsoring non-blocking API, LIMIT ROWS EXAMINED etc
 Ronald Bradford	Brisbane, Australia	EFF contribution for UC2006 Auction
diff --git a/mysql-test/r/create_or_replace.result b/mysql-test/r/create_or_replace.result
index dc3d00aacc8..3f768535bdf 100644
--- a/mysql-test/r/create_or_replace.result
+++ b/mysql-test/r/create_or_replace.result
@@ -262,11 +262,11 @@ create table mysqltest2.t2 like test.t1;
 lock table test.t1 write, mysqltest2.t2 write;
 select * from information_schema.metadata_lock_info;
 THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
-#	MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT	Global read lock		
-#	MDL_SHARED_NO_READ_WRITE	MDL_EXPLICIT	Table metadata lock	test	t1
-#	MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT	Schema metadata lock	mysqltest2	
-#	MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT	Schema metadata lock	test	
-#	MDL_SHARED_NO_READ_WRITE	MDL_EXPLICIT	Table metadata lock	mysqltest2	t2
+#	MDL_INTENTION_EXCLUSIVE	NULL	Global read lock		
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	mysqltest2	
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	test	
+#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	mysqltest2	t2
+#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	test	t1
 create or replace table test.t1;
 ERROR 42000: A table must have at least 1 column
 show tables;
@@ -274,10 +274,10 @@ Tables_in_test
 t2
 select * from information_schema.metadata_lock_info;
 THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
-#	MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT	Global read lock		
-#	MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT	Schema metadata lock	mysqltest2	
-#	MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT	Schema metadata lock	test	
-#	MDL_SHARED_NO_READ_WRITE	MDL_EXPLICIT	Table metadata lock	mysqltest2	t2
+#	MDL_INTENTION_EXCLUSIVE	NULL	Global read lock		
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	mysqltest2	
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	test	
+#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	mysqltest2	t2
 create or replace table mysqltest2.t2;
 ERROR 42000: A table must have at least 1 column
 select * from information_schema.metadata_lock_info;
@@ -289,11 +289,11 @@ create table mysqltest2.t2 like test.t1;
 lock table test.t1 write, mysqltest2.t2 write;
 select * from information_schema.metadata_lock_info;
 THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
-#	MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT	Global read lock		
-#	MDL_SHARED_NO_READ_WRITE	MDL_EXPLICIT	Table metadata lock	test	t1
-#	MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT	Schema metadata lock	mysqltest2	
-#	MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT	Schema metadata lock	test	
-#	MDL_SHARED_NO_READ_WRITE	MDL_EXPLICIT	Table metadata lock	mysqltest2	t2
+#	MDL_INTENTION_EXCLUSIVE	NULL	Global read lock		
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	mysqltest2	
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	test	
+#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	mysqltest2	t2
+#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	test	t1
 create or replace table test.t1 (a int) select 1 as 'a', 2 as 'a';
 ERROR 42S21: Duplicate column name 'a'
 show tables;
@@ -301,10 +301,10 @@ Tables_in_test
 t2
 select * from information_schema.metadata_lock_info;
 THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
-#	MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT	Global read lock		
-#	MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT	Schema metadata lock	mysqltest2	
-#	MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT	Schema metadata lock	test	
-#	MDL_SHARED_NO_READ_WRITE	MDL_EXPLICIT	Table metadata lock	mysqltest2	t2
+#	MDL_INTENTION_EXCLUSIVE	NULL	Global read lock		
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	mysqltest2	
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	test	
+#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	mysqltest2	t2
 create or replace table mysqltest2.t2 (a int) select 1 as 'a', 2 as 'a';
 ERROR 42S21: Duplicate column name 'a'
 select * from information_schema.metadata_lock_info;
@@ -398,31 +398,31 @@ create table t1 (a int);
 lock table t1 write, t2 read;
 select * from information_schema.metadata_lock_info;
 THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
-#	MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT	Global read lock		
-#	MDL_SHARED_NO_READ_WRITE	MDL_EXPLICIT	Table metadata lock	test	t1
-#	MDL_SHARED_READ	MDL_EXPLICIT	Table metadata lock	test	t2
-#	MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT	Schema metadata lock	test	
+#	MDL_INTENTION_EXCLUSIVE	NULL	Global read lock		
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	test	
+#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	test	t1
+#	MDL_SHARED_READ	NULL	Table metadata lock	test	t2
 create or replace table t1 (i int);
 select * from information_schema.metadata_lock_info;
 THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
-#	MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT	Global read lock		
-#	MDL_SHARED_NO_READ_WRITE	MDL_EXPLICIT	Table metadata lock	test	t1
-#	MDL_SHARED_READ	MDL_EXPLICIT	Table metadata lock	test	t2
-#	MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT	Schema metadata lock	test	
+#	MDL_INTENTION_EXCLUSIVE	NULL	Global read lock		
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	test	
+#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	test	t1
+#	MDL_SHARED_READ	NULL	Table metadata lock	test	t2
 create or replace table t1 like t2;
 select * from information_schema.metadata_lock_info;
 THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
-#	MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT	Global read lock		
-#	MDL_SHARED_NO_READ_WRITE	MDL_EXPLICIT	Table metadata lock	test	t1
-#	MDL_SHARED_READ	MDL_EXPLICIT	Table metadata lock	test	t2
-#	MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT	Schema metadata lock	test	
+#	MDL_INTENTION_EXCLUSIVE	NULL	Global read lock		
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	test	
+#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	test	t1
+#	MDL_SHARED_READ	NULL	Table metadata lock	test	t2
 create or replace table t1 select 1 as f1;
 select * from information_schema.metadata_lock_info;
 THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
-#	MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT	Global read lock		
-#	MDL_SHARED_NO_READ_WRITE	MDL_EXPLICIT	Table metadata lock	test	t1
-#	MDL_SHARED_READ	MDL_EXPLICIT	Table metadata lock	test	t2
-#	MDL_INTENTION_EXCLUSIVE	MDL_EXPLICIT	Schema metadata lock	test	
+#	MDL_INTENTION_EXCLUSIVE	NULL	Global read lock		
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	test	
+#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	test	t1
+#	MDL_SHARED_READ	NULL	Table metadata lock	test	t2
 drop table t1;
 unlock tables;
 #
diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result
index bf3969a20fe..e7c40b5ed91 100644
--- a/mysql-test/r/ctype_binary.result
+++ b/mysql-test/r/ctype_binary.result
@@ -2887,6 +2887,15 @@ f1()
 DROP FUNCTION f1;
 DROP TABLE t1;
 #
+# MDEV-9662 Assertion `precision || !scale' failed in my_decimal_precision_to_length_no_truncation(uint, uint8, bool)
+#
+SELECT @@collation_connection;
+@@collation_connection
+binary
+SELECT CASE 1 WHEN 2 THEN ( - '3' ) END;
+CASE 1 WHEN 2 THEN ( - '3' ) END
+NULL
+#
 # MDEV-5702 Incorrect results are returned with NULLIF()
 #
 CREATE TABLE t1 (d DATE);
diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result
index c16847477ce..36dc23f33be 100644
--- a/mysql-test/r/ctype_cp1251.result
+++ b/mysql-test/r/ctype_cp1251.result
@@ -3296,6 +3296,15 @@ f1()
 DROP FUNCTION f1;
 DROP TABLE t1;
 #
+# MDEV-9662 Assertion `precision || !scale' failed in my_decimal_precision_to_length_no_truncation(uint, uint8, bool)
+#
+SELECT @@collation_connection;
+@@collation_connection
+cp1251_general_ci
+SELECT CASE 1 WHEN 2 THEN ( - '3' ) END;
+CASE 1 WHEN 2 THEN ( - '3' ) END
+NULL
+#
 # MDEV-5702 Incorrect results are returned with NULLIF()
 #
 CREATE TABLE t1 (d DATE);
diff --git a/mysql-test/r/ctype_cp850.result b/mysql-test/r/ctype_cp850.result
new file mode 100644
index 00000000000..c028f72b58a
--- /dev/null
+++ b/mysql-test/r/ctype_cp850.result
@@ -0,0 +1,14 @@
+#
+# Start of 5.5 tests
+#
+#
+# MDEV-9862 Illegal mix of collation, when comparing column with CASE expression
+#
+SET NAMES cp850;
+CREATE TABLE t1 (a CHAR(1) CHARACTER SET latin1);
+SELECT a FROM t1 WHERE CASE a WHEN 'aaaa' THEN 'Y' WHEN 'aaaa' THEN 'Y' ELSE NULL END <> a;
+a
+DROP TABLE t1;
+#
+# End of 5.5 tests
+#
diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result
index a5593bf040b..b0bcc5d7bd2 100644
--- a/mysql-test/r/ctype_latin1.result
+++ b/mysql-test/r/ctype_latin1.result
@@ -3593,6 +3593,15 @@ f1()
 DROP FUNCTION f1;
 DROP TABLE t1;
 #
+# MDEV-9662 Assertion `precision || !scale' failed in my_decimal_precision_to_length_no_truncation(uint, uint8, bool)
+#
+SELECT @@collation_connection;
+@@collation_connection
+latin1_swedish_ci
+SELECT CASE 1 WHEN 2 THEN ( - '3' ) END;
+CASE 1 WHEN 2 THEN ( - '3' ) END
+NULL
+#
 # MDEV-5702 Incorrect results are returned with NULLIF()
 #
 CREATE TABLE t1 (d DATE);
diff --git a/mysql-test/r/ctype_ldml.result b/mysql-test/r/ctype_ldml.result
index acb92a8363b..d333c03143b 100644
--- a/mysql-test/r/ctype_ldml.result
+++ b/mysql-test/r/ctype_ldml.result
@@ -460,6 +460,7 @@ utf8mb4_test_400_ci	utf8mb4	328			8
 latin1_test2	latin1	332			1
 utf8_bengali_standard_ci	utf8	336			8
 utf8_bengali_traditional_ci	utf8	337			8
+utf8_implicit_weights_ci	utf8	338			8
 utf8_phone_ci	utf8	352			8
 utf8_test_ci	utf8	353			8
 utf8_5624_1	utf8	354			8
@@ -1156,3 +1157,25 @@ Warning	1273	Expansion too long: 'a\u002Daaaaaa10'
 #
 # Search for occurrences of [ERROR] Syntax error at '[strength tertiary]'
 Occurances : 2
+#
+# MDEV-8686 A user defined collation utf8_confusables doesn't work
+#
+CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_implicit_weights_ci);
+INSERT INTO t1 VALUES ('a'),('b'),('c');
+INSERT INTO t1 VALUES (_ucs2 0x1500),(_ucs2 0x1501);
+INSERT INTO t1 VALUES (_ucs2 0x3400),(_ucs2 0x3560),(_ucs2 0x3561),(_ucs2 0x3600);
+INSERT INTO t1 VALUES (_ucs2 0x3700),(_ucs2 0x3701);
+SELECT HEX(CONVERT(a USING ucs2)) AS ch, HEX(WEIGHT_STRING(a)) AS w, HEX(WEIGHT_STRING(a COLLATE utf8_unicode_ci)) AS ducet FROM t1 ORDER BY a,ch;
+ch	w	ducet
+0061	0E33	0E33
+3561	0E33	FB80B561
+0063	0E60	0E60
+1500	0E60	1BAD
+0062	FB80B400	0E4A
+3400	FB80B400	FB80B400
+3560	FB80B560	FB80B560
+1501	FB80B600	1BAE
+3600	FB80B600	FB80B600
+3700	FB80B700	FB80B700
+3701	FB80B700	FB80B701
+DROP TABLE t1;
diff --git a/mysql-test/r/ctype_like_range.result b/mysql-test/r/ctype_like_range.result
index 033dc214335..176cfe3acf0 100644
--- a/mysql-test/r/ctype_like_range.result
+++ b/mysql-test/r/ctype_like_range.result
@@ -1,3 +1,4 @@
+SET NAMES utf8;
 DROP TABLE IF EXISTS t1;
 DROP VIEW IF EXISTS v1;
 CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a VARBINARY(32));
@@ -284,6 +285,8 @@ id	name	val
 32	mx	63616161616161616161616161616161
 32	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf8;
+INSERT INTO t1 (a) VALUES (_ucs2 0x0425),(_ucs2 0x045F);
+INSERT INTO t1 (a) VALUES (_ucs2 0x2525),(_ucs2 0x5F5F);
 SELECT * FROM v1;
 id	name	val
 1	a	
@@ -414,6 +417,22 @@ id	name	val
 32	mn	6361616161
 32	mx	6361616161
 32	sp	--------------------------------
+33	a	Х
+33	mn	D0A5
+33	mx	D0A5
+33	sp	--------------------------------
+34	a	џ
+34	mn	D19F
+34	mx	D19F
+34	sp	--------------------------------
+35	a	┥
+35	mn	E294A5
+35	mx	E294A5
+35	sp	--------------------------------
+36	a	彟
+36	mn	E5BD9F
+36	mx	E5BD9F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 SELECT * FROM v1;
 id	name	val
@@ -545,6 +564,22 @@ id	name	val
 32	mn	6361616161
 32	mx	6361616161
 32	sp	--------------------------------
+33	a	Х
+33	mn	D0A5
+33	mx	D0A5
+33	sp	--------------------------------
+34	a	џ
+34	mn	D19F
+34	mx	D19F
+34	sp	--------------------------------
+35	a	┥
+35	mn	E294A5
+35	mx	E294A5
+35	sp	--------------------------------
+36	a	彟
+36	mn	E5BD9F
+36	mx	E5BD9F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_czech_ci;
 SELECT * FROM v1;
 id	name	val
@@ -676,6 +711,22 @@ id	name	val
 32	mn	6361616161
 32	mx	6361616161
 32	sp	--------------------------------
+33	a	Х
+33	mn	D0A5
+33	mx	D0A5
+33	sp	--------------------------------
+34	a	џ
+34	mn	D19F
+34	mx	D19F
+34	sp	--------------------------------
+35	a	┥
+35	mn	E294A5
+35	mx	E294A5
+35	sp	--------------------------------
+36	a	彟
+36	mn	E5BD9F
+36	mx	E5BD9F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_danish_ci;
 SELECT * FROM v1;
 id	name	val
@@ -807,6 +858,169 @@ id	name	val
 32	mn	6361616161
 32	mx	6361616161
 32	sp	--------------------------------
+33	a	Х
+33	mn	D0A5
+33	mx	D0A5
+33	sp	--------------------------------
+34	a	џ
+34	mn	D19F
+34	mx	D19F
+34	sp	--------------------------------
+35	a	┥
+35	mn	E294A5
+35	mx	E294A5
+35	sp	--------------------------------
+36	a	彟
+36	mn	E5BD9F
+36	mx	E5BD9F
+36	sp	--------------------------------
+ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_thai_520_w2;
+SELECT * FROM v1;
+id	name	val
+1	a	
+1	mn	
+1	mx	
+1	sp	--------------------------------
+2	a	_
+2	mn	09
+2	mx	EFBFBF
+2	sp	--------------------------------
+3	a	%
+3	mn	09090909090909090909090909090909
+3	mx	EFBFBFEFBFBFEFBFBFEFBFBFEFBFBF20
+3	sp	--------------------------------
+4	a	\_
+4	mn	5F
+4	mx	5F
+4	sp	--------------------------------
+5	a	\%
+5	mn	25
+5	mx	25
+5	sp	--------------------------------
+6	a	\
+6	mn	5C
+6	mx	5C
+6	sp	--------------------------------
+7	a	a
+7	mn	61
+7	mx	61
+7	sp	--------------------------------
+8	a	c
+8	mn	63
+8	mx	63
+8	sp	--------------------------------
+9	a	a_
+9	mn	6109
+9	mx	61EFBFBF
+9	sp	--------------------------------
+10	a	c_
+10	mn	6309
+10	mx	63EFBFBF
+10	sp	--------------------------------
+11	a	a%
+11	mn	61090909090909090909090909090909
+11	mx	61EFBFBFEFBFBFEFBFBFEFBFBFEFBFBF
+11	sp	--------------------------------
+12	a	c%
+12	mn	63090909090909090909090909090909
+12	mx	63EFBFBFEFBFBFEFBFBFEFBFBFEFBFBF
+12	sp	--------------------------------
+13	a	aa
+13	mn	6161
+13	mx	6161
+13	sp	--------------------------------
+14	a	cc
+14	mn	6363
+14	mx	6363
+14	sp	--------------------------------
+15	a	ch
+15	mn	6368
+15	mx	6368
+15	sp	--------------------------------
+16	a	aa_
+16	mn	616109
+16	mx	6161EFBFBF
+16	sp	--------------------------------
+17	a	cc_
+17	mn	636309
+17	mx	6363EFBFBF
+17	sp	--------------------------------
+18	a	ch_
+18	mn	636809
+18	mx	6368EFBFBF
+18	sp	--------------------------------
+19	a	aa%
+19	mn	61610909090909090909090909090909
+19	mx	6161EFBFBFEFBFBFEFBFBFEFBFBF2020
+19	sp	--------------------------------
+20	a	cc%
+20	mn	63630909090909090909090909090909
+20	mx	6363EFBFBFEFBFBFEFBFBFEFBFBF2020
+20	sp	--------------------------------
+21	a	ch%
+21	mn	63680909090909090909090909090909
+21	mx	6368EFBFBFEFBFBFEFBFBFEFBFBF2020
+21	sp	--------------------------------
+22	a	aaa
+22	mn	616161
+22	mx	616161
+22	sp	--------------------------------
+23	a	ccc
+23	mn	636363
+23	mx	636363
+23	sp	--------------------------------
+24	a	cch
+24	mn	636368
+24	mx	636368
+24	sp	--------------------------------
+25	a	aaa_
+25	mn	61616109
+25	mx	616161EFBFBF
+25	sp	--------------------------------
+26	a	ccc_
+26	mn	63636309
+26	mx	636363EFBFBF
+26	sp	--------------------------------
+27	a	cch_
+27	mn	63636809
+27	mx	636368EFBFBF
+27	sp	--------------------------------
+28	a	aaa%
+28	mn	61616109090909090909090909090909
+28	mx	616161EFBFBFEFBFBFEFBFBFEFBFBF20
+28	sp	--------------------------------
+29	a	ccc%
+29	mn	63636309090909090909090909090909
+29	mx	636363EFBFBFEFBFBFEFBFBFEFBFBF20
+29	sp	--------------------------------
+30	a	cch%
+30	mn	63636809090909090909090909090909
+30	mx	636368EFBFBFEFBFBFEFBFBFEFBFBF20
+30	sp	--------------------------------
+31	a	aaaaaaaaaaaaaaaaaaaa
+31	mn	6161616161
+31	mx	6161616161
+31	sp	--------------------------------
+32	a	caaaaaaaaaaaaaaaaaaa
+32	mn	6361616161
+32	mx	6361616161
+32	sp	--------------------------------
+33	a	Х
+33	mn	D0A5
+33	mx	D0A5
+33	sp	--------------------------------
+34	a	џ
+34	mn	D19F
+34	mx	D19F
+34	sp	--------------------------------
+35	a	┥
+35	mn	E294A5
+35	mx	E294A5
+35	sp	--------------------------------
+36	a	彟
+36	mn	E5BD9F
+36	mx	E5BD9F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf8mb4;
 SELECT * FROM v1;
 id	name	val
@@ -938,6 +1152,22 @@ id	name	val
 32	mn	63616161
 32	mx	63616161
 32	sp	--------------------------------
+33	a	Х
+33	mn	D0A5
+33	mx	D0A5
+33	sp	--------------------------------
+34	a	џ
+34	mn	D19F
+34	mx	D19F
+34	sp	--------------------------------
+35	a	┥
+35	mn	E294A5
+35	mx	E294A5
+35	sp	--------------------------------
+36	a	彟
+36	mn	E5BD9F
+36	mx	E5BD9F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 SELECT * FROM v1;
 id	name	val
@@ -1069,6 +1299,22 @@ id	name	val
 32	mn	63616161
 32	mx	63616161
 32	sp	--------------------------------
+33	a	Х
+33	mn	D0A5
+33	mx	D0A5
+33	sp	--------------------------------
+34	a	џ
+34	mn	D19F
+34	mx	D19F
+34	sp	--------------------------------
+35	a	┥
+35	mn	E294A5
+35	mx	E294A5
+35	sp	--------------------------------
+36	a	彟
+36	mn	E5BD9F
+36	mx	E5BD9F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_czech_ci;
 SELECT * FROM v1;
 id	name	val
@@ -1200,6 +1446,22 @@ id	name	val
 32	mn	63616161
 32	mx	63616161
 32	sp	--------------------------------
+33	a	Х
+33	mn	D0A5
+33	mx	D0A5
+33	sp	--------------------------------
+34	a	џ
+34	mn	D19F
+34	mx	D19F
+34	sp	--------------------------------
+35	a	┥
+35	mn	E294A5
+35	mx	E294A5
+35	sp	--------------------------------
+36	a	彟
+36	mn	E5BD9F
+36	mx	E5BD9F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_danish_ci;
 SELECT * FROM v1;
 id	name	val
@@ -1331,6 +1593,169 @@ id	name	val
 32	mn	63616109090909090909090909090909
 32	mx	636161EFBFBFEFBFBFEFBFBFEFBFBF20
 32	sp	--------------------------------
+33	a	Х
+33	mn	D0A5
+33	mx	D0A5
+33	sp	--------------------------------
+34	a	џ
+34	mn	D19F
+34	mx	D19F
+34	sp	--------------------------------
+35	a	┥
+35	mn	E294A5
+35	mx	E294A5
+35	sp	--------------------------------
+36	a	彟
+36	mn	E5BD9F
+36	mx	E5BD9F
+36	sp	--------------------------------
+ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_thai_520_w2;
+SELECT * FROM v1;
+id	name	val
+1	a	
+1	mn	
+1	mx	
+1	sp	--------------------------------
+2	a	_
+2	mn	09
+2	mx	EFBFBF
+2	sp	--------------------------------
+3	a	%
+3	mn	09090909090909090909090909090909
+3	mx	EFBFBFEFBFBFEFBFBFEFBFBFEFBFBF20
+3	sp	--------------------------------
+4	a	\_
+4	mn	5F
+4	mx	5F
+4	sp	--------------------------------
+5	a	\%
+5	mn	25
+5	mx	25
+5	sp	--------------------------------
+6	a	\
+6	mn	5C
+6	mx	5C
+6	sp	--------------------------------
+7	a	a
+7	mn	61
+7	mx	61
+7	sp	--------------------------------
+8	a	c
+8	mn	63
+8	mx	63
+8	sp	--------------------------------
+9	a	a_
+9	mn	6109
+9	mx	61EFBFBF
+9	sp	--------------------------------
+10	a	c_
+10	mn	6309
+10	mx	63EFBFBF
+10	sp	--------------------------------
+11	a	a%
+11	mn	61090909090909090909090909090909
+11	mx	61EFBFBFEFBFBFEFBFBFEFBFBFEFBFBF
+11	sp	--------------------------------
+12	a	c%
+12	mn	63090909090909090909090909090909
+12	mx	63EFBFBFEFBFBFEFBFBFEFBFBFEFBFBF
+12	sp	--------------------------------
+13	a	aa
+13	mn	6161
+13	mx	6161
+13	sp	--------------------------------
+14	a	cc
+14	mn	6363
+14	mx	6363
+14	sp	--------------------------------
+15	a	ch
+15	mn	6368
+15	mx	6368
+15	sp	--------------------------------
+16	a	aa_
+16	mn	616109
+16	mx	6161EFBFBF
+16	sp	--------------------------------
+17	a	cc_
+17	mn	636309
+17	mx	6363EFBFBF
+17	sp	--------------------------------
+18	a	ch_
+18	mn	636809
+18	mx	6368EFBFBF
+18	sp	--------------------------------
+19	a	aa%
+19	mn	61610909090909090909090909090909
+19	mx	6161EFBFBFEFBFBFEFBFBFEFBFBF2020
+19	sp	--------------------------------
+20	a	cc%
+20	mn	63630909090909090909090909090909
+20	mx	6363EFBFBFEFBFBFEFBFBFEFBFBF2020
+20	sp	--------------------------------
+21	a	ch%
+21	mn	63680909090909090909090909090909
+21	mx	6368EFBFBFEFBFBFEFBFBFEFBFBF2020
+21	sp	--------------------------------
+22	a	aaa
+22	mn	616161
+22	mx	616161
+22	sp	--------------------------------
+23	a	ccc
+23	mn	636363
+23	mx	636363
+23	sp	--------------------------------
+24	a	cch
+24	mn	636368
+24	mx	636368
+24	sp	--------------------------------
+25	a	aaa_
+25	mn	61616109
+25	mx	616161EFBFBF
+25	sp	--------------------------------
+26	a	ccc_
+26	mn	63636309
+26	mx	636363EFBFBF
+26	sp	--------------------------------
+27	a	cch_
+27	mn	63636809
+27	mx	636368EFBFBF
+27	sp	--------------------------------
+28	a	aaa%
+28	mn	61616109090909090909090909090909
+28	mx	616161EFBFBFEFBFBFEFBFBFEFBFBF20
+28	sp	--------------------------------
+29	a	ccc%
+29	mn	63636309090909090909090909090909
+29	mx	636363EFBFBFEFBFBFEFBFBFEFBFBF20
+29	sp	--------------------------------
+30	a	cch%
+30	mn	63636809090909090909090909090909
+30	mx	636368EFBFBFEFBFBFEFBFBFEFBFBF20
+30	sp	--------------------------------
+31	a	aaaaaaaaaaaaaaaaaaaa
+31	mn	61616161
+31	mx	61616161
+31	sp	--------------------------------
+32	a	caaaaaaaaaaaaaaaaaaa
+32	mn	63616161
+32	mx	63616161
+32	sp	--------------------------------
+33	a	Х
+33	mn	D0A5
+33	mx	D0A5
+33	sp	--------------------------------
+34	a	џ
+34	mn	D19F
+34	mx	D19F
+34	sp	--------------------------------
+35	a	┥
+35	mn	E294A5
+35	mx	E294A5
+35	sp	--------------------------------
+36	a	彟
+36	mn	E5BD9F
+36	mx	E5BD9F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
 SELECT * FROM v1;
 id	name	val
@@ -1462,6 +1887,22 @@ id	name	val
 32	mn	63616161
 32	mx	63616161
 32	sp	--------------------------------
+33	a	Х
+33	mn	D0A5
+33	mx	D0A5
+33	sp	--------------------------------
+34	a	џ
+34	mn	D19F
+34	mx	D19F
+34	sp	--------------------------------
+35	a	┥
+35	mn	E294A5
+35	mx	E294A5
+35	sp	--------------------------------
+36	a	彟
+36	mn	E5BD9F
+36	mx	E5BD9F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET ucs2;
 SELECT * FROM v1;
 id	name	val
@@ -1593,6 +2034,22 @@ id	name	val
 32	mn	00630061006100610061006100610061
 32	mx	00630061006100610061006100610061
 32	sp	--------------------------------
+33	a	Х
+33	mn	0425
+33	mx	0425
+33	sp	--------------------------------
+34	a	џ
+34	mn	045F
+34	mx	045F
+34	sp	--------------------------------
+35	a	┥
+35	mn	2525
+35	mx	2525
+35	sp	--------------------------------
+36	a	彟
+36	mn	5F5F
+36	mx	5F5F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET ucs2 COLLATE ucs2_unicode_ci;
 SELECT * FROM v1;
 id	name	val
@@ -1724,6 +2181,22 @@ id	name	val
 32	mn	00630061006100610061006100610061
 32	mx	00630061006100610061006100610061
 32	sp	--------------------------------
+33	a	Х
+33	mn	0425
+33	mx	0425
+33	sp	--------------------------------
+34	a	џ
+34	mn	045F
+34	mx	045F
+34	sp	--------------------------------
+35	a	┥
+35	mn	2525
+35	mx	2525
+35	sp	--------------------------------
+36	a	彟
+36	mn	5F5F
+36	mx	5F5F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET ucs2 COLLATE ucs2_czech_ci;
 SELECT * FROM v1;
 id	name	val
@@ -1855,6 +2328,22 @@ id	name	val
 32	mn	00630061006100610061006100610061
 32	mx	00630061006100610061006100610061
 32	sp	--------------------------------
+33	a	Х
+33	mn	0425
+33	mx	0425
+33	sp	--------------------------------
+34	a	џ
+34	mn	045F
+34	mx	045F
+34	sp	--------------------------------
+35	a	┥
+35	mn	2525
+35	mx	2525
+35	sp	--------------------------------
+36	a	彟
+36	mn	5F5F
+36	mx	5F5F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET ucs2 COLLATE ucs2_danish_ci;
 SELECT * FROM v1;
 id	name	val
@@ -1986,6 +2475,169 @@ id	name	val
 32	mn	00630061006100610061006100610009
 32	mx	0063006100610061006100610061FFFF
 32	sp	--------------------------------
+33	a	Х
+33	mn	0425
+33	mx	0425
+33	sp	--------------------------------
+34	a	џ
+34	mn	045F
+34	mx	045F
+34	sp	--------------------------------
+35	a	┥
+35	mn	2525
+35	mx	2525
+35	sp	--------------------------------
+36	a	彟
+36	mn	5F5F
+36	mx	5F5F
+36	sp	--------------------------------
+ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET ucs2 COLLATE ucs2_thai_520_w2;
+SELECT * FROM v1;
+id	name	val
+1	a	
+1	mn	
+1	mx	
+1	sp	--------------------------------
+2	a	_
+2	mn	0009
+2	mx	FFFF
+2	sp	--------------------------------
+3	a	%
+3	mn	00090009000900090009000900090009
+3	mx	FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
+3	sp	--------------------------------
+4	a	\_
+4	mn	005F
+4	mx	005F
+4	sp	--------------------------------
+5	a	\%
+5	mn	0025
+5	mx	0025
+5	sp	--------------------------------
+6	a	\
+6	mn	005C
+6	mx	005C
+6	sp	--------------------------------
+7	a	a
+7	mn	0061
+7	mx	0061
+7	sp	--------------------------------
+8	a	c
+8	mn	0063
+8	mx	0063
+8	sp	--------------------------------
+9	a	a_
+9	mn	00610009
+9	mx	0061FFFF
+9	sp	--------------------------------
+10	a	c_
+10	mn	00630009
+10	mx	0063FFFF
+10	sp	--------------------------------
+11	a	a%
+11	mn	00610009000900090009000900090009
+11	mx	0061FFFFFFFFFFFFFFFFFFFFFFFFFFFF
+11	sp	--------------------------------
+12	a	c%
+12	mn	00630009000900090009000900090009
+12	mx	0063FFFFFFFFFFFFFFFFFFFFFFFFFFFF
+12	sp	--------------------------------
+13	a	aa
+13	mn	00610061
+13	mx	00610061
+13	sp	--------------------------------
+14	a	cc
+14	mn	00630063
+14	mx	00630063
+14	sp	--------------------------------
+15	a	ch
+15	mn	00630068
+15	mx	00630068
+15	sp	--------------------------------
+16	a	aa_
+16	mn	006100610009
+16	mx	00610061FFFF
+16	sp	--------------------------------
+17	a	cc_
+17	mn	006300630009
+17	mx	00630063FFFF
+17	sp	--------------------------------
+18	a	ch_
+18	mn	006300680009
+18	mx	00630068FFFF
+18	sp	--------------------------------
+19	a	aa%
+19	mn	00610061000900090009000900090009
+19	mx	00610061FFFFFFFFFFFFFFFFFFFFFFFF
+19	sp	--------------------------------
+20	a	cc%
+20	mn	00630063000900090009000900090009
+20	mx	00630063FFFFFFFFFFFFFFFFFFFFFFFF
+20	sp	--------------------------------
+21	a	ch%
+21	mn	00630068000900090009000900090009
+21	mx	00630068FFFFFFFFFFFFFFFFFFFFFFFF
+21	sp	--------------------------------
+22	a	aaa
+22	mn	006100610061
+22	mx	006100610061
+22	sp	--------------------------------
+23	a	ccc
+23	mn	006300630063
+23	mx	006300630063
+23	sp	--------------------------------
+24	a	cch
+24	mn	006300630068
+24	mx	006300630068
+24	sp	--------------------------------
+25	a	aaa_
+25	mn	0061006100610009
+25	mx	006100610061FFFF
+25	sp	--------------------------------
+26	a	ccc_
+26	mn	0063006300630009
+26	mx	006300630063FFFF
+26	sp	--------------------------------
+27	a	cch_
+27	mn	0063006300680009
+27	mx	006300630068FFFF
+27	sp	--------------------------------
+28	a	aaa%
+28	mn	00610061006100090009000900090009
+28	mx	006100610061FFFFFFFFFFFFFFFFFFFF
+28	sp	--------------------------------
+29	a	ccc%
+29	mn	00630063006300090009000900090009
+29	mx	006300630063FFFFFFFFFFFFFFFFFFFF
+29	sp	--------------------------------
+30	a	cch%
+30	mn	00630063006800090009000900090009
+30	mx	006300630068FFFFFFFFFFFFFFFFFFFF
+30	sp	--------------------------------
+31	a	aaaaaaaaaaaaaaaaaaaa
+31	mn	00610061006100610061006100610061
+31	mx	00610061006100610061006100610061
+31	sp	--------------------------------
+32	a	caaaaaaaaaaaaaaaaaaa
+32	mn	00630061006100610061006100610061
+32	mx	00630061006100610061006100610061
+32	sp	--------------------------------
+33	a	Х
+33	mn	0425
+33	mx	0425
+33	sp	--------------------------------
+34	a	џ
+34	mn	045F
+34	mx	045F
+34	sp	--------------------------------
+35	a	┥
+35	mn	2525
+35	mx	2525
+35	sp	--------------------------------
+36	a	彟
+36	mn	5F5F
+36	mx	5F5F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf16;
 SELECT * FROM v1;
 id	name	val
@@ -2117,6 +2769,22 @@ id	name	val
 32	mn	0063006100610061
 32	mx	0063006100610061
 32	sp	--------------------------------
+33	a	Х
+33	mn	0425
+33	mx	0425
+33	sp	--------------------------------
+34	a	џ
+34	mn	045F
+34	mx	045F
+34	sp	--------------------------------
+35	a	┥
+35	mn	2525
+35	mx	2525
+35	sp	--------------------------------
+36	a	彟
+36	mn	5F5F
+36	mx	5F5F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf16 COLLATE utf16_unicode_ci;
 SELECT * FROM v1;
 id	name	val
@@ -2248,6 +2916,22 @@ id	name	val
 32	mn	0063006100610061
 32	mx	0063006100610061
 32	sp	--------------------------------
+33	a	Х
+33	mn	0425
+33	mx	0425
+33	sp	--------------------------------
+34	a	џ
+34	mn	045F
+34	mx	045F
+34	sp	--------------------------------
+35	a	┥
+35	mn	2525
+35	mx	2525
+35	sp	--------------------------------
+36	a	彟
+36	mn	5F5F
+36	mx	5F5F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf16 COLLATE utf16_czech_ci;
 SELECT * FROM v1;
 id	name	val
@@ -2379,6 +3063,22 @@ id	name	val
 32	mn	0063006100610061
 32	mx	0063006100610061
 32	sp	--------------------------------
+33	a	Х
+33	mn	0425
+33	mx	0425
+33	sp	--------------------------------
+34	a	џ
+34	mn	045F
+34	mx	045F
+34	sp	--------------------------------
+35	a	┥
+35	mn	2525
+35	mx	2525
+35	sp	--------------------------------
+36	a	彟
+36	mn	5F5F
+36	mx	5F5F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf16 COLLATE utf16_danish_ci;
 SELECT * FROM v1;
 id	name	val
@@ -2510,6 +3210,169 @@ id	name	val
 32	mn	00630061006100090009000900090009
 32	mx	006300610061FFFFFFFFFFFFFFFFFFFF
 32	sp	--------------------------------
+33	a	Х
+33	mn	0425
+33	mx	0425
+33	sp	--------------------------------
+34	a	џ
+34	mn	045F
+34	mx	045F
+34	sp	--------------------------------
+35	a	┥
+35	mn	2525
+35	mx	2525
+35	sp	--------------------------------
+36	a	彟
+36	mn	5F5F
+36	mx	5F5F
+36	sp	--------------------------------
+ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf16 COLLATE utf16_thai_520_w2;
+SELECT * FROM v1;
+id	name	val
+1	a	
+1	mn	
+1	mx	
+1	sp	--------------------------------
+2	a	_
+2	mn	0009
+2	mx	FFFF
+2	sp	--------------------------------
+3	a	%
+3	mn	00090009000900090009000900090009
+3	mx	FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
+3	sp	--------------------------------
+4	a	\_
+4	mn	005F
+4	mx	005F
+4	sp	--------------------------------
+5	a	\%
+5	mn	0025
+5	mx	0025
+5	sp	--------------------------------
+6	a	\
+6	mn	005C
+6	mx	005C
+6	sp	--------------------------------
+7	a	a
+7	mn	0061
+7	mx	0061
+7	sp	--------------------------------
+8	a	c
+8	mn	0063
+8	mx	0063
+8	sp	--------------------------------
+9	a	a_
+9	mn	00610009
+9	mx	0061FFFF
+9	sp	--------------------------------
+10	a	c_
+10	mn	00630009
+10	mx	0063FFFF
+10	sp	--------------------------------
+11	a	a%
+11	mn	00610009000900090009000900090009
+11	mx	0061FFFFFFFFFFFFFFFFFFFFFFFFFFFF
+11	sp	--------------------------------
+12	a	c%
+12	mn	00630009000900090009000900090009
+12	mx	0063FFFFFFFFFFFFFFFFFFFFFFFFFFFF
+12	sp	--------------------------------
+13	a	aa
+13	mn	00610061
+13	mx	00610061
+13	sp	--------------------------------
+14	a	cc
+14	mn	00630063
+14	mx	00630063
+14	sp	--------------------------------
+15	a	ch
+15	mn	00630068
+15	mx	00630068
+15	sp	--------------------------------
+16	a	aa_
+16	mn	006100610009
+16	mx	00610061FFFF
+16	sp	--------------------------------
+17	a	cc_
+17	mn	006300630009
+17	mx	00630063FFFF
+17	sp	--------------------------------
+18	a	ch_
+18	mn	006300680009
+18	mx	00630068FFFF
+18	sp	--------------------------------
+19	a	aa%
+19	mn	00610061000900090009000900090009
+19	mx	00610061FFFFFFFFFFFFFFFFFFFFFFFF
+19	sp	--------------------------------
+20	a	cc%
+20	mn	00630063000900090009000900090009
+20	mx	00630063FFFFFFFFFFFFFFFFFFFFFFFF
+20	sp	--------------------------------
+21	a	ch%
+21	mn	00630068000900090009000900090009
+21	mx	00630068FFFFFFFFFFFFFFFFFFFFFFFF
+21	sp	--------------------------------
+22	a	aaa
+22	mn	006100610061
+22	mx	006100610061
+22	sp	--------------------------------
+23	a	ccc
+23	mn	006300630063
+23	mx	006300630063
+23	sp	--------------------------------
+24	a	cch
+24	mn	006300630068
+24	mx	006300630068
+24	sp	--------------------------------
+25	a	aaa_
+25	mn	0061006100610009
+25	mx	006100610061FFFF
+25	sp	--------------------------------
+26	a	ccc_
+26	mn	0063006300630009
+26	mx	006300630063FFFF
+26	sp	--------------------------------
+27	a	cch_
+27	mn	0063006300680009
+27	mx	006300630068FFFF
+27	sp	--------------------------------
+28	a	aaa%
+28	mn	00610061006100090009000900090009
+28	mx	006100610061FFFFFFFFFFFFFFFFFFFF
+28	sp	--------------------------------
+29	a	ccc%
+29	mn	00630063006300090009000900090009
+29	mx	006300630063FFFFFFFFFFFFFFFFFFFF
+29	sp	--------------------------------
+30	a	cch%
+30	mn	00630063006800090009000900090009
+30	mx	006300630068FFFFFFFFFFFFFFFFFFFF
+30	sp	--------------------------------
+31	a	aaaaaaaaaaaaaaaaaaaa
+31	mn	0061006100610061
+31	mx	0061006100610061
+31	sp	--------------------------------
+32	a	caaaaaaaaaaaaaaaaaaa
+32	mn	0063006100610061
+32	mx	0063006100610061
+32	sp	--------------------------------
+33	a	Х
+33	mn	0425
+33	mx	0425
+33	sp	--------------------------------
+34	a	џ
+34	mn	045F
+34	mx	045F
+34	sp	--------------------------------
+35	a	┥
+35	mn	2525
+35	mx	2525
+35	sp	--------------------------------
+36	a	彟
+36	mn	5F5F
+36	mx	5F5F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf16 COLLATE utf16_unicode_520_ci;
 SELECT * FROM v1;
 id	name	val
@@ -2641,6 +3504,22 @@ id	name	val
 32	mn	0063006100610061
 32	mx	0063006100610061
 32	sp	--------------------------------
+33	a	Х
+33	mn	0425
+33	mx	0425
+33	sp	--------------------------------
+34	a	џ
+34	mn	045F
+34	mx	045F
+34	sp	--------------------------------
+35	a	┥
+35	mn	2525
+35	mx	2525
+35	sp	--------------------------------
+36	a	彟
+36	mn	5F5F
+36	mx	5F5F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf32;
 SELECT * FROM v1;
 id	name	val
@@ -2772,6 +3651,22 @@ id	name	val
 32	mn	00000063000000610000006100000061
 32	mx	00000063000000610000006100000061
 32	sp	--------------------------------
+33	a	Х
+33	mn	00000425
+33	mx	00000425
+33	sp	--------------------------------
+34	a	џ
+34	mn	0000045F
+34	mx	0000045F
+34	sp	--------------------------------
+35	a	┥
+35	mn	00002525
+35	mx	00002525
+35	sp	--------------------------------
+36	a	彟
+36	mn	00005F5F
+36	mx	00005F5F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf32 COLLATE utf32_unicode_ci;
 SELECT * FROM v1;
 id	name	val
@@ -2903,6 +3798,22 @@ id	name	val
 32	mn	00000063000000610000006100000061
 32	mx	00000063000000610000006100000061
 32	sp	--------------------------------
+33	a	Х
+33	mn	00000425
+33	mx	00000425
+33	sp	--------------------------------
+34	a	џ
+34	mn	0000045F
+34	mx	0000045F
+34	sp	--------------------------------
+35	a	┥
+35	mn	00002525
+35	mx	00002525
+35	sp	--------------------------------
+36	a	彟
+36	mn	00005F5F
+36	mx	00005F5F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf32 COLLATE utf32_czech_ci;
 SELECT * FROM v1;
 id	name	val
@@ -3034,6 +3945,22 @@ id	name	val
 32	mn	00000063000000610000006100000061
 32	mx	00000063000000610000006100000061
 32	sp	--------------------------------
+33	a	Х
+33	mn	00000425
+33	mx	00000425
+33	sp	--------------------------------
+34	a	џ
+34	mn	0000045F
+34	mx	0000045F
+34	sp	--------------------------------
+35	a	┥
+35	mn	00002525
+35	mx	00002525
+35	sp	--------------------------------
+36	a	彟
+36	mn	00005F5F
+36	mx	00005F5F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf32 COLLATE utf32_danish_ci;
 SELECT * FROM v1;
 id	name	val
@@ -3165,6 +4092,169 @@ id	name	val
 32	mn	00000063000000610000006100000009
 32	mx	0000006300000061000000610000FFFF
 32	sp	--------------------------------
+33	a	Х
+33	mn	00000425
+33	mx	00000425
+33	sp	--------------------------------
+34	a	џ
+34	mn	0000045F
+34	mx	0000045F
+34	sp	--------------------------------
+35	a	┥
+35	mn	00002525
+35	mx	00002525
+35	sp	--------------------------------
+36	a	彟
+36	mn	00005F5F
+36	mx	00005F5F
+36	sp	--------------------------------
+ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf32 COLLATE utf32_thai_520_w2;
+SELECT * FROM v1;
+id	name	val
+1	a	
+1	mn	
+1	mx	
+1	sp	--------------------------------
+2	a	_
+2	mn	00000009
+2	mx	0000FFFF
+2	sp	--------------------------------
+3	a	%
+3	mn	00000009000000090000000900000009
+3	mx	0000FFFF0000FFFF0000FFFF0000FFFF
+3	sp	--------------------------------
+4	a	\_
+4	mn	0000005F
+4	mx	0000005F
+4	sp	--------------------------------
+5	a	\%
+5	mn	00000025
+5	mx	00000025
+5	sp	--------------------------------
+6	a	\
+6	mn	0000005C
+6	mx	0000005C
+6	sp	--------------------------------
+7	a	a
+7	mn	00000061
+7	mx	00000061
+7	sp	--------------------------------
+8	a	c
+8	mn	00000063
+8	mx	00000063
+8	sp	--------------------------------
+9	a	a_
+9	mn	0000006100000009
+9	mx	000000610000FFFF
+9	sp	--------------------------------
+10	a	c_
+10	mn	0000006300000009
+10	mx	000000630000FFFF
+10	sp	--------------------------------
+11	a	a%
+11	mn	00000061000000090000000900000009
+11	mx	000000610000FFFF0000FFFF0000FFFF
+11	sp	--------------------------------
+12	a	c%
+12	mn	00000063000000090000000900000009
+12	mx	000000630000FFFF0000FFFF0000FFFF
+12	sp	--------------------------------
+13	a	aa
+13	mn	0000006100000061
+13	mx	0000006100000061
+13	sp	--------------------------------
+14	a	cc
+14	mn	0000006300000063
+14	mx	0000006300000063
+14	sp	--------------------------------
+15	a	ch
+15	mn	0000006300000068
+15	mx	0000006300000068
+15	sp	--------------------------------
+16	a	aa_
+16	mn	000000610000006100000009
+16	mx	00000061000000610000FFFF
+16	sp	--------------------------------
+17	a	cc_
+17	mn	000000630000006300000009
+17	mx	00000063000000630000FFFF
+17	sp	--------------------------------
+18	a	ch_
+18	mn	000000630000006800000009
+18	mx	00000063000000680000FFFF
+18	sp	--------------------------------
+19	a	aa%
+19	mn	00000061000000610000000900000009
+19	mx	00000061000000610000FFFF0000FFFF
+19	sp	--------------------------------
+20	a	cc%
+20	mn	00000063000000630000000900000009
+20	mx	00000063000000630000FFFF0000FFFF
+20	sp	--------------------------------
+21	a	ch%
+21	mn	00000063000000680000000900000009
+21	mx	00000063000000680000FFFF0000FFFF
+21	sp	--------------------------------
+22	a	aaa
+22	mn	000000610000006100000061
+22	mx	000000610000006100000061
+22	sp	--------------------------------
+23	a	ccc
+23	mn	000000630000006300000063
+23	mx	000000630000006300000063
+23	sp	--------------------------------
+24	a	cch
+24	mn	000000630000006300000068
+24	mx	000000630000006300000068
+24	sp	--------------------------------
+25	a	aaa_
+25	mn	00000061000000610000006100000009
+25	mx	0000006100000061000000610000FFFF
+25	sp	--------------------------------
+26	a	ccc_
+26	mn	00000063000000630000006300000009
+26	mx	0000006300000063000000630000FFFF
+26	sp	--------------------------------
+27	a	cch_
+27	mn	00000063000000630000006800000009
+27	mx	0000006300000063000000680000FFFF
+27	sp	--------------------------------
+28	a	aaa%
+28	mn	00000061000000610000006100000009
+28	mx	0000006100000061000000610000FFFF
+28	sp	--------------------------------
+29	a	ccc%
+29	mn	00000063000000630000006300000009
+29	mx	0000006300000063000000630000FFFF
+29	sp	--------------------------------
+30	a	cch%
+30	mn	00000063000000630000006800000009
+30	mx	0000006300000063000000680000FFFF
+30	sp	--------------------------------
+31	a	aaaaaaaaaaaaaaaaaaaa
+31	mn	00000061000000610000006100000061
+31	mx	00000061000000610000006100000061
+31	sp	--------------------------------
+32	a	caaaaaaaaaaaaaaaaaaa
+32	mn	00000063000000610000006100000061
+32	mx	00000063000000610000006100000061
+32	sp	--------------------------------
+33	a	Х
+33	mn	00000425
+33	mx	00000425
+33	sp	--------------------------------
+34	a	џ
+34	mn	0000045F
+34	mx	0000045F
+34	sp	--------------------------------
+35	a	┥
+35	mn	00002525
+35	mx	00002525
+35	sp	--------------------------------
+36	a	彟
+36	mn	00005F5F
+36	mx	00005F5F
+36	sp	--------------------------------
 ALTER TABLE t1 MODIFY a VARCHAR(32) CHARACTER SET utf32 COLLATE utf32_unicode_520_ci;
 SELECT * FROM v1;
 id	name	val
@@ -3296,6 +4386,22 @@ id	name	val
 32	mn	00000063000000610000006100000061
 32	mx	00000063000000610000006100000061
 32	sp	--------------------------------
+33	a	Х
+33	mn	00000425
+33	mx	00000425
+33	sp	--------------------------------
+34	a	џ
+34	mn	0000045F
+34	mx	0000045F
+34	sp	--------------------------------
+35	a	┥
+35	mn	00002525
+35	mx	00002525
+35	sp	--------------------------------
+36	a	彟
+36	mn	00005F5F
+36	mx	00005F5F
+36	sp	--------------------------------
 DROP VIEW v1;
 DROP TABLE t1;
 #
diff --git a/mysql-test/r/ctype_recoding.result b/mysql-test/r/ctype_recoding.result
index 1b3f9f76817..93f1639e88d 100644
--- a/mysql-test/r/ctype_recoding.result
+++ b/mysql-test/r/ctype_recoding.result
@@ -277,9 +277,40 @@ CREATE TABLE t1 ( a VARCHAR(1) );
 INSERT INTO t1 VALUES ('m'),('n');
 CREATE VIEW v1 AS SELECT 'w' ;
 SELECT * FROM t1 WHERE a < ALL ( SELECT * FROM v1 );
-ERROR HY000: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<='
+a
+m
+n
 drop view v1;
 drop table t1;
 SET character_set_connection = default;
 SET optimizer_switch= default;
 #End of 5.3 tests
+#
+# Start of 5.5 tests
+#
+#
+# MDEV-10181 Illegal mix of collation for a field and an ASCII string as a view field
+#
+SET NAMES utf8;
+CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1);
+INSERT INTO t1 VALUES ('A'),('a'),('B'),('b');
+CREATE VIEW v1 AS SELECT 'a';
+SELECT * FROM v1,t1 where t1.a=v1.a;
+a	a
+a	A
+a	a
+DROP VIEW v1;
+DROP TABLE t1;
+SET NAMES utf8;
+CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1);
+INSERT INTO t1 VALUES ('a'),('b'),('c');
+CREATE VIEW v1 AS SELECT 'a' AS a UNION SELECT 'b';
+SELECT * FROM v1,t1 WHERE t1.a=v1.a;
+a	a
+a	a
+b	b
+DROP VIEW v1;
+DROP TABLE t1;
+#
+# End of 5.5 tests
+#
diff --git a/mysql-test/r/ctype_uca.result b/mysql-test/r/ctype_uca.result
index 08d87fa292d..cade3e4cd75 100644
--- a/mysql-test/r/ctype_uca.result
+++ b/mysql-test/r/ctype_uca.result
@@ -2706,6 +2706,261 @@ Z,z,Ź,ź,Ż,ż,Ž,ž
 ǁ
 ǂ
 ǃ
+select group_concat(c1 order by c1) from t1 group by c1 collate utf8_thai_520_w2;
+group_concat(c1 order by c1)
+÷
+×
+A,a
+Á,á
+À,à
+Ă,ă
+Ắ,ắ
+Ằ,ằ
+Ẵ,ẵ
+Ẳ,ẳ
+Â,â
+Ấ,ấ
+Ầ,ầ
+Ẫ,ẫ
+Ẩ,ẩ
+Ǎ,ǎ
+Å,å
+Ǻ,ǻ
+Ä,ä
+Ǟ,ǟ
+Ã,ã
+Ǡ,ǡ
+Ą,ą
+Ā,ā
+Ả,ả
+Ạ,ạ
+Ặ,ặ
+Ậ,ậ
+AA,Aa,aA,aa
+AE,Ae,aE,ae
+Æ,æ
+Ǽ,ǽ
+Ǣ,ǣ
+B,b
+ƀ
+Ɓ
+Ƃ,ƃ
+C,c
+Ć,ć
+Ĉ,ĉ
+Č,č
+Ċ,ċ
+Ç,ç
+CH,Ch,cH,ch
+Ƈ,ƈ
+D,d
+Ď,ď
+Đ,đ
+Ð,ð
+DZ,Dz,dZ,dz,DZ,Dz,dz
+DŽ,Dž,dŽ,dž,DŽ,Dž,dž
+Ɖ
+Ɗ
+Ƌ,ƌ
+E,e
+É,é
+È,è
+Ĕ,ĕ
+Ê,ê
+Ế,ế
+Ề,ề
+Ễ,ễ
+Ể,ể
+Ě,ě
+Ë,ë
+Ẽ,ẽ
+Ė,ė
+Ę,ę
+Ē,ē
+Ẻ,ẻ
+Ẹ,ẹ
+Ệ,ệ
+Ǝ,ǝ
+Ə
+Ɛ
+F,f
+Ƒ,ƒ
+G,g
+Ǵ,ǵ
+Ğ,ğ
+Ĝ,ĝ
+Ǧ,ǧ
+Ġ,ġ
+Ģ,ģ
+Ǥ,ǥ
+Ɠ
+Ɣ
+Ƣ,ƣ
+H,h
+Ĥ,ĥ
+Ħ,ħ
+ƕ,Ƕ
+I,i
+Í,í
+Ì,ì
+Ĭ,ĭ
+Î,î
+Ǐ,ǐ
+Ï,ï
+Ĩ,ĩ
+İ
+Į,į
+Ī,ī
+Ỉ,ỉ
+Ị,ị
+IJ,Ij,iJ,ij,IJ,ij
+ı
+Ɨ
+Ɩ
+J,j
+Ĵ,ĵ
+ǰ
+K,k
+Ǩ,ǩ
+Ķ,ķ
+Ƙ,ƙ
+L,l
+Ĺ,ĺ
+Ľ,ľ
+Ļ,ļ
+Ł,ł
+Ŀ,ŀ
+LJ,Lj,lJ,lj,LJ,Lj,lj
+LL,Ll,lL,ll
+ƚ
+ƛ
+M,m
+N,n
+Ń,ń
+Ǹ,ǹ
+Ň,ň
+Ñ,ñ
+Ņ,ņ
+NJ,Nj,nJ,nj,NJ,Nj,nj
+Ɲ
+ƞ
+Ŋ,ŋ
+O,o
+Ó,ó
+Ò,ò
+Ŏ,ŏ
+Ô,ô
+Ố,ố
+Ồ,ồ
+Ỗ,ỗ
+Ổ,ổ
+Ǒ,ǒ
+Ö,ö
+Ő,ő
+Õ,õ
+Ø,ø
+Ǿ,ǿ
+Ǫ,ǫ
+Ǭ,ǭ
+Ō,ō
+Ỏ,ỏ
+Ơ,ơ
+Ớ,ớ
+Ờ,ờ
+Ỡ,ỡ
+Ở,ở
+Ợ,ợ
+Ọ,ọ
+Ộ,ộ
+OE,Oe,oE,oe
+Œ,œ
+Ɔ
+Ɵ
+P,p
+Ƥ,ƥ
+Q,q
+ĸ
+R,r
+Ŕ,ŕ
+Ř,ř
+Ŗ,ŗ
+RR,Rr,rR,rr
+Ʀ
+S,s
+Ś,ś
+Ŝ,ŝ
+Š,š
+Ş,ş
+ſ
+SS,Ss,sS,ss
+ß
+Ʃ
+ƪ
+T,t
+Ť,ť
+Ţ,ţ
+ƾ
+Ŧ,ŧ
+ƫ
+Ƭ,ƭ
+Ʈ
+U,u
+Ú,ú
+Ù,ù
+Ŭ,ŭ
+Û,û
+Ǔ,ǔ
+Ů,ů
+Ü,ü
+Ǘ,ǘ
+Ǜ,ǜ
+Ǚ,ǚ
+Ǖ,ǖ
+Ű,ű
+Ũ,ũ
+Ų,ų
+Ū,ū
+Ủ,ủ
+Ư,ư
+Ứ,ứ
+Ừ,ừ
+Ữ,ữ
+Ử,ử
+Ự,ự
+Ụ,ụ
+Ɯ
+Ʊ
+V,v
+Ʋ
+W,w
+Ŵ,ŵ
+X,x
+Y,y
+Ý,ý
+Ŷ,ŷ
+ÿ,Ÿ
+Ƴ,ƴ
+Z,z
+Ź,ź
+Ž,ž
+Ż,ż
+ƍ
+Ƶ,ƶ
+Ʒ
+Ǯ,ǯ
+Ƹ,ƹ
+ƺ
+Þ,þ
+ƿ,Ƿ
+ƻ
+Ƨ,ƨ
+Ƽ,ƽ
+Ƅ,ƅ
+ʼn
+ǀ
+ǁ
+ǂ
+ǃ
 ALTER TABLE t1 CONVERT TO CHARACTER SET ucs2 COLLATE ucs2_bin;
 SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_unicode_ci;
 GROUP_CONCAT(c1 ORDER BY c1)
@@ -13724,7 +13979,1393 @@ SELECT * FROM t1 WHERE a='a' AND a=_utf8'a';
 a
 a
 DROP TABLE t1;
+SET NAMES utf8 COLLATE utf8_thai_520_w2;
+#
+# Start of ctype_uca_w2.inc
+#
+SELECT @@collation_connection;
+@@collation_connection
+utf8_thai_520_w2
+SELECT ID, SORTLEN, COLLATION_NAME, CHARACTER_SET_NAME
+FROM INFORMATION_SCHEMA.COLLATIONS
+WHERE COLLATION_NAME LIKE @@collation_connection;
+ID	SORTLEN	COLLATION_NAME	CHARACTER_SET_NAME
+578	4	utf8_thai_520_w2	utf8
+#
+# Testing strnxfrm
+#
+CREATE TABLE t1 AS SELECT SPACE(3) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(3) CHARACTER SET utf8 COLLATE utf8_thai_520_w2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (SPACE(0)),(SPACE(1)),(SPACE(2));
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(8))) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(8)))
+	020A020A020A020A020A020A020A020A00200020002000200020002000200020
+20	020A020A020A020A020A020A020A020A00200020002000200020002000200020
+2020	020A020A020A020A020A020A020A020A00200020002000200020002000200020
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(4))) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(4)))
+	020A020A020A020A0020002000200020
+20	020A020A020A020A0020002000200020
+2020	020A020A020A020A0020002000200020
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 1)) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 1))
+	020A020A020A020A
+20	020A020A020A020A
+2020	020A020A020A020A
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 2)) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 2))
+	0020002000200020
+20	0020002000200020
+2020	0020002000200020
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 3)) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 3))
+	0020002000200020
+20	0020002000200020
+2020	0020002000200020
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT SPACE(10) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(10) CHARACTER SET utf8 COLLATE utf8_thai_520_w2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (_ucs2 0x3400);
+INSERT INTO t1 VALUES (_ucs2 0xF001);
+SELECT HEX(CONVERT(a USING ucs2)) AS ucs2, HEX(a), HEX(WEIGHT_STRING(a)) FROM t1;
+ucs2	HEX(a)	HEX(WEIGHT_STRING(a))
+3400	E39080	FB80B4000020
+F001	EF8081	FBC1F0010020
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT SPACE(10) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(10) CHARACTER SET utf8 COLLATE utf8_thai_520_w2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('A'),('À'),('Á'),('Â'),('Ã'),('Ä'),('Å');
+SELECT a, HEX(WEIGHT_STRING(a LEVEL 2)) FROM t1 ORDER BY a;
+a	HEX(WEIGHT_STRING(a LEVEL 2))
+A	0020
+Á	00200032
+À	00200035
+Â	0020003C
+Å	00200043
+Ä	00200047
+Ã	0020004E
+SELECT a, HEX(WEIGHT_STRING(a LEVEL 2)) FROM t1 ORDER BY a DESC;
+a	HEX(WEIGHT_STRING(a LEVEL 2))
+Ã	0020004E
+Ä	00200047
+Å	00200043
+Â	0020003C
+À	00200035
+Á	00200032
+A	0020
+SET @backup_character_set_connection=@@character_set_connection;
+SET @backup_collation_connection=@@collation_connection;
+SET NAMES utf8;
+SET @stmt= CONCAT('ALTER TABLE t1 MODIFY a CHAR(10)' COLLATE utf8_bin,
+' CHARACTER SET ',  @backup_character_set_connection,
+' COLLATE ',  @backup_collation_connection);
+PREPARE stmt FROM @stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+SET collation_connection=@backup_collation_connection;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` char(10) CHARACTER SET utf8 COLLATE utf8_thai_520_w2 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT a, HEX(WEIGHT_STRING(a LEVEL 2)) FROM t1 ORDER BY a;
+a	HEX(WEIGHT_STRING(a LEVEL 2))
+A	0020
+Á	00200032
+À	00200035
+Â	0020003C
+Å	00200043
+Ä	00200047
+Ã	0020004E
+SELECT a, HEX(WEIGHT_STRING(a LEVEL 2)) FROM t1 ORDER BY a DESC;
+a	HEX(WEIGHT_STRING(a LEVEL 2))
+Ã	0020004E
+Ä	00200047
+Å	00200043
+Â	0020003C
+À	00200035
+Á	00200032
+A	0020
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT SPACE(10) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(10) CHARACTER SET utf8 COLLATE utf8_thai_520_w2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('cota'),('cote'),('cotz');
+INSERT INTO t1 VALUES ('coté'),('côte'),('côté'),('cotë'),('côtë');
+SELECT * FROM t1 ORDER BY a;
+a
+cota
+cote
+coté
+cotë
+côte
+côté
+côtë
+cotz
+SELECT * FROM t1 ORDER BY a DESC;
+a
+cotz
+côtë
+côté
+côte
+cotë
+coté
+cote
+cota
+DROP TABLE t1;
+#
+# End of ctype_uca_w2.inc
+#
+#
+# Start of ctype_thai.inc
+#
+CREATE TABLE t1 (a VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_bin);
+INSERT INTO t1 VALUES ('-กระแย่ง');
+INSERT INTO t1 VALUES ('กก');
+INSERT INTO t1 VALUES ('กราบ');
+INSERT INTO t1 VALUES ('ข่าง');
+INSERT INTO t1 VALUES ('ข้าง');
+INSERT INTO t1 VALUES ('ข้างกระดาน');
+INSERT INTO t1 VALUES ('ข้างขึ้น');
+INSERT INTO t1 VALUES ('ข้างควาย');
+INSERT INTO t1 VALUES ('ข้างเงิน');
+INSERT INTO t1 VALUES ('ข้างแรม');
+INSERT INTO t1 VALUES ('ข้างออก');
+INSERT INTO t1 VALUES ('ข้างๆ');
+INSERT INTO t1 VALUES ('ข้างๆ คูๆ');
+INSERT INTO t1 VALUES ('ขาง');
+INSERT INTO t1 VALUES ('แข็ง');
+INSERT INTO t1 VALUES ('แข่ง');
+INSERT INTO t1 VALUES ('แข่งขัน');
+INSERT INTO t1 VALUES ('แข้ง');
+INSERT INTO t1 VALUES ('แข้งขวา');
+INSERT INTO t1 VALUES ('แข็งขัน');
+INSERT INTO t1 VALUES ('ทูลเกล้า');
+INSERT INTO t1 VALUES ('ทูลเกล้าทูลกระหม่อม');
+INSERT INTO t1 VALUES ('ทูลเกล้าฯ');
+INSERT INTO t1 VALUES ('บุญญา');
+INSERT INTO t1 VALUES ('บุญ-หลง');
+INSERT INTO t1 VALUES ('บุญหลง');
+INSERT INTO t1 VALUES ('ป่า');
+INSERT INTO t1 VALUES ('ป่าน');
+INSERT INTO t1 VALUES ('ป้า');
+INSERT INTO t1 VALUES ('ป้าน');
+INSERT INTO t1 VALUES ('ป๊า');
+INSERT INTO t1 VALUES ('ป๊าน');
+INSERT INTO t1 VALUES ('ป๋า');
+INSERT INTO t1 VALUES ('ป๋าน');
+INSERT INTO t1 VALUES ('ปา');
+INSERT INTO t1 VALUES ('ปาน');
+INSERT INTO t1 VALUES ('แป้ง');
+INSERT INTO t1 VALUES ('พณิชย์');
+INSERT INTO t1 VALUES ('ม้า');
+INSERT INTO t1 VALUES ('ฯพณฯ');
+INSERT INTO t1 VALUES ('A');
+INSERT INTO t1 VALUES ('a');
+INSERT INTO t1 VALUES ('a\'');
+INSERT INTO t1 VALUES ('A-');
+INSERT INTO t1 VALUES ('a-');
+INSERT INTO t1 VALUES ('-a');
+INSERT INTO t1 VALUES ('A.');
+INSERT INTO t1 VALUES ('a.');
+INSERT INTO t1 VALUES ('A-1');
+INSERT INTO t1 VALUES ('aa');
+INSERT INTO t1 VALUES ('AA');
+INSERT INTO t1 VALUES ('A.A.');
+INSERT INTO t1 VALUES ('a.a.');
+INSERT INTO t1 VALUES ('AAA');
+INSERT INTO t1 VALUES ('A.A.A.');
+INSERT INTO t1 VALUES ('AAAA');
+INSERT INTO t1 VALUES ('A.A.A.L.');
+INSERT INTO t1 VALUES ('A.A.A.S.');
+INSERT INTO t1 VALUES ('Aachen');
+INSERT INTO t1 VALUES ('A.A.E.');
+INSERT INTO t1 VALUES ('A.Ae.E.');
+INSERT INTO t1 VALUES ('A.A.E.E.');
+INSERT INTO t1 VALUES ('AAES');
+INSERT INTO t1 VALUES ('AAF');
+INSERT INTO t1 VALUES ('A.Agr');
+INSERT INTO t1 VALUES ('aah');
+INSERT INTO t1 VALUES ('@@@@@');
+INSERT INTO t1 VALUES ('0000');
+INSERT INTO t1 VALUES ('9999');
+INSERT INTO t1 VALUES ('Aalborg');
+INSERT INTO t1 VALUES ('aide');
+INSERT INTO t1 VALUES ('air');
+INSERT INTO t1 VALUES ('@@@air');
+INSERT INTO t1 VALUES ('air@@@');
+INSERT INTO t1 VALUES ('C.A.F');
+INSERT INTO t1 VALUES ('Canon');
+INSERT INTO t1 VALUES ('coop');
+INSERT INTO t1 VALUES ('co-op');
+INSERT INTO t1 VALUES ('COOP');
+INSERT INTO t1 VALUES ('CO-OP');
+INSERT INTO t1 VALUES ('Copenhegen');
+INSERT INTO t1 VALUES ('McArthur');
+INSERT INTO t1 VALUES ('Mc Arthur');
+INSERT INTO t1 VALUES ('Mc Mahon');
+INSERT INTO t1 VALUES ('vice-president');
+INSERT INTO t1 VALUES ('vice versa');
+INSERT INTO t1 VALUES ('vice-versa');
+INSERT INTO t1 VALUES ('10 ลิตร');
+INSERT INTO t1 VALUES ('๑๐ ลิตร');
+INSERT INTO t1 VALUES ('10 litre');
+INSERT INTO t1 VALUES ('10 litre (10 ลิตร)');
+INSERT INTO t1 VALUES ('10 ลิตร (10 litre)');
+INSERT INTO t1 VALUES ('10 litre (๑๐ ลิตร)');
+INSERT INTO t1 VALUES ('๑๐ ลิตร (10 litre)');
+INSERT INTO t1 VALUES ('๑๐ ลิตร [10 litre]');
+INSERT INTO t1 VALUES ('๑๐ ลิตร {10 litre}');
+ALTER TABLE t1 ORDER BY a;
+SET @backup_character_set_connection=@@character_set_connection;
+SET @backup_collation_connection=@@collation_connection;
+SET NAMES utf8;
+SET @stmt= CONCAT('ALTER TABLE t1 MODIFY a VARCHAR(30)',
+' CHARACTER SET ', @backup_character_set_connection,
+' COLLATE ', @backup_collation_connection,
+' , ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY'
+                  ' , ADD KEY a_id (a, id)');
+PREPARE stmt FROM @stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+SET collation_connection=@backup_collation_connection;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(30) CHARACTER SET utf8 COLLATE utf8_thai_520_w2 DEFAULT NULL,
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`),
+  KEY `a_id` (`a`,`id`)
+) ENGINE=MyISAM AUTO_INCREMENT=97 DEFAULT CHARSET=latin1
+#
+# Ascending sort, using filesort
+#
+EXPLAIN SELECT a FROM t1 ORDER BY a, BINARY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a_id	97	NULL	96	Using index; Using filesort
+SELECT a FROM t1 ORDER BY a, BINARY a;
+a
+-a
+-กระแย่ง
+@@@@@
+@@@air
+0000
+10 litre
+10 litre (10 ลิตร)
+10 litre (๑๐ ลิตร)
+10 ลิตร
+๑๐ ลิตร
+10 ลิตร (10 litre)
+๑๐ ลิตร (10 litre)
+๑๐ ลิตร [10 litre]
+๑๐ ลิตร {10 litre}
+9999
+A
+a
+A-
+a-
+A-1
+A.
+a.
+A.A.
+a.a.
+A.A.A.
+A.A.A.L.
+A.A.A.S.
+A.A.E.
+A.A.E.E.
+A.Ae.E.
+A.Agr
+a'
+AA
+aa
+AAA
+AAAA
+Aachen
+AAES
+AAF
+aah
+Aalborg
+aide
+air
+air@@@
+C.A.F
+Canon
+CO-OP
+co-op
+COOP
+coop
+Copenhegen
+Mc Arthur
+Mc Mahon
+McArthur
+vice versa
+vice-president
+vice-versa
+กก
+กราบ
+ขาง
+ข่าง
+ข้าง
+ข้างๆ
+ข้างๆ คูๆ
+ข้างกระดาน
+ข้างขึ้น
+ข้างควาย
+ข้างเงิน
+ข้างแรม
+ข้างออก
+แข็ง
+แข่ง
+แข้ง
+แข้งขวา
+แข็งขัน
+แข่งขัน
+ทูลเกล้า
+ทูลเกล้าทูลกระหม่อม
+ทูลเกล้าฯ
+บุญ-หลง
+บุญญา
+บุญหลง
+ปา
+ป่า
+ป้า
+ป๊า
+ป๋า
+ปาน
+ป่าน
+ป้าน
+ป๊าน
+ป๋าน
+แป้ง
+พณิชย์
+ม้า
+ฯพณฯ
+#
+# Descending sort, using filesort
+#
+EXPLAIN SELECT a FROM t1 ORDER BY a DESC, BINARY a DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a_id	97	NULL	96	Using index; Using filesort
+SELECT a FROM t1 ORDER BY a DESC, BINARY a DESC;
+a
+ฯพณฯ
+ม้า
+พณิชย์
+แป้ง
+ป๋าน
+ป๊าน
+ป้าน
+ป่าน
+ปาน
+ป๋า
+ป๊า
+ป้า
+ป่า
+ปา
+บุญหลง
+บุญญา
+บุญ-หลง
+ทูลเกล้าฯ
+ทูลเกล้าทูลกระหม่อม
+ทูลเกล้า
+แข่งขัน
+แข็งขัน
+แข้งขวา
+แข้ง
+แข่ง
+แข็ง
+ข้างออก
+ข้างแรม
+ข้างเงิน
+ข้างควาย
+ข้างขึ้น
+ข้างกระดาน
+ข้างๆ คูๆ
+ข้างๆ
+ข้าง
+ข่าง
+ขาง
+กราบ
+กก
+vice-versa
+vice-president
+vice versa
+McArthur
+Mc Mahon
+Mc Arthur
+Copenhegen
+coop
+COOP
+co-op
+CO-OP
+Canon
+C.A.F
+air@@@
+air
+aide
+Aalborg
+aah
+AAF
+AAES
+Aachen
+AAAA
+AAA
+aa
+AA
+a'
+A.Agr
+A.Ae.E.
+A.A.E.E.
+A.A.E.
+A.A.A.S.
+A.A.A.L.
+A.A.A.
+a.a.
+A.A.
+a.
+A.
+A-1
+a-
+A-
+a
+A
+9999
+๑๐ ลิตร {10 litre}
+๑๐ ลิตร [10 litre]
+๑๐ ลิตร (10 litre)
+10 ลิตร (10 litre)
+๑๐ ลิตร
+10 ลิตร
+10 litre (๑๐ ลิตร)
+10 litre (10 ลิตร)
+10 litre
+0000
+@@@air
+@@@@@
+-กระแย่ง
+-a
+#
+# Ascending sort, using index
+#
+EXPLAIN SELECT a FROM t1 ORDER BY a, id;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a_id	97	NULL	96	Using index
+SELECT a FROM t1 ORDER BY a, id;
+a
+-a
+-กระแย่ง
+@@@@@
+@@@air
+0000
+10 litre
+10 litre (10 ลิตร)
+10 litre (๑๐ ลิตร)
+10 ลิตร
+๑๐ ลิตร
+10 ลิตร (10 litre)
+๑๐ ลิตร (10 litre)
+๑๐ ลิตร [10 litre]
+๑๐ ลิตร {10 litre}
+9999
+A
+a
+A-
+a-
+A-1
+A.
+a.
+A.A.
+a.a.
+A.A.A.
+A.A.A.L.
+A.A.A.S.
+A.A.E.
+A.A.E.E.
+A.Ae.E.
+A.Agr
+a'
+AA
+aa
+AAA
+AAAA
+Aachen
+AAES
+AAF
+aah
+Aalborg
+aide
+air
+air@@@
+C.A.F
+Canon
+CO-OP
+co-op
+COOP
+coop
+Copenhegen
+Mc Arthur
+Mc Mahon
+McArthur
+vice versa
+vice-president
+vice-versa
+กก
+กราบ
+ขาง
+ข่าง
+ข้าง
+ข้างๆ
+ข้างๆ คูๆ
+ข้างกระดาน
+ข้างขึ้น
+ข้างควาย
+ข้างเงิน
+ข้างแรม
+ข้างออก
+แข็ง
+แข่ง
+แข้ง
+แข้งขวา
+แข็งขัน
+แข่งขัน
+ทูลเกล้า
+ทูลเกล้าทูลกระหม่อม
+ทูลเกล้าฯ
+บุญ-หลง
+บุญญา
+บุญหลง
+ปา
+ป่า
+ป้า
+ป๊า
+ป๋า
+ปาน
+ป่าน
+ป้าน
+ป๊าน
+ป๋าน
+แป้ง
+พณิชย์
+ม้า
+ฯพณฯ
+#
+# Descending sort, using index
+#
+EXPLAIN SELECT a FROM t1 ORDER BY a DESC, id DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a_id	97	NULL	96	Using index
+SELECT a FROM t1 ORDER BY a DESC, id DESC;
+a
+ฯพณฯ
+ม้า
+พณิชย์
+แป้ง
+ป๋าน
+ป๊าน
+ป้าน
+ป่าน
+ปาน
+ป๋า
+ป๊า
+ป้า
+ป่า
+ปา
+บุญหลง
+บุญญา
+บุญ-หลง
+ทูลเกล้าฯ
+ทูลเกล้าทูลกระหม่อม
+ทูลเกล้า
+แข่งขัน
+แข็งขัน
+แข้งขวา
+แข้ง
+แข่ง
+แข็ง
+ข้างออก
+ข้างแรม
+ข้างเงิน
+ข้างควาย
+ข้างขึ้น
+ข้างกระดาน
+ข้างๆ คูๆ
+ข้างๆ
+ข้าง
+ข่าง
+ขาง
+กราบ
+กก
+vice-versa
+vice-president
+vice versa
+McArthur
+Mc Mahon
+Mc Arthur
+Copenhegen
+coop
+COOP
+co-op
+CO-OP
+Canon
+C.A.F
+air@@@
+air
+aide
+Aalborg
+aah
+AAF
+AAES
+Aachen
+AAAA
+AAA
+aa
+AA
+a'
+A.Agr
+A.Ae.E.
+A.A.E.E.
+A.A.E.
+A.A.A.S.
+A.A.A.L.
+A.A.A.
+a.a.
+A.A.
+a.
+A.
+A-1
+a-
+A-
+a
+A
+9999
+๑๐ ลิตร {10 litre}
+๑๐ ลิตร [10 litre]
+๑๐ ลิตร (10 litre)
+10 ลิตร (10 litre)
+๑๐ ลิตร
+10 ลิตร
+10 litre (๑๐ ลิตร)
+10 litre (10 ลิตร)
+10 litre
+0000
+@@@air
+@@@@@
+-กระแย่ง
+-a
+DROP TABLE t1;
+#
+# End of ctype_thai.inc
+#
+SET NAMES utf8;
+SET collation_connection=ucs2_thai_520_w2;
+#
+# Start of ctype_uca_w2.inc
+#
+SELECT @@collation_connection;
+@@collation_connection
+ucs2_thai_520_w2
+SELECT ID, SORTLEN, COLLATION_NAME, CHARACTER_SET_NAME
+FROM INFORMATION_SCHEMA.COLLATIONS
+WHERE COLLATION_NAME LIKE @@collation_connection;
+ID	SORTLEN	COLLATION_NAME	CHARACTER_SET_NAME
+642	4	ucs2_thai_520_w2	ucs2
+#
+# Testing strnxfrm
+#
+CREATE TABLE t1 AS SELECT SPACE(3) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(3) CHARACTER SET ucs2 COLLATE ucs2_thai_520_w2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (SPACE(0)),(SPACE(1)),(SPACE(2));
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(8))) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(8)))
+	020A020A020A020A020A020A020A020A00200020002000200020002000200020
+0020	020A020A020A020A020A020A020A020A00200020002000200020002000200020
+00200020	020A020A020A020A020A020A020A020A00200020002000200020002000200020
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(4))) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(4)))
+	020A020A020A020A0020002000200020
+0020	020A020A020A020A0020002000200020
+00200020	020A020A020A020A0020002000200020
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 1)) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 1))
+	020A020A020A020A
+0020	020A020A020A020A
+00200020	020A020A020A020A
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 2)) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 2))
+	0020002000200020
+0020	0020002000200020
+00200020	0020002000200020
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 3)) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 3))
+	0020002000200020
+0020	0020002000200020
+00200020	0020002000200020
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT SPACE(10) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(10) CHARACTER SET ucs2 COLLATE ucs2_thai_520_w2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (_ucs2 0x3400);
+INSERT INTO t1 VALUES (_ucs2 0xF001);
+SELECT HEX(CONVERT(a USING ucs2)) AS ucs2, HEX(a), HEX(WEIGHT_STRING(a)) FROM t1;
+ucs2	HEX(a)	HEX(WEIGHT_STRING(a))
+3400	3400	FB80B4000020
+F001	F001	FBC1F0010020
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT SPACE(10) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(10) CHARACTER SET ucs2 COLLATE ucs2_thai_520_w2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('A'),('À'),('Á'),('Â'),('Ã'),('Ä'),('Å');
+SELECT a, HEX(WEIGHT_STRING(a LEVEL 2)) FROM t1 ORDER BY a;
+a	HEX(WEIGHT_STRING(a LEVEL 2))
+A	0020
+Á	00200032
+À	00200035
+Â	0020003C
+Å	00200043
+Ä	00200047
+Ã	0020004E
+SELECT a, HEX(WEIGHT_STRING(a LEVEL 2)) FROM t1 ORDER BY a DESC;
+a	HEX(WEIGHT_STRING(a LEVEL 2))
+Ã	0020004E
+Ä	00200047
+Å	00200043
+Â	0020003C
+À	00200035
+Á	00200032
+A	0020
+SET @backup_character_set_connection=@@character_set_connection;
+SET @backup_collation_connection=@@collation_connection;
+SET NAMES utf8;
+SET @stmt= CONCAT('ALTER TABLE t1 MODIFY a CHAR(10)' COLLATE utf8_bin,
+' CHARACTER SET ',  @backup_character_set_connection,
+' COLLATE ',  @backup_collation_connection);
+PREPARE stmt FROM @stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+SET collation_connection=@backup_collation_connection;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` char(10) CHARACTER SET ucs2 COLLATE ucs2_thai_520_w2 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT a, HEX(WEIGHT_STRING(a LEVEL 2)) FROM t1 ORDER BY a;
+a	HEX(WEIGHT_STRING(a LEVEL 2))
+A	0020
+Á	00200032
+À	00200035
+Â	0020003C
+Å	00200043
+Ä	00200047
+Ã	0020004E
+SELECT a, HEX(WEIGHT_STRING(a LEVEL 2)) FROM t1 ORDER BY a DESC;
+a	HEX(WEIGHT_STRING(a LEVEL 2))
+Ã	0020004E
+Ä	00200047
+Å	00200043
+Â	0020003C
+À	00200035
+Á	00200032
+A	0020
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT SPACE(10) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(10) CHARACTER SET ucs2 COLLATE ucs2_thai_520_w2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('cota'),('cote'),('cotz');
+INSERT INTO t1 VALUES ('coté'),('côte'),('côté'),('cotë'),('côtë');
+SELECT * FROM t1 ORDER BY a;
+a
+cota
+cote
+coté
+cotë
+côte
+côté
+côtë
+cotz
+SELECT * FROM t1 ORDER BY a DESC;
+a
+cotz
+côtë
+côté
+côte
+cotë
+coté
+cote
+cota
+DROP TABLE t1;
+#
+# End of ctype_uca_w2.inc
+#
+#
+# Start of ctype_thai.inc
+#
+CREATE TABLE t1 (a VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_bin);
+INSERT INTO t1 VALUES ('-กระแย่ง');
+INSERT INTO t1 VALUES ('กก');
+INSERT INTO t1 VALUES ('กราบ');
+INSERT INTO t1 VALUES ('ข่าง');
+INSERT INTO t1 VALUES ('ข้าง');
+INSERT INTO t1 VALUES ('ข้างกระดาน');
+INSERT INTO t1 VALUES ('ข้างขึ้น');
+INSERT INTO t1 VALUES ('ข้างควาย');
+INSERT INTO t1 VALUES ('ข้างเงิน');
+INSERT INTO t1 VALUES ('ข้างแรม');
+INSERT INTO t1 VALUES ('ข้างออก');
+INSERT INTO t1 VALUES ('ข้างๆ');
+INSERT INTO t1 VALUES ('ข้างๆ คูๆ');
+INSERT INTO t1 VALUES ('ขาง');
+INSERT INTO t1 VALUES ('แข็ง');
+INSERT INTO t1 VALUES ('แข่ง');
+INSERT INTO t1 VALUES ('แข่งขัน');
+INSERT INTO t1 VALUES ('แข้ง');
+INSERT INTO t1 VALUES ('แข้งขวา');
+INSERT INTO t1 VALUES ('แข็งขัน');
+INSERT INTO t1 VALUES ('ทูลเกล้า');
+INSERT INTO t1 VALUES ('ทูลเกล้าทูลกระหม่อม');
+INSERT INTO t1 VALUES ('ทูลเกล้าฯ');
+INSERT INTO t1 VALUES ('บุญญา');
+INSERT INTO t1 VALUES ('บุญ-หลง');
+INSERT INTO t1 VALUES ('บุญหลง');
+INSERT INTO t1 VALUES ('ป่า');
+INSERT INTO t1 VALUES ('ป่าน');
+INSERT INTO t1 VALUES ('ป้า');
+INSERT INTO t1 VALUES ('ป้าน');
+INSERT INTO t1 VALUES ('ป๊า');
+INSERT INTO t1 VALUES ('ป๊าน');
+INSERT INTO t1 VALUES ('ป๋า');
+INSERT INTO t1 VALUES ('ป๋าน');
+INSERT INTO t1 VALUES ('ปา');
+INSERT INTO t1 VALUES ('ปาน');
+INSERT INTO t1 VALUES ('แป้ง');
+INSERT INTO t1 VALUES ('พณิชย์');
+INSERT INTO t1 VALUES ('ม้า');
+INSERT INTO t1 VALUES ('ฯพณฯ');
+INSERT INTO t1 VALUES ('A');
+INSERT INTO t1 VALUES ('a');
+INSERT INTO t1 VALUES ('a\'');
+INSERT INTO t1 VALUES ('A-');
+INSERT INTO t1 VALUES ('a-');
+INSERT INTO t1 VALUES ('-a');
+INSERT INTO t1 VALUES ('A.');
+INSERT INTO t1 VALUES ('a.');
+INSERT INTO t1 VALUES ('A-1');
+INSERT INTO t1 VALUES ('aa');
+INSERT INTO t1 VALUES ('AA');
+INSERT INTO t1 VALUES ('A.A.');
+INSERT INTO t1 VALUES ('a.a.');
+INSERT INTO t1 VALUES ('AAA');
+INSERT INTO t1 VALUES ('A.A.A.');
+INSERT INTO t1 VALUES ('AAAA');
+INSERT INTO t1 VALUES ('A.A.A.L.');
+INSERT INTO t1 VALUES ('A.A.A.S.');
+INSERT INTO t1 VALUES ('Aachen');
+INSERT INTO t1 VALUES ('A.A.E.');
+INSERT INTO t1 VALUES ('A.Ae.E.');
+INSERT INTO t1 VALUES ('A.A.E.E.');
+INSERT INTO t1 VALUES ('AAES');
+INSERT INTO t1 VALUES ('AAF');
+INSERT INTO t1 VALUES ('A.Agr');
+INSERT INTO t1 VALUES ('aah');
+INSERT INTO t1 VALUES ('@@@@@');
+INSERT INTO t1 VALUES ('0000');
+INSERT INTO t1 VALUES ('9999');
+INSERT INTO t1 VALUES ('Aalborg');
+INSERT INTO t1 VALUES ('aide');
+INSERT INTO t1 VALUES ('air');
+INSERT INTO t1 VALUES ('@@@air');
+INSERT INTO t1 VALUES ('air@@@');
+INSERT INTO t1 VALUES ('C.A.F');
+INSERT INTO t1 VALUES ('Canon');
+INSERT INTO t1 VALUES ('coop');
+INSERT INTO t1 VALUES ('co-op');
+INSERT INTO t1 VALUES ('COOP');
+INSERT INTO t1 VALUES ('CO-OP');
+INSERT INTO t1 VALUES ('Copenhegen');
+INSERT INTO t1 VALUES ('McArthur');
+INSERT INTO t1 VALUES ('Mc Arthur');
+INSERT INTO t1 VALUES ('Mc Mahon');
+INSERT INTO t1 VALUES ('vice-president');
+INSERT INTO t1 VALUES ('vice versa');
+INSERT INTO t1 VALUES ('vice-versa');
+INSERT INTO t1 VALUES ('10 ลิตร');
+INSERT INTO t1 VALUES ('๑๐ ลิตร');
+INSERT INTO t1 VALUES ('10 litre');
+INSERT INTO t1 VALUES ('10 litre (10 ลิตร)');
+INSERT INTO t1 VALUES ('10 ลิตร (10 litre)');
+INSERT INTO t1 VALUES ('10 litre (๑๐ ลิตร)');
+INSERT INTO t1 VALUES ('๑๐ ลิตร (10 litre)');
+INSERT INTO t1 VALUES ('๑๐ ลิตร [10 litre]');
+INSERT INTO t1 VALUES ('๑๐ ลิตร {10 litre}');
+ALTER TABLE t1 ORDER BY a;
+SET @backup_character_set_connection=@@character_set_connection;
+SET @backup_collation_connection=@@collation_connection;
 SET NAMES utf8;
+SET @stmt= CONCAT('ALTER TABLE t1 MODIFY a VARCHAR(30)',
+' CHARACTER SET ', @backup_character_set_connection,
+' COLLATE ', @backup_collation_connection,
+' , ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY'
+                  ' , ADD KEY a_id (a, id)');
+PREPARE stmt FROM @stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+SET collation_connection=@backup_collation_connection;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(30) CHARACTER SET ucs2 COLLATE ucs2_thai_520_w2 DEFAULT NULL,
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`),
+  KEY `a_id` (`a`,`id`)
+) ENGINE=MyISAM AUTO_INCREMENT=97 DEFAULT CHARSET=latin1
+#
+# Ascending sort, using filesort
+#
+EXPLAIN SELECT a FROM t1 ORDER BY a, BINARY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a_id	67	NULL	96	Using index; Using filesort
+SELECT a FROM t1 ORDER BY a, BINARY a;
+a
+-a
+-กระแย่ง
+@@@@@
+@@@air
+0000
+10 litre
+10 litre (10 ลิตร)
+10 litre (๑๐ ลิตร)
+10 ลิตร
+๑๐ ลิตร
+10 ลิตร (10 litre)
+๑๐ ลิตร (10 litre)
+๑๐ ลิตร [10 litre]
+๑๐ ลิตร {10 litre}
+9999
+A
+a
+A-
+a-
+A-1
+A.
+a.
+A.A.
+a.a.
+A.A.A.
+A.A.A.L.
+A.A.A.S.
+A.A.E.
+A.A.E.E.
+A.Ae.E.
+A.Agr
+a'
+AA
+aa
+AAA
+AAAA
+Aachen
+AAES
+AAF
+aah
+Aalborg
+aide
+air
+air@@@
+C.A.F
+Canon
+CO-OP
+co-op
+COOP
+coop
+Copenhegen
+Mc Arthur
+Mc Mahon
+McArthur
+vice versa
+vice-president
+vice-versa
+กก
+กราบ
+ขาง
+ข่าง
+ข้าง
+ข้างๆ
+ข้างๆ คูๆ
+ข้างกระดาน
+ข้างขึ้น
+ข้างควาย
+ข้างเงิน
+ข้างแรม
+ข้างออก
+แข็ง
+แข่ง
+แข้ง
+แข้งขวา
+แข็งขัน
+แข่งขัน
+ทูลเกล้า
+ทูลเกล้าทูลกระหม่อม
+ทูลเกล้าฯ
+บุญ-หลง
+บุญญา
+บุญหลง
+ปา
+ป่า
+ป้า
+ป๊า
+ป๋า
+ปาน
+ป่าน
+ป้าน
+ป๊าน
+ป๋าน
+แป้ง
+พณิชย์
+ม้า
+ฯพณฯ
+#
+# Descending sort, using filesort
+#
+EXPLAIN SELECT a FROM t1 ORDER BY a DESC, BINARY a DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a_id	67	NULL	96	Using index; Using filesort
+SELECT a FROM t1 ORDER BY a DESC, BINARY a DESC;
+a
+ฯพณฯ
+ม้า
+พณิชย์
+แป้ง
+ป๋าน
+ป๊าน
+ป้าน
+ป่าน
+ปาน
+ป๋า
+ป๊า
+ป้า
+ป่า
+ปา
+บุญหลง
+บุญญา
+บุญ-หลง
+ทูลเกล้าฯ
+ทูลเกล้าทูลกระหม่อม
+ทูลเกล้า
+แข่งขัน
+แข็งขัน
+แข้งขวา
+แข้ง
+แข่ง
+แข็ง
+ข้างออก
+ข้างแรม
+ข้างเงิน
+ข้างควาย
+ข้างขึ้น
+ข้างกระดาน
+ข้างๆ คูๆ
+ข้างๆ
+ข้าง
+ข่าง
+ขาง
+กราบ
+กก
+vice-versa
+vice-president
+vice versa
+McArthur
+Mc Mahon
+Mc Arthur
+Copenhegen
+coop
+COOP
+co-op
+CO-OP
+Canon
+C.A.F
+air@@@
+air
+aide
+Aalborg
+aah
+AAF
+AAES
+Aachen
+AAAA
+AAA
+aa
+AA
+a'
+A.Agr
+A.Ae.E.
+A.A.E.E.
+A.A.E.
+A.A.A.S.
+A.A.A.L.
+A.A.A.
+a.a.
+A.A.
+a.
+A.
+A-1
+a-
+A-
+a
+A
+9999
+๑๐ ลิตร {10 litre}
+๑๐ ลิตร [10 litre]
+๑๐ ลิตร (10 litre)
+10 ลิตร (10 litre)
+๑๐ ลิตร
+10 ลิตร
+10 litre (๑๐ ลิตร)
+10 litre (10 ลิตร)
+10 litre
+0000
+@@@air
+@@@@@
+-กระแย่ง
+-a
+#
+# Ascending sort, using index
+#
+EXPLAIN SELECT a FROM t1 ORDER BY a, id;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a_id	67	NULL	96	Using index
+SELECT a FROM t1 ORDER BY a, id;
+a
+-a
+-กระแย่ง
+@@@@@
+@@@air
+0000
+10 litre
+10 litre (10 ลิตร)
+10 litre (๑๐ ลิตร)
+10 ลิตร
+๑๐ ลิตร
+10 ลิตร (10 litre)
+๑๐ ลิตร (10 litre)
+๑๐ ลิตร [10 litre]
+๑๐ ลิตร {10 litre}
+9999
+A
+a
+A-
+a-
+A-1
+A.
+a.
+A.A.
+a.a.
+A.A.A.
+A.A.A.L.
+A.A.A.S.
+A.A.E.
+A.A.E.E.
+A.Ae.E.
+A.Agr
+a'
+AA
+aa
+AAA
+AAAA
+Aachen
+AAES
+AAF
+aah
+Aalborg
+aide
+air
+air@@@
+C.A.F
+Canon
+CO-OP
+co-op
+COOP
+coop
+Copenhegen
+Mc Arthur
+Mc Mahon
+McArthur
+vice versa
+vice-president
+vice-versa
+กก
+กราบ
+ขาง
+ข่าง
+ข้าง
+ข้างๆ
+ข้างๆ คูๆ
+ข้างกระดาน
+ข้างขึ้น
+ข้างควาย
+ข้างเงิน
+ข้างแรม
+ข้างออก
+แข็ง
+แข่ง
+แข้ง
+แข้งขวา
+แข็งขัน
+แข่งขัน
+ทูลเกล้า
+ทูลเกล้าทูลกระหม่อม
+ทูลเกล้าฯ
+บุญ-หลง
+บุญญา
+บุญหลง
+ปา
+ป่า
+ป้า
+ป๊า
+ป๋า
+ปาน
+ป่าน
+ป้าน
+ป๊าน
+ป๋าน
+แป้ง
+พณิชย์
+ม้า
+ฯพณฯ
+#
+# Descending sort, using index
+#
+EXPLAIN SELECT a FROM t1 ORDER BY a DESC, id DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a_id	67	NULL	96	Using index
+SELECT a FROM t1 ORDER BY a DESC, id DESC;
+a
+ฯพณฯ
+ม้า
+พณิชย์
+แป้ง
+ป๋าน
+ป๊าน
+ป้าน
+ป่าน
+ปาน
+ป๋า
+ป๊า
+ป้า
+ป่า
+ปา
+บุญหลง
+บุญญา
+บุญ-หลง
+ทูลเกล้าฯ
+ทูลเกล้าทูลกระหม่อม
+ทูลเกล้า
+แข่งขัน
+แข็งขัน
+แข้งขวา
+แข้ง
+แข่ง
+แข็ง
+ข้างออก
+ข้างแรม
+ข้างเงิน
+ข้างควาย
+ข้างขึ้น
+ข้างกระดาน
+ข้างๆ คูๆ
+ข้างๆ
+ข้าง
+ข่าง
+ขาง
+กราบ
+กก
+vice-versa
+vice-president
+vice versa
+McArthur
+Mc Mahon
+Mc Arthur
+Copenhegen
+coop
+COOP
+co-op
+CO-OP
+Canon
+C.A.F
+air@@@
+air
+aide
+Aalborg
+aah
+AAF
+AAES
+Aachen
+AAAA
+AAA
+aa
+AA
+a'
+A.Agr
+A.Ae.E.
+A.A.E.E.
+A.A.E.
+A.A.A.S.
+A.A.A.L.
+A.A.A.
+a.a.
+A.A.
+a.
+A.
+A-1
+a-
+A-
+a
+A
+9999
+๑๐ ลิตร {10 litre}
+๑๐ ลิตร [10 litre]
+๑๐ ลิตร (10 litre)
+10 ลิตร (10 litre)
+๑๐ ลิตร
+10 ลิตร
+10 litre (๑๐ ลิตร)
+10 litre (10 ลิตร)
+10 litre
+0000
+@@@air
+@@@@@
+-กระแย่ง
+-a
+DROP TABLE t1;
+#
+# End of ctype_thai.inc
+#
 #
 # End of MariaDB-10.1 tests
 #
diff --git a/mysql-test/r/ctype_uca_partitions.result b/mysql-test/r/ctype_uca_partitions.result
new file mode 100644
index 00000000000..11d4e82e27b
--- /dev/null
+++ b/mysql-test/r/ctype_uca_partitions.result
@@ -0,0 +1,86 @@
+SET NAMES utf8;
+CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_thai_520_w2)
+PARTITION BY KEY(c1) PARTITIONS 3;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` varchar(10) CHARACTER SET utf8 COLLATE utf8_thai_520_w2 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY KEY (c1)
+PARTITIONS 3 */
+INSERT INTO t1 VALUES ('A'),('À'),('Á'),('Â'),('Ã'),('Ä'),('Å');
+INSERT INTO t1 VALUES ('B');
+INSERT INTO t1 VALUES ('C');
+INSERT INTO t1 VALUES ('D');
+INSERT INTO t1 VALUES ('E');
+INSERT INTO t1 VALUES ('F');
+INSERT INTO t1 VALUES ('G');
+INSERT INTO t1 VALUES ('H');
+INSERT INTO t1 VALUES ('I');
+INSERT INTO t1 VALUES ('J');
+INSERT INTO t1 VALUES ('K');
+INSERT INTO t1 VALUES ('L'),('Ĺ'),('Ļ'),('Ľ'),('Ŀ'),('Ł');
+INSERT INTO t1 VALUES ('M');
+INSERT INTO t1 VALUES ('N');
+INSERT INTO t1 VALUES ('O'),('Ò'),('Ó'),('Ô'),('Õ'),('Ö'),('Ø');
+INSERT INTO t1 VALUES ('P');
+INSERT INTO t1 VALUES ('Q');
+INSERT INTO t1 VALUES ('R');
+INSERT INTO t1 VALUES ('S');
+INSERT INTO t1 VALUES ('T');
+INSERT INTO t1 VALUES ('U');
+INSERT INTO t1 VALUES ('V');
+INSERT INTO t1 VALUES ('W');
+INSERT INTO t1 VALUES ('X');
+INSERT INTO t1 VALUES ('Y');
+INSERT INTO t1 VALUES ('Z');
+SELECT * FROM t1 PARTITION (p0) ORDER BY c1;
+c1
+J
+K
+L
+Ĺ
+Ľ
+Ļ
+Ł
+Ŀ
+M
+Q
+R
+T
+V
+X
+SELECT * FROM t1 PARTITION (p1) ORDER BY c1;
+c1
+A
+Á
+À
+Â
+Å
+Ä
+Ã
+D
+E
+F
+G
+I
+N
+S
+U
+W
+Z
+SELECT * FROM t1 PARTITION (p2) ORDER BY c1;
+c1
+B
+C
+H
+O
+Ó
+Ò
+Ô
+Ö
+Õ
+Ø
+P
+Y
+DROP TABLE t1;
diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result
index fe5c76af000..4a6d5fa1b41 100644
--- a/mysql-test/r/ctype_ucs.result
+++ b/mysql-test/r/ctype_ucs.result
@@ -4496,6 +4496,15 @@ f1()
 DROP FUNCTION f1;
 DROP TABLE t1;
 #
+# MDEV-9662 Assertion `precision || !scale' failed in my_decimal_precision_to_length_no_truncation(uint, uint8, bool)
+#
+SELECT @@collation_connection;
+@@collation_connection
+ucs2_general_ci
+SELECT CASE 1 WHEN 2 THEN ( - '3' ) END;
+CASE 1 WHEN 2 THEN ( - '3' ) END
+NULL
+#
 # MDEV-5702 Incorrect results are returned with NULLIF()
 #
 CREATE TABLE t1 (d DATE);
diff --git a/mysql-test/r/ctype_utf16_uca.result b/mysql-test/r/ctype_utf16_uca.result
index ac2ed9b2c5b..1e4c77ea83d 100644
--- a/mysql-test/r/ctype_utf16_uca.result
+++ b/mysql-test/r/ctype_utf16_uca.result
@@ -6605,3 +6605,703 @@ DROP TABLE t1;
 #
 # End of MariaDB-10.0 tests
 #
+#
+# Start of 10.1 tests
+#
+SET NAMES utf8;
+SET collation_connection=utf16_thai_520_w2;
+#
+# Start of ctype_uca_w2.inc
+#
+SELECT @@collation_connection;
+@@collation_connection
+utf16_thai_520_w2
+SELECT ID, SORTLEN, COLLATION_NAME, CHARACTER_SET_NAME
+FROM INFORMATION_SCHEMA.COLLATIONS
+WHERE COLLATION_NAME LIKE @@collation_connection;
+ID	SORTLEN	COLLATION_NAME	CHARACTER_SET_NAME
+674	4	utf16_thai_520_w2	utf16
+#
+# Testing strnxfrm
+#
+CREATE TABLE t1 AS SELECT SPACE(3) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(3) CHARACTER SET utf16 COLLATE utf16_thai_520_w2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (SPACE(0)),(SPACE(1)),(SPACE(2));
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(8))) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(8)))
+	020A020A020A020A020A020A020A020A00200020002000200020002000200020
+0020	020A020A020A020A020A020A020A020A00200020002000200020002000200020
+00200020	020A020A020A020A020A020A020A020A00200020002000200020002000200020
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(4))) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(4)))
+	020A020A020A020A0020002000200020
+0020	020A020A020A020A0020002000200020
+00200020	020A020A020A020A0020002000200020
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 1)) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 1))
+	020A020A020A020A
+0020	020A020A020A020A
+00200020	020A020A020A020A
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 2)) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 2))
+	0020002000200020
+0020	0020002000200020
+00200020	0020002000200020
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 3)) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 3))
+	0020002000200020
+0020	0020002000200020
+00200020	0020002000200020
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT SPACE(10) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(10) CHARACTER SET utf16 COLLATE utf16_thai_520_w2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (_ucs2 0x3400);
+INSERT INTO t1 VALUES (_ucs2 0xF001);
+SELECT HEX(CONVERT(a USING ucs2)) AS ucs2, HEX(a), HEX(WEIGHT_STRING(a)) FROM t1;
+ucs2	HEX(a)	HEX(WEIGHT_STRING(a))
+3400	3400	FB80B4000020
+F001	F001	FBC1F0010020
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT SPACE(10) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(10) CHARACTER SET utf16 COLLATE utf16_thai_520_w2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('A'),('À'),('Á'),('Â'),('Ã'),('Ä'),('Å');
+SELECT a, HEX(WEIGHT_STRING(a LEVEL 2)) FROM t1 ORDER BY a;
+a	HEX(WEIGHT_STRING(a LEVEL 2))
+A	0020
+Á	00200032
+À	00200035
+Â	0020003C
+Å	00200043
+Ä	00200047
+Ã	0020004E
+SELECT a, HEX(WEIGHT_STRING(a LEVEL 2)) FROM t1 ORDER BY a DESC;
+a	HEX(WEIGHT_STRING(a LEVEL 2))
+Ã	0020004E
+Ä	00200047
+Å	00200043
+Â	0020003C
+À	00200035
+Á	00200032
+A	0020
+SET @backup_character_set_connection=@@character_set_connection;
+SET @backup_collation_connection=@@collation_connection;
+SET NAMES utf8;
+SET @stmt= CONCAT('ALTER TABLE t1 MODIFY a CHAR(10)' COLLATE utf8_bin,
+' CHARACTER SET ',  @backup_character_set_connection,
+' COLLATE ',  @backup_collation_connection);
+PREPARE stmt FROM @stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+SET collation_connection=@backup_collation_connection;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` char(10) CHARACTER SET utf16 COLLATE utf16_thai_520_w2 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT a, HEX(WEIGHT_STRING(a LEVEL 2)) FROM t1 ORDER BY a;
+a	HEX(WEIGHT_STRING(a LEVEL 2))
+A	0020
+Á	00200032
+À	00200035
+Â	0020003C
+Å	00200043
+Ä	00200047
+Ã	0020004E
+SELECT a, HEX(WEIGHT_STRING(a LEVEL 2)) FROM t1 ORDER BY a DESC;
+a	HEX(WEIGHT_STRING(a LEVEL 2))
+Ã	0020004E
+Ä	00200047
+Å	00200043
+Â	0020003C
+À	00200035
+Á	00200032
+A	0020
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT SPACE(10) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(10) CHARACTER SET utf16 COLLATE utf16_thai_520_w2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('cota'),('cote'),('cotz');
+INSERT INTO t1 VALUES ('coté'),('côte'),('côté'),('cotë'),('côtë');
+SELECT * FROM t1 ORDER BY a;
+a
+cota
+cote
+coté
+cotë
+côte
+côté
+côtë
+cotz
+SELECT * FROM t1 ORDER BY a DESC;
+a
+cotz
+côtë
+côté
+côte
+cotë
+coté
+cote
+cota
+DROP TABLE t1;
+#
+# End of ctype_uca_w2.inc
+#
+#
+# Start of ctype_thai.inc
+#
+CREATE TABLE t1 (a VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_bin);
+INSERT INTO t1 VALUES ('-กระแย่ง');
+INSERT INTO t1 VALUES ('กก');
+INSERT INTO t1 VALUES ('กราบ');
+INSERT INTO t1 VALUES ('ข่าง');
+INSERT INTO t1 VALUES ('ข้าง');
+INSERT INTO t1 VALUES ('ข้างกระดาน');
+INSERT INTO t1 VALUES ('ข้างขึ้น');
+INSERT INTO t1 VALUES ('ข้างควาย');
+INSERT INTO t1 VALUES ('ข้างเงิน');
+INSERT INTO t1 VALUES ('ข้างแรม');
+INSERT INTO t1 VALUES ('ข้างออก');
+INSERT INTO t1 VALUES ('ข้างๆ');
+INSERT INTO t1 VALUES ('ข้างๆ คูๆ');
+INSERT INTO t1 VALUES ('ขาง');
+INSERT INTO t1 VALUES ('แข็ง');
+INSERT INTO t1 VALUES ('แข่ง');
+INSERT INTO t1 VALUES ('แข่งขัน');
+INSERT INTO t1 VALUES ('แข้ง');
+INSERT INTO t1 VALUES ('แข้งขวา');
+INSERT INTO t1 VALUES ('แข็งขัน');
+INSERT INTO t1 VALUES ('ทูลเกล้า');
+INSERT INTO t1 VALUES ('ทูลเกล้าทูลกระหม่อม');
+INSERT INTO t1 VALUES ('ทูลเกล้าฯ');
+INSERT INTO t1 VALUES ('บุญญา');
+INSERT INTO t1 VALUES ('บุญ-หลง');
+INSERT INTO t1 VALUES ('บุญหลง');
+INSERT INTO t1 VALUES ('ป่า');
+INSERT INTO t1 VALUES ('ป่าน');
+INSERT INTO t1 VALUES ('ป้า');
+INSERT INTO t1 VALUES ('ป้าน');
+INSERT INTO t1 VALUES ('ป๊า');
+INSERT INTO t1 VALUES ('ป๊าน');
+INSERT INTO t1 VALUES ('ป๋า');
+INSERT INTO t1 VALUES ('ป๋าน');
+INSERT INTO t1 VALUES ('ปา');
+INSERT INTO t1 VALUES ('ปาน');
+INSERT INTO t1 VALUES ('แป้ง');
+INSERT INTO t1 VALUES ('พณิชย์');
+INSERT INTO t1 VALUES ('ม้า');
+INSERT INTO t1 VALUES ('ฯพณฯ');
+INSERT INTO t1 VALUES ('A');
+INSERT INTO t1 VALUES ('a');
+INSERT INTO t1 VALUES ('a\'');
+INSERT INTO t1 VALUES ('A-');
+INSERT INTO t1 VALUES ('a-');
+INSERT INTO t1 VALUES ('-a');
+INSERT INTO t1 VALUES ('A.');
+INSERT INTO t1 VALUES ('a.');
+INSERT INTO t1 VALUES ('A-1');
+INSERT INTO t1 VALUES ('aa');
+INSERT INTO t1 VALUES ('AA');
+INSERT INTO t1 VALUES ('A.A.');
+INSERT INTO t1 VALUES ('a.a.');
+INSERT INTO t1 VALUES ('AAA');
+INSERT INTO t1 VALUES ('A.A.A.');
+INSERT INTO t1 VALUES ('AAAA');
+INSERT INTO t1 VALUES ('A.A.A.L.');
+INSERT INTO t1 VALUES ('A.A.A.S.');
+INSERT INTO t1 VALUES ('Aachen');
+INSERT INTO t1 VALUES ('A.A.E.');
+INSERT INTO t1 VALUES ('A.Ae.E.');
+INSERT INTO t1 VALUES ('A.A.E.E.');
+INSERT INTO t1 VALUES ('AAES');
+INSERT INTO t1 VALUES ('AAF');
+INSERT INTO t1 VALUES ('A.Agr');
+INSERT INTO t1 VALUES ('aah');
+INSERT INTO t1 VALUES ('@@@@@');
+INSERT INTO t1 VALUES ('0000');
+INSERT INTO t1 VALUES ('9999');
+INSERT INTO t1 VALUES ('Aalborg');
+INSERT INTO t1 VALUES ('aide');
+INSERT INTO t1 VALUES ('air');
+INSERT INTO t1 VALUES ('@@@air');
+INSERT INTO t1 VALUES ('air@@@');
+INSERT INTO t1 VALUES ('C.A.F');
+INSERT INTO t1 VALUES ('Canon');
+INSERT INTO t1 VALUES ('coop');
+INSERT INTO t1 VALUES ('co-op');
+INSERT INTO t1 VALUES ('COOP');
+INSERT INTO t1 VALUES ('CO-OP');
+INSERT INTO t1 VALUES ('Copenhegen');
+INSERT INTO t1 VALUES ('McArthur');
+INSERT INTO t1 VALUES ('Mc Arthur');
+INSERT INTO t1 VALUES ('Mc Mahon');
+INSERT INTO t1 VALUES ('vice-president');
+INSERT INTO t1 VALUES ('vice versa');
+INSERT INTO t1 VALUES ('vice-versa');
+INSERT INTO t1 VALUES ('10 ลิตร');
+INSERT INTO t1 VALUES ('๑๐ ลิตร');
+INSERT INTO t1 VALUES ('10 litre');
+INSERT INTO t1 VALUES ('10 litre (10 ลิตร)');
+INSERT INTO t1 VALUES ('10 ลิตร (10 litre)');
+INSERT INTO t1 VALUES ('10 litre (๑๐ ลิตร)');
+INSERT INTO t1 VALUES ('๑๐ ลิตร (10 litre)');
+INSERT INTO t1 VALUES ('๑๐ ลิตร [10 litre]');
+INSERT INTO t1 VALUES ('๑๐ ลิตร {10 litre}');
+ALTER TABLE t1 ORDER BY a;
+SET @backup_character_set_connection=@@character_set_connection;
+SET @backup_collation_connection=@@collation_connection;
+SET NAMES utf8;
+SET @stmt= CONCAT('ALTER TABLE t1 MODIFY a VARCHAR(30)',
+' CHARACTER SET ', @backup_character_set_connection,
+' COLLATE ', @backup_collation_connection,
+' , ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY'
+                  ' , ADD KEY a_id (a, id)');
+PREPARE stmt FROM @stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+SET collation_connection=@backup_collation_connection;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(30) CHARACTER SET utf16 COLLATE utf16_thai_520_w2 DEFAULT NULL,
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`),
+  KEY `a_id` (`a`,`id`)
+) ENGINE=MyISAM AUTO_INCREMENT=97 DEFAULT CHARSET=latin1
+#
+# Ascending sort, using filesort
+#
+EXPLAIN SELECT a FROM t1 ORDER BY a, BINARY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a_id	127	NULL	96	Using index; Using filesort
+SELECT a FROM t1 ORDER BY a, BINARY a;
+a
+-a
+-กระแย่ง
+@@@@@
+@@@air
+0000
+10 litre
+10 litre (10 ลิตร)
+10 litre (๑๐ ลิตร)
+10 ลิตร
+๑๐ ลิตร
+10 ลิตร (10 litre)
+๑๐ ลิตร (10 litre)
+๑๐ ลิตร [10 litre]
+๑๐ ลิตร {10 litre}
+9999
+A
+a
+A-
+a-
+A-1
+A.
+a.
+A.A.
+a.a.
+A.A.A.
+A.A.A.L.
+A.A.A.S.
+A.A.E.
+A.A.E.E.
+A.Ae.E.
+A.Agr
+a'
+AA
+aa
+AAA
+AAAA
+Aachen
+AAES
+AAF
+aah
+Aalborg
+aide
+air
+air@@@
+C.A.F
+Canon
+CO-OP
+co-op
+COOP
+coop
+Copenhegen
+Mc Arthur
+Mc Mahon
+McArthur
+vice versa
+vice-president
+vice-versa
+กก
+กราบ
+ขาง
+ข่าง
+ข้าง
+ข้างๆ
+ข้างๆ คูๆ
+ข้างกระดาน
+ข้างขึ้น
+ข้างควาย
+ข้างเงิน
+ข้างแรม
+ข้างออก
+แข็ง
+แข่ง
+แข้ง
+แข้งขวา
+แข็งขัน
+แข่งขัน
+ทูลเกล้า
+ทูลเกล้าทูลกระหม่อม
+ทูลเกล้าฯ
+บุญ-หลง
+บุญญา
+บุญหลง
+ปา
+ป่า
+ป้า
+ป๊า
+ป๋า
+ปาน
+ป่าน
+ป้าน
+ป๊าน
+ป๋าน
+แป้ง
+พณิชย์
+ม้า
+ฯพณฯ
+#
+# Descending sort, using filesort
+#
+EXPLAIN SELECT a FROM t1 ORDER BY a DESC, BINARY a DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a_id	127	NULL	96	Using index; Using filesort
+SELECT a FROM t1 ORDER BY a DESC, BINARY a DESC;
+a
+ฯพณฯ
+ม้า
+พณิชย์
+แป้ง
+ป๋าน
+ป๊าน
+ป้าน
+ป่าน
+ปาน
+ป๋า
+ป๊า
+ป้า
+ป่า
+ปา
+บุญหลง
+บุญญา
+บุญ-หลง
+ทูลเกล้าฯ
+ทูลเกล้าทูลกระหม่อม
+ทูลเกล้า
+แข่งขัน
+แข็งขัน
+แข้งขวา
+แข้ง
+แข่ง
+แข็ง
+ข้างออก
+ข้างแรม
+ข้างเงิน
+ข้างควาย
+ข้างขึ้น
+ข้างกระดาน
+ข้างๆ คูๆ
+ข้างๆ
+ข้าง
+ข่าง
+ขาง
+กราบ
+กก
+vice-versa
+vice-president
+vice versa
+McArthur
+Mc Mahon
+Mc Arthur
+Copenhegen
+coop
+COOP
+co-op
+CO-OP
+Canon
+C.A.F
+air@@@
+air
+aide
+Aalborg
+aah
+AAF
+AAES
+Aachen
+AAAA
+AAA
+aa
+AA
+a'
+A.Agr
+A.Ae.E.
+A.A.E.E.
+A.A.E.
+A.A.A.S.
+A.A.A.L.
+A.A.A.
+a.a.
+A.A.
+a.
+A.
+A-1
+a-
+A-
+a
+A
+9999
+๑๐ ลิตร {10 litre}
+๑๐ ลิตร [10 litre]
+๑๐ ลิตร (10 litre)
+10 ลิตร (10 litre)
+๑๐ ลิตร
+10 ลิตร
+10 litre (๑๐ ลิตร)
+10 litre (10 ลิตร)
+10 litre
+0000
+@@@air
+@@@@@
+-กระแย่ง
+-a
+#
+# Ascending sort, using index
+#
+EXPLAIN SELECT a FROM t1 ORDER BY a, id;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a_id	127	NULL	96	Using index
+SELECT a FROM t1 ORDER BY a, id;
+a
+-a
+-กระแย่ง
+@@@@@
+@@@air
+0000
+10 litre
+10 litre (10 ลิตร)
+10 litre (๑๐ ลิตร)
+10 ลิตร
+๑๐ ลิตร
+10 ลิตร (10 litre)
+๑๐ ลิตร (10 litre)
+๑๐ ลิตร [10 litre]
+๑๐ ลิตร {10 litre}
+9999
+A
+a
+A-
+a-
+A-1
+A.
+a.
+A.A.
+a.a.
+A.A.A.
+A.A.A.L.
+A.A.A.S.
+A.A.E.
+A.A.E.E.
+A.Ae.E.
+A.Agr
+a'
+AA
+aa
+AAA
+AAAA
+Aachen
+AAES
+AAF
+aah
+Aalborg
+aide
+air
+air@@@
+C.A.F
+Canon
+CO-OP
+co-op
+COOP
+coop
+Copenhegen
+Mc Arthur
+Mc Mahon
+McArthur
+vice versa
+vice-president
+vice-versa
+กก
+กราบ
+ขาง
+ข่าง
+ข้าง
+ข้างๆ
+ข้างๆ คูๆ
+ข้างกระดาน
+ข้างขึ้น
+ข้างควาย
+ข้างเงิน
+ข้างแรม
+ข้างออก
+แข็ง
+แข่ง
+แข้ง
+แข้งขวา
+แข็งขัน
+แข่งขัน
+ทูลเกล้า
+ทูลเกล้าทูลกระหม่อม
+ทูลเกล้าฯ
+บุญ-หลง
+บุญญา
+บุญหลง
+ปา
+ป่า
+ป้า
+ป๊า
+ป๋า
+ปาน
+ป่าน
+ป้าน
+ป๊าน
+ป๋าน
+แป้ง
+พณิชย์
+ม้า
+ฯพณฯ
+#
+# Descending sort, using index
+#
+EXPLAIN SELECT a FROM t1 ORDER BY a DESC, id DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a_id	127	NULL	96	Using index
+SELECT a FROM t1 ORDER BY a DESC, id DESC;
+a
+ฯพณฯ
+ม้า
+พณิชย์
+แป้ง
+ป๋าน
+ป๊าน
+ป้าน
+ป่าน
+ปาน
+ป๋า
+ป๊า
+ป้า
+ป่า
+ปา
+บุญหลง
+บุญญา
+บุญ-หลง
+ทูลเกล้าฯ
+ทูลเกล้าทูลกระหม่อม
+ทูลเกล้า
+แข่งขัน
+แข็งขัน
+แข้งขวา
+แข้ง
+แข่ง
+แข็ง
+ข้างออก
+ข้างแรม
+ข้างเงิน
+ข้างควาย
+ข้างขึ้น
+ข้างกระดาน
+ข้างๆ คูๆ
+ข้างๆ
+ข้าง
+ข่าง
+ขาง
+กราบ
+กก
+vice-versa
+vice-president
+vice versa
+McArthur
+Mc Mahon
+Mc Arthur
+Copenhegen
+coop
+COOP
+co-op
+CO-OP
+Canon
+C.A.F
+air@@@
+air
+aide
+Aalborg
+aah
+AAF
+AAES
+Aachen
+AAAA
+AAA
+aa
+AA
+a'
+A.Agr
+A.Ae.E.
+A.A.E.E.
+A.A.E.
+A.A.A.S.
+A.A.A.L.
+A.A.A.
+a.a.
+A.A.
+a.
+A.
+A-1
+a-
+A-
+a
+A
+9999
+๑๐ ลิตร {10 litre}
+๑๐ ลิตร [10 litre]
+๑๐ ลิตร (10 litre)
+10 ลิตร (10 litre)
+๑๐ ลิตร
+10 ลิตร
+10 litre (๑๐ ลิตร)
+10 litre (10 ลิตร)
+10 litre
+0000
+@@@air
+@@@@@
+-กระแย่ง
+-a
+DROP TABLE t1;
+#
+# End of ctype_thai.inc
+#
+#
+# End of 10.1 tests
+#
diff --git a/mysql-test/r/ctype_utf32_uca.result b/mysql-test/r/ctype_utf32_uca.result
index 89a9e23bf45..234a01bb108 100644
--- a/mysql-test/r/ctype_utf32_uca.result
+++ b/mysql-test/r/ctype_utf32_uca.result
@@ -6625,3 +6625,703 @@ DROP TABLE t1;
 #
 # End of MariaDB-10.0 tests
 #
+#
+# Start of 10.1 tests
+#
+SET NAMES utf8;
+SET collation_connection=utf32_thai_520_w2;
+#
+# Start of ctype_uca_w2.inc
+#
+SELECT @@collation_connection;
+@@collation_connection
+utf32_thai_520_w2
+SELECT ID, SORTLEN, COLLATION_NAME, CHARACTER_SET_NAME
+FROM INFORMATION_SCHEMA.COLLATIONS
+WHERE COLLATION_NAME LIKE @@collation_connection;
+ID	SORTLEN	COLLATION_NAME	CHARACTER_SET_NAME
+738	4	utf32_thai_520_w2	utf32
+#
+# Testing strnxfrm
+#
+CREATE TABLE t1 AS SELECT SPACE(3) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(3) CHARACTER SET utf32 COLLATE utf32_thai_520_w2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (SPACE(0)),(SPACE(1)),(SPACE(2));
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(8))) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(8)))
+	020A020A020A020A020A020A020A020A00200020002000200020002000200020
+00000020	020A020A020A020A020A020A020A020A00200020002000200020002000200020
+0000002000000020	020A020A020A020A020A020A020A020A00200020002000200020002000200020
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(4))) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(4)))
+	020A020A020A020A0020002000200020
+00000020	020A020A020A020A0020002000200020
+0000002000000020	020A020A020A020A0020002000200020
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 1)) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 1))
+	020A020A020A020A
+00000020	020A020A020A020A
+0000002000000020	020A020A020A020A
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 2)) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 2))
+	0020002000200020
+00000020	0020002000200020
+0000002000000020	0020002000200020
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 3)) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 3))
+	0020002000200020
+00000020	0020002000200020
+0000002000000020	0020002000200020
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT SPACE(10) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(10) CHARACTER SET utf32 COLLATE utf32_thai_520_w2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (_ucs2 0x3400);
+INSERT INTO t1 VALUES (_ucs2 0xF001);
+SELECT HEX(CONVERT(a USING ucs2)) AS ucs2, HEX(a), HEX(WEIGHT_STRING(a)) FROM t1;
+ucs2	HEX(a)	HEX(WEIGHT_STRING(a))
+3400	00003400	FB80B4000020
+F001	0000F001	FBC1F0010020
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT SPACE(10) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(10) CHARACTER SET utf32 COLLATE utf32_thai_520_w2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('A'),('À'),('Á'),('Â'),('Ã'),('Ä'),('Å');
+SELECT a, HEX(WEIGHT_STRING(a LEVEL 2)) FROM t1 ORDER BY a;
+a	HEX(WEIGHT_STRING(a LEVEL 2))
+A	0020
+Á	00200032
+À	00200035
+Â	0020003C
+Å	00200043
+Ä	00200047
+Ã	0020004E
+SELECT a, HEX(WEIGHT_STRING(a LEVEL 2)) FROM t1 ORDER BY a DESC;
+a	HEX(WEIGHT_STRING(a LEVEL 2))
+Ã	0020004E
+Ä	00200047
+Å	00200043
+Â	0020003C
+À	00200035
+Á	00200032
+A	0020
+SET @backup_character_set_connection=@@character_set_connection;
+SET @backup_collation_connection=@@collation_connection;
+SET NAMES utf8;
+SET @stmt= CONCAT('ALTER TABLE t1 MODIFY a CHAR(10)' COLLATE utf8_bin,
+' CHARACTER SET ',  @backup_character_set_connection,
+' COLLATE ',  @backup_collation_connection);
+PREPARE stmt FROM @stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+SET collation_connection=@backup_collation_connection;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` char(10) CHARACTER SET utf32 COLLATE utf32_thai_520_w2 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT a, HEX(WEIGHT_STRING(a LEVEL 2)) FROM t1 ORDER BY a;
+a	HEX(WEIGHT_STRING(a LEVEL 2))
+A	0020
+Á	00200032
+À	00200035
+Â	0020003C
+Å	00200043
+Ä	00200047
+Ã	0020004E
+SELECT a, HEX(WEIGHT_STRING(a LEVEL 2)) FROM t1 ORDER BY a DESC;
+a	HEX(WEIGHT_STRING(a LEVEL 2))
+Ã	0020004E
+Ä	00200047
+Å	00200043
+Â	0020003C
+À	00200035
+Á	00200032
+A	0020
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT SPACE(10) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(10) CHARACTER SET utf32 COLLATE utf32_thai_520_w2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('cota'),('cote'),('cotz');
+INSERT INTO t1 VALUES ('coté'),('côte'),('côté'),('cotë'),('côtë');
+SELECT * FROM t1 ORDER BY a;
+a
+cota
+cote
+coté
+cotë
+côte
+côté
+côtë
+cotz
+SELECT * FROM t1 ORDER BY a DESC;
+a
+cotz
+côtë
+côté
+côte
+cotë
+coté
+cote
+cota
+DROP TABLE t1;
+#
+# End of ctype_uca_w2.inc
+#
+#
+# Start of ctype_thai.inc
+#
+CREATE TABLE t1 (a VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_bin);
+INSERT INTO t1 VALUES ('-กระแย่ง');
+INSERT INTO t1 VALUES ('กก');
+INSERT INTO t1 VALUES ('กราบ');
+INSERT INTO t1 VALUES ('ข่าง');
+INSERT INTO t1 VALUES ('ข้าง');
+INSERT INTO t1 VALUES ('ข้างกระดาน');
+INSERT INTO t1 VALUES ('ข้างขึ้น');
+INSERT INTO t1 VALUES ('ข้างควาย');
+INSERT INTO t1 VALUES ('ข้างเงิน');
+INSERT INTO t1 VALUES ('ข้างแรม');
+INSERT INTO t1 VALUES ('ข้างออก');
+INSERT INTO t1 VALUES ('ข้างๆ');
+INSERT INTO t1 VALUES ('ข้างๆ คูๆ');
+INSERT INTO t1 VALUES ('ขาง');
+INSERT INTO t1 VALUES ('แข็ง');
+INSERT INTO t1 VALUES ('แข่ง');
+INSERT INTO t1 VALUES ('แข่งขัน');
+INSERT INTO t1 VALUES ('แข้ง');
+INSERT INTO t1 VALUES ('แข้งขวา');
+INSERT INTO t1 VALUES ('แข็งขัน');
+INSERT INTO t1 VALUES ('ทูลเกล้า');
+INSERT INTO t1 VALUES ('ทูลเกล้าทูลกระหม่อม');
+INSERT INTO t1 VALUES ('ทูลเกล้าฯ');
+INSERT INTO t1 VALUES ('บุญญา');
+INSERT INTO t1 VALUES ('บุญ-หลง');
+INSERT INTO t1 VALUES ('บุญหลง');
+INSERT INTO t1 VALUES ('ป่า');
+INSERT INTO t1 VALUES ('ป่าน');
+INSERT INTO t1 VALUES ('ป้า');
+INSERT INTO t1 VALUES ('ป้าน');
+INSERT INTO t1 VALUES ('ป๊า');
+INSERT INTO t1 VALUES ('ป๊าน');
+INSERT INTO t1 VALUES ('ป๋า');
+INSERT INTO t1 VALUES ('ป๋าน');
+INSERT INTO t1 VALUES ('ปา');
+INSERT INTO t1 VALUES ('ปาน');
+INSERT INTO t1 VALUES ('แป้ง');
+INSERT INTO t1 VALUES ('พณิชย์');
+INSERT INTO t1 VALUES ('ม้า');
+INSERT INTO t1 VALUES ('ฯพณฯ');
+INSERT INTO t1 VALUES ('A');
+INSERT INTO t1 VALUES ('a');
+INSERT INTO t1 VALUES ('a\'');
+INSERT INTO t1 VALUES ('A-');
+INSERT INTO t1 VALUES ('a-');
+INSERT INTO t1 VALUES ('-a');
+INSERT INTO t1 VALUES ('A.');
+INSERT INTO t1 VALUES ('a.');
+INSERT INTO t1 VALUES ('A-1');
+INSERT INTO t1 VALUES ('aa');
+INSERT INTO t1 VALUES ('AA');
+INSERT INTO t1 VALUES ('A.A.');
+INSERT INTO t1 VALUES ('a.a.');
+INSERT INTO t1 VALUES ('AAA');
+INSERT INTO t1 VALUES ('A.A.A.');
+INSERT INTO t1 VALUES ('AAAA');
+INSERT INTO t1 VALUES ('A.A.A.L.');
+INSERT INTO t1 VALUES ('A.A.A.S.');
+INSERT INTO t1 VALUES ('Aachen');
+INSERT INTO t1 VALUES ('A.A.E.');
+INSERT INTO t1 VALUES ('A.Ae.E.');
+INSERT INTO t1 VALUES ('A.A.E.E.');
+INSERT INTO t1 VALUES ('AAES');
+INSERT INTO t1 VALUES ('AAF');
+INSERT INTO t1 VALUES ('A.Agr');
+INSERT INTO t1 VALUES ('aah');
+INSERT INTO t1 VALUES ('@@@@@');
+INSERT INTO t1 VALUES ('0000');
+INSERT INTO t1 VALUES ('9999');
+INSERT INTO t1 VALUES ('Aalborg');
+INSERT INTO t1 VALUES ('aide');
+INSERT INTO t1 VALUES ('air');
+INSERT INTO t1 VALUES ('@@@air');
+INSERT INTO t1 VALUES ('air@@@');
+INSERT INTO t1 VALUES ('C.A.F');
+INSERT INTO t1 VALUES ('Canon');
+INSERT INTO t1 VALUES ('coop');
+INSERT INTO t1 VALUES ('co-op');
+INSERT INTO t1 VALUES ('COOP');
+INSERT INTO t1 VALUES ('CO-OP');
+INSERT INTO t1 VALUES ('Copenhegen');
+INSERT INTO t1 VALUES ('McArthur');
+INSERT INTO t1 VALUES ('Mc Arthur');
+INSERT INTO t1 VALUES ('Mc Mahon');
+INSERT INTO t1 VALUES ('vice-president');
+INSERT INTO t1 VALUES ('vice versa');
+INSERT INTO t1 VALUES ('vice-versa');
+INSERT INTO t1 VALUES ('10 ลิตร');
+INSERT INTO t1 VALUES ('๑๐ ลิตร');
+INSERT INTO t1 VALUES ('10 litre');
+INSERT INTO t1 VALUES ('10 litre (10 ลิตร)');
+INSERT INTO t1 VALUES ('10 ลิตร (10 litre)');
+INSERT INTO t1 VALUES ('10 litre (๑๐ ลิตร)');
+INSERT INTO t1 VALUES ('๑๐ ลิตร (10 litre)');
+INSERT INTO t1 VALUES ('๑๐ ลิตร [10 litre]');
+INSERT INTO t1 VALUES ('๑๐ ลิตร {10 litre}');
+ALTER TABLE t1 ORDER BY a;
+SET @backup_character_set_connection=@@character_set_connection;
+SET @backup_collation_connection=@@collation_connection;
+SET NAMES utf8;
+SET @stmt= CONCAT('ALTER TABLE t1 MODIFY a VARCHAR(30)',
+' CHARACTER SET ', @backup_character_set_connection,
+' COLLATE ', @backup_collation_connection,
+' , ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY'
+                  ' , ADD KEY a_id (a, id)');
+PREPARE stmt FROM @stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+SET collation_connection=@backup_collation_connection;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(30) CHARACTER SET utf32 COLLATE utf32_thai_520_w2 DEFAULT NULL,
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`),
+  KEY `a_id` (`a`,`id`)
+) ENGINE=MyISAM AUTO_INCREMENT=97 DEFAULT CHARSET=latin1
+#
+# Ascending sort, using filesort
+#
+EXPLAIN SELECT a FROM t1 ORDER BY a, BINARY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a_id	127	NULL	96	Using index; Using filesort
+SELECT a FROM t1 ORDER BY a, BINARY a;
+a
+-a
+-กระแย่ง
+@@@@@
+@@@air
+0000
+10 litre
+10 litre (10 ลิตร)
+10 litre (๑๐ ลิตร)
+10 ลิตร
+๑๐ ลิตร
+10 ลิตร (10 litre)
+๑๐ ลิตร (10 litre)
+๑๐ ลิตร [10 litre]
+๑๐ ลิตร {10 litre}
+9999
+A
+a
+A-
+a-
+A-1
+A.
+a.
+A.A.
+a.a.
+A.A.A.
+A.A.A.L.
+A.A.A.S.
+A.A.E.
+A.A.E.E.
+A.Ae.E.
+A.Agr
+a'
+AA
+aa
+AAA
+AAAA
+Aachen
+AAES
+AAF
+aah
+Aalborg
+aide
+air
+air@@@
+C.A.F
+Canon
+CO-OP
+co-op
+COOP
+coop
+Copenhegen
+Mc Arthur
+Mc Mahon
+McArthur
+vice versa
+vice-president
+vice-versa
+กก
+กราบ
+ขาง
+ข่าง
+ข้าง
+ข้างๆ
+ข้างๆ คูๆ
+ข้างกระดาน
+ข้างขึ้น
+ข้างควาย
+ข้างเงิน
+ข้างแรม
+ข้างออก
+แข็ง
+แข่ง
+แข้ง
+แข้งขวา
+แข็งขัน
+แข่งขัน
+ทูลเกล้า
+ทูลเกล้าทูลกระหม่อม
+ทูลเกล้าฯ
+บุญ-หลง
+บุญญา
+บุญหลง
+ปา
+ป่า
+ป้า
+ป๊า
+ป๋า
+ปาน
+ป่าน
+ป้าน
+ป๊าน
+ป๋าน
+แป้ง
+พณิชย์
+ม้า
+ฯพณฯ
+#
+# Descending sort, using filesort
+#
+EXPLAIN SELECT a FROM t1 ORDER BY a DESC, BINARY a DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a_id	127	NULL	96	Using index; Using filesort
+SELECT a FROM t1 ORDER BY a DESC, BINARY a DESC;
+a
+ฯพณฯ
+ม้า
+พณิชย์
+แป้ง
+ป๋าน
+ป๊าน
+ป้าน
+ป่าน
+ปาน
+ป๋า
+ป๊า
+ป้า
+ป่า
+ปา
+บุญหลง
+บุญญา
+บุญ-หลง
+ทูลเกล้าฯ
+ทูลเกล้าทูลกระหม่อม
+ทูลเกล้า
+แข่งขัน
+แข็งขัน
+แข้งขวา
+แข้ง
+แข่ง
+แข็ง
+ข้างออก
+ข้างแรม
+ข้างเงิน
+ข้างควาย
+ข้างขึ้น
+ข้างกระดาน
+ข้างๆ คูๆ
+ข้างๆ
+ข้าง
+ข่าง
+ขาง
+กราบ
+กก
+vice-versa
+vice-president
+vice versa
+McArthur
+Mc Mahon
+Mc Arthur
+Copenhegen
+coop
+COOP
+co-op
+CO-OP
+Canon
+C.A.F
+air@@@
+air
+aide
+Aalborg
+aah
+AAF
+AAES
+Aachen
+AAAA
+AAA
+aa
+AA
+a'
+A.Agr
+A.Ae.E.
+A.A.E.E.
+A.A.E.
+A.A.A.S.
+A.A.A.L.
+A.A.A.
+a.a.
+A.A.
+a.
+A.
+A-1
+a-
+A-
+a
+A
+9999
+๑๐ ลิตร {10 litre}
+๑๐ ลิตร [10 litre]
+๑๐ ลิตร (10 litre)
+10 ลิตร (10 litre)
+๑๐ ลิตร
+10 ลิตร
+10 litre (๑๐ ลิตร)
+10 litre (10 ลิตร)
+10 litre
+0000
+@@@air
+@@@@@
+-กระแย่ง
+-a
+#
+# Ascending sort, using index
+#
+EXPLAIN SELECT a FROM t1 ORDER BY a, id;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a_id	127	NULL	96	Using index
+SELECT a FROM t1 ORDER BY a, id;
+a
+-a
+-กระแย่ง
+@@@@@
+@@@air
+0000
+10 litre
+10 litre (10 ลิตร)
+10 litre (๑๐ ลิตร)
+10 ลิตร
+๑๐ ลิตร
+10 ลิตร (10 litre)
+๑๐ ลิตร (10 litre)
+๑๐ ลิตร [10 litre]
+๑๐ ลิตร {10 litre}
+9999
+A
+a
+A-
+a-
+A-1
+A.
+a.
+A.A.
+a.a.
+A.A.A.
+A.A.A.L.
+A.A.A.S.
+A.A.E.
+A.A.E.E.
+A.Ae.E.
+A.Agr
+a'
+AA
+aa
+AAA
+AAAA
+Aachen
+AAES
+AAF
+aah
+Aalborg
+aide
+air
+air@@@
+C.A.F
+Canon
+CO-OP
+co-op
+COOP
+coop
+Copenhegen
+Mc Arthur
+Mc Mahon
+McArthur
+vice versa
+vice-president
+vice-versa
+กก
+กราบ
+ขาง
+ข่าง
+ข้าง
+ข้างๆ
+ข้างๆ คูๆ
+ข้างกระดาน
+ข้างขึ้น
+ข้างควาย
+ข้างเงิน
+ข้างแรม
+ข้างออก
+แข็ง
+แข่ง
+แข้ง
+แข้งขวา
+แข็งขัน
+แข่งขัน
+ทูลเกล้า
+ทูลเกล้าทูลกระหม่อม
+ทูลเกล้าฯ
+บุญ-หลง
+บุญญา
+บุญหลง
+ปา
+ป่า
+ป้า
+ป๊า
+ป๋า
+ปาน
+ป่าน
+ป้าน
+ป๊าน
+ป๋าน
+แป้ง
+พณิชย์
+ม้า
+ฯพณฯ
+#
+# Descending sort, using index
+#
+EXPLAIN SELECT a FROM t1 ORDER BY a DESC, id DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a_id	127	NULL	96	Using index
+SELECT a FROM t1 ORDER BY a DESC, id DESC;
+a
+ฯพณฯ
+ม้า
+พณิชย์
+แป้ง
+ป๋าน
+ป๊าน
+ป้าน
+ป่าน
+ปาน
+ป๋า
+ป๊า
+ป้า
+ป่า
+ปา
+บุญหลง
+บุญญา
+บุญ-หลง
+ทูลเกล้าฯ
+ทูลเกล้าทูลกระหม่อม
+ทูลเกล้า
+แข่งขัน
+แข็งขัน
+แข้งขวา
+แข้ง
+แข่ง
+แข็ง
+ข้างออก
+ข้างแรม
+ข้างเงิน
+ข้างควาย
+ข้างขึ้น
+ข้างกระดาน
+ข้างๆ คูๆ
+ข้างๆ
+ข้าง
+ข่าง
+ขาง
+กราบ
+กก
+vice-versa
+vice-president
+vice versa
+McArthur
+Mc Mahon
+Mc Arthur
+Copenhegen
+coop
+COOP
+co-op
+CO-OP
+Canon
+C.A.F
+air@@@
+air
+aide
+Aalborg
+aah
+AAF
+AAES
+Aachen
+AAAA
+AAA
+aa
+AA
+a'
+A.Agr
+A.Ae.E.
+A.A.E.E.
+A.A.E.
+A.A.A.S.
+A.A.A.L.
+A.A.A.
+a.a.
+A.A.
+a.
+A.
+A-1
+a-
+A-
+a
+A
+9999
+๑๐ ลิตร {10 litre}
+๑๐ ลิตร [10 litre]
+๑๐ ลิตร (10 litre)
+10 ลิตร (10 litre)
+๑๐ ลิตร
+10 ลิตร
+10 litre (๑๐ ลิตร)
+10 litre (10 ลิตร)
+10 litre
+0000
+@@@air
+@@@@@
+-กระแย่ง
+-a
+DROP TABLE t1;
+#
+# End of ctype_thai.inc
+#
+#
+# End of 10.1 tests
+#
diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result
index f2f425c8107..bd40b82fa6e 100644
--- a/mysql-test/r/ctype_utf8.result
+++ b/mysql-test/r/ctype_utf8.result
@@ -5338,6 +5338,15 @@ f1()
 DROP FUNCTION f1;
 DROP TABLE t1;
 #
+# MDEV-9662 Assertion `precision || !scale' failed in my_decimal_precision_to_length_no_truncation(uint, uint8, bool)
+#
+SELECT @@collation_connection;
+@@collation_connection
+utf8_general_ci
+SELECT CASE 1 WHEN 2 THEN ( - '3' ) END;
+CASE 1 WHEN 2 THEN ( - '3' ) END
+NULL
+#
 # MDEV-5702 Incorrect results are returned with NULLIF()
 #
 CREATE TABLE t1 (d DATE);
@@ -6200,6 +6209,45 @@ OCTET_LENGTH(a)	a
 255	ＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡ
 DROP TABLE t1;
 #
+# MDEV-8402 Bug#77473 Bug#21317406 TRUNCATED DATA WITH SUBQUERY & UTF8
+#
+#
+SET NAMES utf8;
+SELECT length(rpad(_utf8 0xD0B1, 65536, _utf8 0xD0B2)) AS data;
+data
+131072
+SELECT length(data) AS len FROM (
+SELECT rpad(_utf8 0xD0B1, 65536, _utf8 0xD0B2) AS data
+) AS sub;
+len
+131072
+SELECT length(rpad(_utf8 0xD0B1, 65535, _utf8 0xD0B2)) AS data;
+data
+131070
+SELECT length(data) AS len FROM (
+SELECT rpad(_utf8 0xD0B1, 65535, _utf8 0xD0B2) AS data
+) AS sub;
+len
+131070
+SELECT length(data) AS len FROM (SELECT REPEAT('ä', 36766) AS data) AS sub;
+len
+73532
+SELECT length(data) AS len FROM (SELECT REPEAT('ä', 36767) AS data) AS sub;
+len
+73534
+SELECT length(data) AS len FROM (SELECT REPEAT('ä', 36778) AS data) AS sub;
+len
+73556
+SELECT length(data) AS len FROM (SELECT REPEAT('ä', 65535) AS data) AS sub;
+len
+131070
+SELECT length(data) AS len FROM (SELECT REPEAT('ä', 65536) AS data) AS sub;
+len
+131072
+SELECT length(data) AS len FROM (SELECT REPEAT('ä', 65537) AS data) AS sub;
+len
+131074
+#
 # End of 5.5 tests
 #
 #
diff --git a/mysql-test/r/ctype_utf8mb4.result b/mysql-test/r/ctype_utf8mb4.result
index 4ba8a8a39a1..32fa9bd74c0 100644
--- a/mysql-test/r/ctype_utf8mb4.result
+++ b/mysql-test/r/ctype_utf8mb4.result
@@ -2825,6 +2825,40 @@ OCTET_LENGTH(a)	a
 252	😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎
 DROP TABLE t1;
 #
+# MDEV-8402 Bug#77473 Bug#21317406 TRUNCATED DATA WITH SUBQUERY & UTF8
+#
+#
+SET NAMES utf8mb4;
+SELECT length(repeat(_utf8mb4 0xE29883, 21844)) AS data;
+data
+65532
+SELECT length(data) AS len
+FROM ( SELECT repeat(_utf8mb4 0xE29883, 21844) AS data ) AS sub;
+len
+65532
+SELECT length(repeat(_utf8mb4 0xE29883, 21846)) AS data;
+data
+65538
+SELECT length(data) AS len
+FROM ( SELECT repeat(_utf8mb4 0xE29883, 21846) AS data ) AS sub;
+len
+65538
+SELECT LENGTH(data) AS len FROM (SELECT REPEAT('☃', 21844) AS data ) AS sub;
+len
+65532
+SELECT LENGTH(data) AS len FROM (SELECT REPEAT('☃', 21845) AS data ) AS sub;
+len
+65535
+SELECT LENGTH(data) AS len FROM (SELECT REPEAT('☃', 21846) AS data ) AS sub;
+len
+65538
+SELECT LENGTH(data) AS len FROM (SELECT REPEAT('☃', 65535) AS data ) AS sub;
+len
+196605
+SELECT LENGTH(data) AS len FROM (SELECT REPEAT('☃', 65536) AS data ) AS sub;
+len
+196608
+#
 # End of 5.5 tests
 #
 #
diff --git a/mysql-test/r/ctype_utf8mb4_uca.result b/mysql-test/r/ctype_utf8mb4_uca.result
index 1057b9743f4..4792d746709 100644
--- a/mysql-test/r/ctype_utf8mb4_uca.result
+++ b/mysql-test/r/ctype_utf8mb4_uca.result
@@ -5315,3 +5315,703 @@ DROP TABLE t1;
 #
 # End of MariaDB-10.0 tests
 #
+#
+# Start of 10.1 tests
+#
+SET NAMES utf8;
+SET collation_connection=utf8mb4_thai_520_w2;
+#
+# Start of ctype_uca_w2.inc
+#
+SELECT @@collation_connection;
+@@collation_connection
+utf8mb4_thai_520_w2
+SELECT ID, SORTLEN, COLLATION_NAME, CHARACTER_SET_NAME
+FROM INFORMATION_SCHEMA.COLLATIONS
+WHERE COLLATION_NAME LIKE @@collation_connection;
+ID	SORTLEN	COLLATION_NAME	CHARACTER_SET_NAME
+610	4	utf8mb4_thai_520_w2	utf8mb4
+#
+# Testing strnxfrm
+#
+CREATE TABLE t1 AS SELECT SPACE(3) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_thai_520_w2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (SPACE(0)),(SPACE(1)),(SPACE(2));
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(8))) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(8)))
+	020A020A020A020A020A020A020A020A00200020002000200020002000200020
+20	020A020A020A020A020A020A020A020A00200020002000200020002000200020
+2020	020A020A020A020A020A020A020A020A00200020002000200020002000200020
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(4))) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(4)))
+	020A020A020A020A0020002000200020
+20	020A020A020A020A0020002000200020
+2020	020A020A020A020A0020002000200020
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 1)) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 1))
+	020A020A020A020A
+20	020A020A020A020A
+2020	020A020A020A020A
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 2)) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 2))
+	0020002000200020
+20	0020002000200020
+2020	0020002000200020
+SELECT HEX(a), HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 3)) FROM t1;
+HEX(a)	HEX(WEIGHT_STRING(a AS CHAR(4) LEVEL 3))
+	0020002000200020
+20	0020002000200020
+2020	0020002000200020
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT SPACE(10) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_thai_520_w2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (_ucs2 0x3400);
+INSERT INTO t1 VALUES (_ucs2 0xF001);
+SELECT HEX(CONVERT(a USING ucs2)) AS ucs2, HEX(a), HEX(WEIGHT_STRING(a)) FROM t1;
+ucs2	HEX(a)	HEX(WEIGHT_STRING(a))
+3400	E39080	FB80B4000020
+F001	EF8081	FBC1F0010020
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT SPACE(10) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_thai_520_w2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('A'),('À'),('Á'),('Â'),('Ã'),('Ä'),('Å');
+SELECT a, HEX(WEIGHT_STRING(a LEVEL 2)) FROM t1 ORDER BY a;
+a	HEX(WEIGHT_STRING(a LEVEL 2))
+A	0020
+Á	00200032
+À	00200035
+Â	0020003C
+Å	00200043
+Ä	00200047
+Ã	0020004E
+SELECT a, HEX(WEIGHT_STRING(a LEVEL 2)) FROM t1 ORDER BY a DESC;
+a	HEX(WEIGHT_STRING(a LEVEL 2))
+Ã	0020004E
+Ä	00200047
+Å	00200043
+Â	0020003C
+À	00200035
+Á	00200032
+A	0020
+SET @backup_character_set_connection=@@character_set_connection;
+SET @backup_collation_connection=@@collation_connection;
+SET NAMES utf8;
+SET @stmt= CONCAT('ALTER TABLE t1 MODIFY a CHAR(10)' COLLATE utf8_bin,
+' CHARACTER SET ',  @backup_character_set_connection,
+' COLLATE ',  @backup_collation_connection);
+PREPARE stmt FROM @stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+SET collation_connection=@backup_collation_connection;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_thai_520_w2 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT a, HEX(WEIGHT_STRING(a LEVEL 2)) FROM t1 ORDER BY a;
+a	HEX(WEIGHT_STRING(a LEVEL 2))
+A	0020
+Á	00200032
+À	00200035
+Â	0020003C
+Å	00200043
+Ä	00200047
+Ã	0020004E
+SELECT a, HEX(WEIGHT_STRING(a LEVEL 2)) FROM t1 ORDER BY a DESC;
+a	HEX(WEIGHT_STRING(a LEVEL 2))
+Ã	0020004E
+Ä	00200047
+Å	00200043
+Â	0020003C
+À	00200035
+Á	00200032
+A	0020
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT SPACE(10) AS a LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_thai_520_w2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('cota'),('cote'),('cotz');
+INSERT INTO t1 VALUES ('coté'),('côte'),('côté'),('cotë'),('côtë');
+SELECT * FROM t1 ORDER BY a;
+a
+cota
+cote
+coté
+cotë
+côte
+côté
+côtë
+cotz
+SELECT * FROM t1 ORDER BY a DESC;
+a
+cotz
+côtë
+côté
+côte
+cotë
+coté
+cote
+cota
+DROP TABLE t1;
+#
+# End of ctype_uca_w2.inc
+#
+#
+# Start of ctype_thai.inc
+#
+CREATE TABLE t1 (a VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_bin);
+INSERT INTO t1 VALUES ('-กระแย่ง');
+INSERT INTO t1 VALUES ('กก');
+INSERT INTO t1 VALUES ('กราบ');
+INSERT INTO t1 VALUES ('ข่าง');
+INSERT INTO t1 VALUES ('ข้าง');
+INSERT INTO t1 VALUES ('ข้างกระดาน');
+INSERT INTO t1 VALUES ('ข้างขึ้น');
+INSERT INTO t1 VALUES ('ข้างควาย');
+INSERT INTO t1 VALUES ('ข้างเงิน');
+INSERT INTO t1 VALUES ('ข้างแรม');
+INSERT INTO t1 VALUES ('ข้างออก');
+INSERT INTO t1 VALUES ('ข้างๆ');
+INSERT INTO t1 VALUES ('ข้างๆ คูๆ');
+INSERT INTO t1 VALUES ('ขาง');
+INSERT INTO t1 VALUES ('แข็ง');
+INSERT INTO t1 VALUES ('แข่ง');
+INSERT INTO t1 VALUES ('แข่งขัน');
+INSERT INTO t1 VALUES ('แข้ง');
+INSERT INTO t1 VALUES ('แข้งขวา');
+INSERT INTO t1 VALUES ('แข็งขัน');
+INSERT INTO t1 VALUES ('ทูลเกล้า');
+INSERT INTO t1 VALUES ('ทูลเกล้าทูลกระหม่อม');
+INSERT INTO t1 VALUES ('ทูลเกล้าฯ');
+INSERT INTO t1 VALUES ('บุญญา');
+INSERT INTO t1 VALUES ('บุญ-หลง');
+INSERT INTO t1 VALUES ('บุญหลง');
+INSERT INTO t1 VALUES ('ป่า');
+INSERT INTO t1 VALUES ('ป่าน');
+INSERT INTO t1 VALUES ('ป้า');
+INSERT INTO t1 VALUES ('ป้าน');
+INSERT INTO t1 VALUES ('ป๊า');
+INSERT INTO t1 VALUES ('ป๊าน');
+INSERT INTO t1 VALUES ('ป๋า');
+INSERT INTO t1 VALUES ('ป๋าน');
+INSERT INTO t1 VALUES ('ปา');
+INSERT INTO t1 VALUES ('ปาน');
+INSERT INTO t1 VALUES ('แป้ง');
+INSERT INTO t1 VALUES ('พณิชย์');
+INSERT INTO t1 VALUES ('ม้า');
+INSERT INTO t1 VALUES ('ฯพณฯ');
+INSERT INTO t1 VALUES ('A');
+INSERT INTO t1 VALUES ('a');
+INSERT INTO t1 VALUES ('a\'');
+INSERT INTO t1 VALUES ('A-');
+INSERT INTO t1 VALUES ('a-');
+INSERT INTO t1 VALUES ('-a');
+INSERT INTO t1 VALUES ('A.');
+INSERT INTO t1 VALUES ('a.');
+INSERT INTO t1 VALUES ('A-1');
+INSERT INTO t1 VALUES ('aa');
+INSERT INTO t1 VALUES ('AA');
+INSERT INTO t1 VALUES ('A.A.');
+INSERT INTO t1 VALUES ('a.a.');
+INSERT INTO t1 VALUES ('AAA');
+INSERT INTO t1 VALUES ('A.A.A.');
+INSERT INTO t1 VALUES ('AAAA');
+INSERT INTO t1 VALUES ('A.A.A.L.');
+INSERT INTO t1 VALUES ('A.A.A.S.');
+INSERT INTO t1 VALUES ('Aachen');
+INSERT INTO t1 VALUES ('A.A.E.');
+INSERT INTO t1 VALUES ('A.Ae.E.');
+INSERT INTO t1 VALUES ('A.A.E.E.');
+INSERT INTO t1 VALUES ('AAES');
+INSERT INTO t1 VALUES ('AAF');
+INSERT INTO t1 VALUES ('A.Agr');
+INSERT INTO t1 VALUES ('aah');
+INSERT INTO t1 VALUES ('@@@@@');
+INSERT INTO t1 VALUES ('0000');
+INSERT INTO t1 VALUES ('9999');
+INSERT INTO t1 VALUES ('Aalborg');
+INSERT INTO t1 VALUES ('aide');
+INSERT INTO t1 VALUES ('air');
+INSERT INTO t1 VALUES ('@@@air');
+INSERT INTO t1 VALUES ('air@@@');
+INSERT INTO t1 VALUES ('C.A.F');
+INSERT INTO t1 VALUES ('Canon');
+INSERT INTO t1 VALUES ('coop');
+INSERT INTO t1 VALUES ('co-op');
+INSERT INTO t1 VALUES ('COOP');
+INSERT INTO t1 VALUES ('CO-OP');
+INSERT INTO t1 VALUES ('Copenhegen');
+INSERT INTO t1 VALUES ('McArthur');
+INSERT INTO t1 VALUES ('Mc Arthur');
+INSERT INTO t1 VALUES ('Mc Mahon');
+INSERT INTO t1 VALUES ('vice-president');
+INSERT INTO t1 VALUES ('vice versa');
+INSERT INTO t1 VALUES ('vice-versa');
+INSERT INTO t1 VALUES ('10 ลิตร');
+INSERT INTO t1 VALUES ('๑๐ ลิตร');
+INSERT INTO t1 VALUES ('10 litre');
+INSERT INTO t1 VALUES ('10 litre (10 ลิตร)');
+INSERT INTO t1 VALUES ('10 ลิตร (10 litre)');
+INSERT INTO t1 VALUES ('10 litre (๑๐ ลิตร)');
+INSERT INTO t1 VALUES ('๑๐ ลิตร (10 litre)');
+INSERT INTO t1 VALUES ('๑๐ ลิตร [10 litre]');
+INSERT INTO t1 VALUES ('๑๐ ลิตร {10 litre}');
+ALTER TABLE t1 ORDER BY a;
+SET @backup_character_set_connection=@@character_set_connection;
+SET @backup_collation_connection=@@collation_connection;
+SET NAMES utf8;
+SET @stmt= CONCAT('ALTER TABLE t1 MODIFY a VARCHAR(30)',
+' CHARACTER SET ', @backup_character_set_connection,
+' COLLATE ', @backup_collation_connection,
+' , ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY'
+                  ' , ADD KEY a_id (a, id)');
+PREPARE stmt FROM @stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+SET collation_connection=@backup_collation_connection;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_thai_520_w2 DEFAULT NULL,
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`),
+  KEY `a_id` (`a`,`id`)
+) ENGINE=MyISAM AUTO_INCREMENT=97 DEFAULT CHARSET=latin1
+#
+# Ascending sort, using filesort
+#
+EXPLAIN SELECT a FROM t1 ORDER BY a, BINARY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a_id	127	NULL	96	Using index; Using filesort
+SELECT a FROM t1 ORDER BY a, BINARY a;
+a
+-a
+-กระแย่ง
+@@@@@
+@@@air
+0000
+10 litre
+10 litre (10 ลิตร)
+10 litre (๑๐ ลิตร)
+10 ลิตร
+๑๐ ลิตร
+10 ลิตร (10 litre)
+๑๐ ลิตร (10 litre)
+๑๐ ลิตร [10 litre]
+๑๐ ลิตร {10 litre}
+9999
+A
+a
+A-
+a-
+A-1
+A.
+a.
+A.A.
+a.a.
+A.A.A.
+A.A.A.L.
+A.A.A.S.
+A.A.E.
+A.A.E.E.
+A.Ae.E.
+A.Agr
+a'
+AA
+aa
+AAA
+AAAA
+Aachen
+AAES
+AAF
+aah
+Aalborg
+aide
+air
+air@@@
+C.A.F
+Canon
+CO-OP
+co-op
+COOP
+coop
+Copenhegen
+Mc Arthur
+Mc Mahon
+McArthur
+vice versa
+vice-president
+vice-versa
+กก
+กราบ
+ขาง
+ข่าง
+ข้าง
+ข้างๆ
+ข้างๆ คูๆ
+ข้างกระดาน
+ข้างขึ้น
+ข้างควาย
+ข้างเงิน
+ข้างแรม
+ข้างออก
+แข็ง
+แข่ง
+แข้ง
+แข้งขวา
+แข็งขัน
+แข่งขัน
+ทูลเกล้า
+ทูลเกล้าทูลกระหม่อม
+ทูลเกล้าฯ
+บุญ-หลง
+บุญญา
+บุญหลง
+ปา
+ป่า
+ป้า
+ป๊า
+ป๋า
+ปาน
+ป่าน
+ป้าน
+ป๊าน
+ป๋าน
+แป้ง
+พณิชย์
+ม้า
+ฯพณฯ
+#
+# Descending sort, using filesort
+#
+EXPLAIN SELECT a FROM t1 ORDER BY a DESC, BINARY a DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a_id	127	NULL	96	Using index; Using filesort
+SELECT a FROM t1 ORDER BY a DESC, BINARY a DESC;
+a
+ฯพณฯ
+ม้า
+พณิชย์
+แป้ง
+ป๋าน
+ป๊าน
+ป้าน
+ป่าน
+ปาน
+ป๋า
+ป๊า
+ป้า
+ป่า
+ปา
+บุญหลง
+บุญญา
+บุญ-หลง
+ทูลเกล้าฯ
+ทูลเกล้าทูลกระหม่อม
+ทูลเกล้า
+แข่งขัน
+แข็งขัน
+แข้งขวา
+แข้ง
+แข่ง
+แข็ง
+ข้างออก
+ข้างแรม
+ข้างเงิน
+ข้างควาย
+ข้างขึ้น
+ข้างกระดาน
+ข้างๆ คูๆ
+ข้างๆ
+ข้าง
+ข่าง
+ขาง
+กราบ
+กก
+vice-versa
+vice-president
+vice versa
+McArthur
+Mc Mahon
+Mc Arthur
+Copenhegen
+coop
+COOP
+co-op
+CO-OP
+Canon
+C.A.F
+air@@@
+air
+aide
+Aalborg
+aah
+AAF
+AAES
+Aachen
+AAAA
+AAA
+aa
+AA
+a'
+A.Agr
+A.Ae.E.
+A.A.E.E.
+A.A.E.
+A.A.A.S.
+A.A.A.L.
+A.A.A.
+a.a.
+A.A.
+a.
+A.
+A-1
+a-
+A-
+a
+A
+9999
+๑๐ ลิตร {10 litre}
+๑๐ ลิตร [10 litre]
+๑๐ ลิตร (10 litre)
+10 ลิตร (10 litre)
+๑๐ ลิตร
+10 ลิตร
+10 litre (๑๐ ลิตร)
+10 litre (10 ลิตร)
+10 litre
+0000
+@@@air
+@@@@@
+-กระแย่ง
+-a
+#
+# Ascending sort, using index
+#
+EXPLAIN SELECT a FROM t1 ORDER BY a, id;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a_id	127	NULL	96	Using index
+SELECT a FROM t1 ORDER BY a, id;
+a
+-a
+-กระแย่ง
+@@@@@
+@@@air
+0000
+10 litre
+10 litre (10 ลิตร)
+10 litre (๑๐ ลิตร)
+10 ลิตร
+๑๐ ลิตร
+10 ลิตร (10 litre)
+๑๐ ลิตร (10 litre)
+๑๐ ลิตร [10 litre]
+๑๐ ลิตร {10 litre}
+9999
+A
+a
+A-
+a-
+A-1
+A.
+a.
+A.A.
+a.a.
+A.A.A.
+A.A.A.L.
+A.A.A.S.
+A.A.E.
+A.A.E.E.
+A.Ae.E.
+A.Agr
+a'
+AA
+aa
+AAA
+AAAA
+Aachen
+AAES
+AAF
+aah
+Aalborg
+aide
+air
+air@@@
+C.A.F
+Canon
+CO-OP
+co-op
+COOP
+coop
+Copenhegen
+Mc Arthur
+Mc Mahon
+McArthur
+vice versa
+vice-president
+vice-versa
+กก
+กราบ
+ขาง
+ข่าง
+ข้าง
+ข้างๆ
+ข้างๆ คูๆ
+ข้างกระดาน
+ข้างขึ้น
+ข้างควาย
+ข้างเงิน
+ข้างแรม
+ข้างออก
+แข็ง
+แข่ง
+แข้ง
+แข้งขวา
+แข็งขัน
+แข่งขัน
+ทูลเกล้า
+ทูลเกล้าทูลกระหม่อม
+ทูลเกล้าฯ
+บุญ-หลง
+บุญญา
+บุญหลง
+ปา
+ป่า
+ป้า
+ป๊า
+ป๋า
+ปาน
+ป่าน
+ป้าน
+ป๊าน
+ป๋าน
+แป้ง
+พณิชย์
+ม้า
+ฯพณฯ
+#
+# Descending sort, using index
+#
+EXPLAIN SELECT a FROM t1 ORDER BY a DESC, id DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a_id	127	NULL	96	Using index
+SELECT a FROM t1 ORDER BY a DESC, id DESC;
+a
+ฯพณฯ
+ม้า
+พณิชย์
+แป้ง
+ป๋าน
+ป๊าน
+ป้าน
+ป่าน
+ปาน
+ป๋า
+ป๊า
+ป้า
+ป่า
+ปา
+บุญหลง
+บุญญา
+บุญ-หลง
+ทูลเกล้าฯ
+ทูลเกล้าทูลกระหม่อม
+ทูลเกล้า
+แข่งขัน
+แข็งขัน
+แข้งขวา
+แข้ง
+แข่ง
+แข็ง
+ข้างออก
+ข้างแรม
+ข้างเงิน
+ข้างควาย
+ข้างขึ้น
+ข้างกระดาน
+ข้างๆ คูๆ
+ข้างๆ
+ข้าง
+ข่าง
+ขาง
+กราบ
+กก
+vice-versa
+vice-president
+vice versa
+McArthur
+Mc Mahon
+Mc Arthur
+Copenhegen
+coop
+COOP
+co-op
+CO-OP
+Canon
+C.A.F
+air@@@
+air
+aide
+Aalborg
+aah
+AAF
+AAES
+Aachen
+AAAA
+AAA
+aa
+AA
+a'
+A.Agr
+A.Ae.E.
+A.A.E.E.
+A.A.E.
+A.A.A.S.
+A.A.A.L.
+A.A.A.
+a.a.
+A.A.
+a.
+A.
+A-1
+a-
+A-
+a
+A
+9999
+๑๐ ลิตร {10 litre}
+๑๐ ลิตร [10 litre]
+๑๐ ลิตร (10 litre)
+10 ลิตร (10 litre)
+๑๐ ลิตร
+10 ลิตร
+10 litre (๑๐ ลิตร)
+10 litre (10 ลิตร)
+10 litre
+0000
+@@@air
+@@@@@
+-กระแย่ง
+-a
+DROP TABLE t1;
+#
+# End of ctype_thai.inc
+#
+#
+# End of 10.1 tests
+#
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 639942f0da9..5783247b673 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -1101,7 +1101,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	100.00	
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
+Note	1003	select 6 AS `a`,5 AS `b` from `test`.`t3` where 1
 SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0 
 WHERE (t.a,t.b) NOT IN (SELECT 7, 5);
 a	b
@@ -1115,7 +1115,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	100.00	
 3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
+Note	1003	select 6 AS `a`,5 AS `b` from `test`.`t3` where 1
 SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0 
 WHERE (t.a,t.b) NOT IN (SELECT 7, 5);
 a	b
@@ -1129,7 +1129,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	100.00	
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
+Note	1003	select 6 AS `a`,5 AS `b` from `test`.`t3` where 1
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 #
diff --git a/mysql-test/r/events_1.result b/mysql-test/r/events_1.result
index 70443604f41..fc8d02e5e93 100644
--- a/mysql-test/r/events_1.result
+++ b/mysql-test/r/events_1.result
@@ -475,6 +475,26 @@ DROP EVENT ev1;
 
 SHOW EVENTS;
 Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
+#
+# MDEV-9524 Cannot load from mysql.event when sql_mode is set to PAD_CHAR_TO_FULL_LENGTH
+#
+CREATE TABLE t1 (a INT);
+CREATE EVENT ev1 ON SCHEDULE EVERY 5 SECOND DO DELETE FROM t1;
+SHOW EVENTS;
+Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
+events_test	ev1	root@localhost	SYSTEM	RECURRING	NULL	5	#	#	NULL	ENABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
+SET sql_mode=PAD_CHAR_TO_FULL_LENGTH;
+SHOW EVENTS;
+Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
+events_test	ev1	root@localhost	SYSTEM	RECURRING	NULL	5	#	#	NULL	ENABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
+DROP EVENT ev1;
+CREATE EVENT ev1 ON SCHEDULE EVERY 5 SECOND DO DELETE FROM t1;
+SHOW EVENTS;
+Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
+events_test	ev1	root@localhost	SYSTEM	RECURRING	NULL	5	#	#	NULL	ENABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
+DROP EVENT ev1;
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
 
 # 
 # End of tests
diff --git a/mysql-test/r/fulltext3.result b/mysql-test/r/fulltext3.result
index 4ec48369ad1..c0b871cd5a7 100644
--- a/mysql-test/r/fulltext3.result
+++ b/mysql-test/r/fulltext3.result
@@ -15,3 +15,15 @@ CREATE TABLE t1(a VARCHAR(2) CHARACTER SET big5 COLLATE big5_chinese_ci,
 FULLTEXT(a));
 INSERT INTO t1 VALUES(0xA3C2);
 DROP TABLE t1;
+create table t1 (
+id varchar(255),
+business_name text null collate utf8mb4_unicode_ci,
+street_address text,
+fulltext index ft (business_name),
+fulltext index ft2 (street_address)
+);
+select * from t1 where match (business_name, street_address) against ('some business name and address here');
+ERROR HY000: Can't find FULLTEXT index matching the column list
+select * from t1 where match (business_name, street_address) against ('some business name and address here' in boolean mode);
+id	business_name	street_address
+drop table t1;
diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result
index 33a997c8004..1855fa72523 100644
--- a/mysql-test/r/func_in.result
+++ b/mysql-test/r/func_in.result
@@ -813,6 +813,25 @@ EXECUTE s;
 DROP TABLE t1;
 # End of 5.3 tests
 #
+# Start of 10.0 tests
+#
+#
+# MDEV-10020 InnoDB NOT IN Query Crash When One Item Is NULL
+#
+CREATE TABLE t1
+(
+a INT(11),
+b VARCHAR(10),
+KEY (b)
+);
+INSERT INTO t1 VALUES (1,'x'),(2,'y'),(3,'z');
+SELECT * FROM t1 WHERE b NOT IN (NULL, '', 'A');
+a	b
+DROP TABLE t1;
+#
+# Start of 10.1 tests
+#
+#
 # MDEV-8755 Equal field propagation is not performed any longer for the IN list when multiple comparison types
 #
 CREATE TABLE t1 (a INT);
diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result
index 2a646cbcb8d..f7ca1aa3d03 100644
--- a/mysql-test/r/func_math.result
+++ b/mysql-test/r/func_math.result
@@ -667,9 +667,14 @@ ERROR 22003: BIGINT UNSIGNED value is out of range in '(18446744073709551615 DIV
 CREATE TABLE t1(a BIGINT, b BIGINT UNSIGNED);
 INSERT INTO t1 VALUES(-9223372036854775808, 9223372036854775809);
 SELECT -a FROM t1;
-ERROR 22003: BIGINT value is out of range in '-(-9223372036854775808)'
+ERROR 22003: BIGINT value is out of range in '-(`test`.`t1`.`a`)'
 SELECT -b FROM t1;
-ERROR 22003: BIGINT value is out of range in '-(9223372036854775809)'
+ERROR 22003: BIGINT value is out of range in '-(`test`.`t1`.`b`)'
+INSERT INTO t1 VALUES(0,0);
+SELECT -a FROM t1;
+ERROR 22003: BIGINT value is out of range in '-(`test`.`t1`.`a`)'
+SELECT -b FROM t1;
+ERROR 22003: BIGINT value is out of range in '-(`test`.`t1`.`b`)'
 DROP TABLE t1;
 SET @a:=999999999999999999999999999999999999999999999999999999999999999999999999999999999;
 SELECT @a + @a;
diff --git a/mysql-test/r/grant5.result b/mysql-test/r/grant5.result
index 2df394c0432..d7f3b6812bb 100644
--- a/mysql-test/r/grant5.result
+++ b/mysql-test/r/grant5.result
@@ -1,2 +1,18 @@
 SHOW GRANTS FOR root@invalid_host;
 ERROR 42000: There is no such grant defined for user 'root' on host 'invalid_host'
+create user test;
+create user foo;
+create role foo;
+grant foo to test;
+set role foo;
+show grants for test;
+Grants for test@%
+GRANT foo TO 'test'@'%'
+GRANT USAGE ON *.* TO 'test'@'%'
+show grants for foo;
+Grants for foo
+GRANT USAGE ON *.* TO 'foo'
+show grants for foo@'%';
+ERROR 42000: Access denied for user 'test'@'%' to database 'mysql'
+drop user test, foo;
+drop role foo;
diff --git a/mysql-test/r/have_crypt.require b/mysql-test/r/have_crypt.require
deleted file mode 100644
index 739fbb738f0..00000000000
--- a/mysql-test/r/have_crypt.require
+++ /dev/null
@@ -1,2 +0,0 @@
-Variable_name	Value
-have_crypt	YES
diff --git a/mysql-test/r/help.result b/mysql-test/r/help.result
index 3a266567175..aefe6f8381e 100644
--- a/mysql-test/r/help.result
+++ b/mysql-test/r/help.result
@@ -148,6 +148,21 @@ help 'impossible_category_1';
 source_category_name	name	is_it_category
 impossible_category_1	impossible_function_1	N
 impossible_category_1	impossible_function_2	N
+# MDEV-9524 Cannot load from mysql.event when sql_mode is set to PAD_CHAR_TO_FULL_LENGTH
+help 'impossible_function_1';
+name	description	example
+impossible_function_1	description of 
+ impossible_function1
+	example of 
+ impossible_function1
+SET sql_mode=PAD_CHAR_TO_FULL_LENGTH;
+help 'impossible_function_1';
+name	description	example
+impossible_function_1	description of 
+ impossible_function1
+	example of 
+ impossible_function1
+SET sql_mode=DEFAULT;
 set sql_mode="";
 alter table mysql.help_relation engine=innodb;
 alter table mysql.help_keyword engine=innodb;
diff --git a/mysql-test/r/information_schema_stats.result b/mysql-test/r/information_schema_stats.result
new file mode 100644
index 00000000000..cd73636879c
--- /dev/null
+++ b/mysql-test/r/information_schema_stats.result
@@ -0,0 +1,70 @@
+set global userstat=1;
+create table just_a_test(id int,first_name varchar(10),last_name varchar(10),address varchar(100),phone bigint,email varchar(30), state varchar(30));
+insert into just_a_test values(1,'fa','la','china_a',11111111,'fa_la@163.com','California'),
+(2,'fb','lb','china_b',22222222,'fb_lb@163.com','Arizona'),
+(3,'fc','lc','china_c',33333333,'fc_lc@163.com','California'),
+(4,'fd','ld','china_d',44444444,'fd_ld@163.com','Utah'),
+(5,'fe','le','china_e',55555555,'fe_le@163.com','Arizona');
+alter table just_a_test add primary key (id);
+alter table just_a_test add key IND_just_a_test_first_name_last_name(first_name,last_name);
+alter table just_a_test add key IND_just_a_test_state(state);
+select count(*) from just_a_test where first_name='fc' and last_name='lc';
+count(*)
+1
+select count(*) from just_a_test where state = 'California';
+count(*)
+2
+select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test';
+TABLE_SCHEMA	TABLE_NAME	INDEX_NAME	ROWS_READ
+test	just_a_test	IND_just_a_test_state	2
+test	just_a_test	IND_just_a_test_first_name_last_name	1
+select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test';
+TABLE_SCHEMA	TABLE_NAME	ROWS_READ	ROWS_CHANGED	ROWS_CHANGED_X_INDEXES
+test	just_a_test	18	5	5
+alter table just_a_test drop key IND_just_a_test_first_name_last_name;
+select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test';
+TABLE_SCHEMA	TABLE_NAME	INDEX_NAME	ROWS_READ
+test	just_a_test	IND_just_a_test_state	2
+select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test';
+TABLE_SCHEMA	TABLE_NAME	ROWS_READ	ROWS_CHANGED	ROWS_CHANGED_X_INDEXES
+test	just_a_test	23	5	5
+alter table just_a_test drop column state;
+select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test';
+TABLE_SCHEMA	TABLE_NAME	INDEX_NAME	ROWS_READ
+select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test';
+TABLE_SCHEMA	TABLE_NAME	ROWS_READ	ROWS_CHANGED	ROWS_CHANGED_X_INDEXES
+test	just_a_test	28	5	5
+drop table just_a_test;
+select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test';
+TABLE_SCHEMA	TABLE_NAME	INDEX_NAME	ROWS_READ
+select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test';
+TABLE_SCHEMA	TABLE_NAME	ROWS_READ	ROWS_CHANGED	ROWS_CHANGED_X_INDEXES
+create table just_a_test(id int not null primary key,first_name varchar(10),last_name varchar(10),address varchar(100),phone bigint,email varchar(30), state varchar(30),key(first_name,last_name),key(state));
+insert into just_a_test values(1,'fa','la','china_a',11111111,'fa_la@163.com','California'),
+(2,'fb','lb','china_b',22222222,'fb_lb@163.com','Arizona'),
+(3,'fc','lc','china_c',33333333,'fc_lc@163.com','California'),
+(4,'fd','ld','china_d',44444444,'fd_ld@163.com','Utah'),
+(5,'fe','le','china_e',55555555,'fe_le@163.com','Arizona');
+select count(*) from just_a_test where first_name='fc' and last_name='lc';
+count(*)
+1
+select count(*) from just_a_test where state = 'California';
+count(*)
+2
+select count(*) from just_a_test where id between 2 and 4;
+count(*)
+3
+select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test';
+TABLE_SCHEMA	TABLE_NAME	INDEX_NAME	ROWS_READ
+test	just_a_test	first_name	1
+test	just_a_test	state	2
+test	just_a_test	PRIMARY	5
+select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test';
+TABLE_SCHEMA	TABLE_NAME	ROWS_READ	ROWS_CHANGED	ROWS_CHANGED_X_INDEXES
+test	just_a_test	8	5	15
+drop table just_a_test;
+select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test';
+TABLE_SCHEMA	TABLE_NAME	INDEX_NAME	ROWS_READ
+select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test';
+TABLE_SCHEMA	TABLE_NAME	ROWS_READ	ROWS_CHANGED	ROWS_CHANGED_X_INDEXES
+set global userstat=0;
diff --git a/mysql-test/r/insert_innodb.result b/mysql-test/r/insert_innodb.result
new file mode 100644
index 00000000000..e5e2b4b8623
--- /dev/null
+++ b/mysql-test/r/insert_innodb.result
@@ -0,0 +1,45 @@
+#
+# BUG#22037930: INSERT IGNORE FAILS TO IGNORE
+#               FOREIGN KEY CONSTRAINT
+# Setup.
+CREATE TABLE t1 (fld1 INT PRIMARY KEY) ENGINE=INNODB;
+CREATE TABLE t2 (fld2 INT, FOREIGN KEY (fld2) REFERENCES t1 (fld1))
+ENGINE=INNODB;
+INSERT INTO t1 VALUES(0);
+INSERT INTO t2 VALUES(0);
+# Without fix, an error is reported.
+INSERT IGNORE INTO t2 VALUES(1);
+Warnings:
+Warning	1452	Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
+UPDATE IGNORE t2 SET fld2=20 WHERE fld2=0;
+UPDATE IGNORE t1 SET fld1=20 WHERE fld1=0;
+# Test for multi update.
+UPDATE IGNORE t1, t2 SET t2.fld2= t2.fld2 + 3;
+UPDATE IGNORE t1, t2 SET t1.fld1= t1.fld1 + 3;
+# Reports an error since IGNORE is not used.
+INSERT INTO t2 VALUES(1);
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
+UPDATE t2 SET fld2=20 WHERE fld2=0;
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
+UPDATE t1 SET fld1=20 WHERE fld1=0;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
+UPDATE t1, t2 SET t2.fld2= t2.fld2 + 3;
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
+UPDATE t1, t2 SET t1.fld1= t1.fld1 + 3;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
+DROP TABLE t2, t1;
+#
+# BUG#22037930: INSERT IGNORE FAILS TO IGNORE FOREIGN
+#               KEY CONSTRAINT
+CREATE TABLE t1 (fld1 INT PRIMARY KEY) ENGINE= INNODB;
+CREATE TABLE t2 (fld1 VARCHAR(10), fld2 INT NOT NULL,
+CONSTRAINT fk FOREIGN KEY (fld2) REFERENCES t1(fld1)) ENGINE= INNODB;
+# Without patch, reports incorrect error.
+INSERT INTO t2 VALUES('abc', 2) ON DUPLICATE KEY UPDATE fld1= 'def';
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
+REPLACE INTO t2 VALUES('abc', 2);
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
+INSERT IGNORE INTO t2 VALUES('abc', 2) ON DUPLICATE KEY UPDATE fld1= 'def';
+Warnings:
+Warning	1452	Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`))
+DROP TABLE t2, t1;
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 24dcdeacf60..2602181f234 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -2271,4 +2271,67 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	9	
 1	SIMPLE	t2	ref	c	c	5	const	393	Using where
 drop table t1,t2;
+#
+# MDEV-10006: optimizer doesn't convert outer join to inner on views with WHERE clause
+#
+CREATE TABLE t1(i1 int primary key, v1 int, key(v1));
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 VALUES (2, 2);
+INSERT INTO t1 VALUES (3, 3);
+INSERT INTO t1 VALUES (4, 4);
+INSERT INTO t1 VALUES (5, 3);
+INSERT INTO t1 VALUES (6, 6);
+INSERT INTO t1 VALUES (7, 7);
+INSERT INTO t1 VALUES (8, 8);
+INSERT INTO t1 VALUES (9, 9);
+CREATE TABLE t2(i2 int primary key, v2 int, key(v2));
+INSERT INTO t2 VALUES (1, 1);
+INSERT INTO t2 VALUES (2, 2);
+INSERT INTO t2 VALUES (3, 3);
+INSERT INTO t2 VALUES (4, 4);
+INSERT INTO t2 VALUES (5, 3);
+INSERT INTO t2 VALUES (6, 6);
+INSERT INTO t2 VALUES (7, 7);
+INSERT INTO t2 VALUES (8, 8);
+INSERT INTO t2 VALUES (9, 9);
+CREATE TABLE t3(i3 int primary key, v3 int, key(v3));
+INSERT INTO t3 VALUES (2, 2);
+INSERT INTO t3 VALUES (4, 4);
+INSERT INTO t3 VALUES (6, 6);
+INSERT INTO t3 VALUES (8, 8);
+# This should have a join order of t3,t1,t2 (or t3,t2,t1, the idea is that t3 is the first one)
+EXPLAIN EXTENDED 
+SELECT * FROM 
+(SELECT t1.i1 as i1, t1.v1 as v1,
+t2.i2 as i2, t2.v2 as v2,
+t3.i3 as i3, t3.v3 as v3
+FROM t1 JOIN t2 on t1.i1 = t2.i2
+LEFT JOIN t3 on t2.i2 = t3.i3
+) as w1 
+WHERE v3 = 4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t3	ref	PRIMARY,v3	v3	5	const	1	100.00	
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.i3	1	100.00	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.i3	1	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`i1` AS `i1`,`test`.`t1`.`v1` AS `v1`,`test`.`t2`.`i2` AS `i2`,`test`.`t2`.`v2` AS `v2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`v3` AS `v3` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`v3` = 4) and (`test`.`t1`.`i1` = `test`.`t3`.`i3`) and (`test`.`t2`.`i2` = `test`.`t3`.`i3`))
+# This should have the same join order like the query above:
+EXPLAIN EXTENDED 
+SELECT * FROM 
+(SELECT t1.i1 as i1, t1.v1 as v1,
+t2.i2 as i2, t2.v2 as v2,
+t3.i3 as i3, t3.v3 as v3
+FROM t1 JOIN t2 on t1.i1 = t2.i2
+LEFT JOIN t3 on t2.i2 = t3.i3
+WHERE t1.i1 = t2.i2
+AND 1 = 1
+) as w2 
+WHERE v3 = 4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t3	ref	PRIMARY,v3	v3	5	const	1	100.00	
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.i3	1	100.00	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.i3	1	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`i1` AS `i1`,`test`.`t1`.`v1` AS `v1`,`test`.`t2`.`i2` AS `i2`,`test`.`t2`.`v2` AS `v2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`v3` AS `v3` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`v3` = 4) and (`test`.`t1`.`i1` = `test`.`t3`.`i3`) and (`test`.`t2`.`i2` = `test`.`t3`.`i3`))
+drop table t1,t2,t3;
 SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index e20914c6e13..51f43f1404e 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -2282,6 +2282,69 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	9	
 1	SIMPLE	t2	ref	c	c	5	const	393	Using where
 drop table t1,t2;
+#
+# MDEV-10006: optimizer doesn't convert outer join to inner on views with WHERE clause
+#
+CREATE TABLE t1(i1 int primary key, v1 int, key(v1));
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 VALUES (2, 2);
+INSERT INTO t1 VALUES (3, 3);
+INSERT INTO t1 VALUES (4, 4);
+INSERT INTO t1 VALUES (5, 3);
+INSERT INTO t1 VALUES (6, 6);
+INSERT INTO t1 VALUES (7, 7);
+INSERT INTO t1 VALUES (8, 8);
+INSERT INTO t1 VALUES (9, 9);
+CREATE TABLE t2(i2 int primary key, v2 int, key(v2));
+INSERT INTO t2 VALUES (1, 1);
+INSERT INTO t2 VALUES (2, 2);
+INSERT INTO t2 VALUES (3, 3);
+INSERT INTO t2 VALUES (4, 4);
+INSERT INTO t2 VALUES (5, 3);
+INSERT INTO t2 VALUES (6, 6);
+INSERT INTO t2 VALUES (7, 7);
+INSERT INTO t2 VALUES (8, 8);
+INSERT INTO t2 VALUES (9, 9);
+CREATE TABLE t3(i3 int primary key, v3 int, key(v3));
+INSERT INTO t3 VALUES (2, 2);
+INSERT INTO t3 VALUES (4, 4);
+INSERT INTO t3 VALUES (6, 6);
+INSERT INTO t3 VALUES (8, 8);
+# This should have a join order of t3,t1,t2 (or t3,t2,t1, the idea is that t3 is the first one)
+EXPLAIN EXTENDED 
+SELECT * FROM 
+(SELECT t1.i1 as i1, t1.v1 as v1,
+t2.i2 as i2, t2.v2 as v2,
+t3.i3 as i3, t3.v3 as v3
+FROM t1 JOIN t2 on t1.i1 = t2.i2
+LEFT JOIN t3 on t2.i2 = t3.i3
+) as w1 
+WHERE v3 = 4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t3	ref	PRIMARY,v3	v3	5	const	1	100.00	
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.i3	1	100.00	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.i3	1	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`i1` AS `i1`,`test`.`t1`.`v1` AS `v1`,`test`.`t2`.`i2` AS `i2`,`test`.`t2`.`v2` AS `v2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`v3` AS `v3` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`v3` = 4) and (`test`.`t1`.`i1` = `test`.`t3`.`i3`) and (`test`.`t2`.`i2` = `test`.`t3`.`i3`))
+# This should have the same join order like the query above:
+EXPLAIN EXTENDED 
+SELECT * FROM 
+(SELECT t1.i1 as i1, t1.v1 as v1,
+t2.i2 as i2, t2.v2 as v2,
+t3.i3 as i3, t3.v3 as v3
+FROM t1 JOIN t2 on t1.i1 = t2.i2
+LEFT JOIN t3 on t2.i2 = t3.i3
+WHERE t1.i1 = t2.i2
+AND 1 = 1
+) as w2 
+WHERE v3 = 4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t3	ref	PRIMARY,v3	v3	5	const	1	100.00	
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.i3	1	100.00	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.i3	1	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`i1` AS `i1`,`test`.`t1`.`v1` AS `v1`,`test`.`t2`.`i2` AS `i2`,`test`.`t2`.`v2` AS `v2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`v3` AS `v3` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`v3` = 4) and (`test`.`t1`.`i1` = `test`.`t3`.`i3`) and (`test`.`t2`.`i2` = `test`.`t3`.`i3`))
+drop table t1,t2,t3;
 SET optimizer_switch=@save_optimizer_switch;
 set join_cache_level=default;
 show variables like 'join_cache_level';
diff --git a/mysql-test/r/locale.result b/mysql-test/r/locale.result
index 1335f1ec9d5..95c1f51fd87 100644
--- a/mysql-test/r/locale.result
+++ b/mysql-test/r/locale.result
@@ -90,6 +90,45 @@ SELECT DATE_FORMAT('2001-01-07', '%w %a %W');
 DATE_FORMAT('2001-01-07', '%w %a %W')
 0 Du Duminică
 End of 5.4 tests
+SET NAMES utf8;
+SET lc_time_names=de_AT;
+SELECT monthname('2001-01-01');
+monthname('2001-01-01')
+Jänner
+SELECT monthname('2001-02-01');
+monthname('2001-02-01')
+Februar
+SELECT monthname('2001-03-01');
+monthname('2001-03-01')
+März
+#
+# MDEV-10052 Illegal mix of collations with DAYNAME(date_field)<>varchar_field
+#
+SET NAMES utf8;
+CREATE TABLE t1 (c VARCHAR(8) CHARACTER SET latin1, d DATE);
+INSERT INTO t1 VALUES ('test',now());
+Warnings:
+Note	1265	Data truncated for column 'd' at row 1
+SET lc_time_names=ru_RU;
+SELECT c FROM t1 WHERE DAYNAME(d)<>c;
+ERROR HY000: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<>'
+SELECT c FROM t1 WHERE MONTHNAME(d)<>c;
+ERROR HY000: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<>'
+SET lc_time_names=en_US;
+SELECT c FROM t1 WHERE DAYNAME(d)<>c;
+c
+test
+SELECT c FROM t1 WHERE MONTHNAME(d)<>c;
+c
+test
+SET NAMES latin1;
+SELECT c FROM t1 WHERE DAYNAME(d)<>c;
+c
+test
+SELECT c FROM t1 WHERE MONTHNAME(d)<>c;
+c
+test
+DROP TABLE t1;
 #
 # Start of 5.6 tests
 #
diff --git a/mysql-test/r/mdev6830.result b/mysql-test/r/mdev6830.result
index 0570659e860..d1cf8c98ac1 100644
--- a/mysql-test/r/mdev6830.result
+++ b/mysql-test/r/mdev6830.result
@@ -1,5 +1,4 @@
-drop table if exists t1,t2,t3;
-drop view if exists v2,v3;
+set @@debug_dbug= 'd,opt';
 CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=MyISAM;
 CREATE TABLE t2 (
 f1 DATE,
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index 204596bf20b..b9c12be8d0d 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -1061,4 +1061,36 @@ f1	f2
 1	97
 DROP TABLE t1, t2;
 DROP VIEW v1;
+#
+# MDEV-5973: MySQL  Bug#11757486:49539: NON-DESCRIPTIVE ERR (ERROR 0
+# FROM STORAGE ENGINE) WITH MULTI-TABLE UPDATE
+#
+CREATE TABLE table_11757486 (field1 tinyint) ENGINE=INNODB;
+INSERT INTO table_11757486 VALUES (0),(0);
+SET SESSION SQL_MODE='STRICT_ALL_TABLES';
+UPDATE IGNORE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1;
+Warnings:
+Warning	1264	Out of range value for column 'field1' at row 1
+Warning	1264	Out of range value for column 'field1' at row 2
+UPDATE IGNORE table_11757486 SET field1=128;
+Warnings:
+Warning	1264	Out of range value for column 'field1' at row 1
+Warning	1264	Out of range value for column 'field1' at row 2
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. UPDATE IGNORE is unsafe because the order in which rows are updated determines which (if any) rows are ignored. This order cannot be predicted and may differ on master and the slave.
+UPDATE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1;
+ERROR 22003: Out of range value for column 'field1' at row 1
+UPDATE table_11757486 SET field1=128;
+ERROR 22003: Out of range value for column 'field1' at row 1
+SET SESSION SQL_MODE='';
+UPDATE IGNORE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1;
+Warnings:
+Warning	1264	Out of range value for column 'field1' at row 1
+Warning	1264	Out of range value for column 'field1' at row 2
+UPDATE IGNORE table_11757486 SET field1=128;
+Warnings:
+Warning	1264	Out of range value for column 'field1' at row 1
+Warning	1264	Out of range value for column 'field1' at row 2
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. UPDATE IGNORE is unsafe because the order in which rows are updated determines which (if any) rows are ignored. This order cannot be predicted and may differ on master and the slave.
+DROP TABLE table_11757486;
+SET SESSION SQL_MODE=default;
 end of 10.0 tests
diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result
index 033b4086985..e0d93b02ecd 100644
--- a/mysql-test/r/mysqlcheck.result
+++ b/mysql-test/r/mysqlcheck.result
@@ -376,6 +376,11 @@ Repairing views
 test.v1                                            OK
 drop view v1;
 drop table t1;
+create table `#mysql50#t1``1` (a int) engine=myisam;
+show tables;
+Tables_in_test
+t1`1
+drop table `t1``1`;
 #
 #MDEV-7384 [PATCH] add PERSISENT FOR ALL option to mysqlanalyze/mysqlcheck
 #
diff --git a/mysql-test/r/mysqld--help,win.rdiff b/mysql-test/r/mysqld--help,win.rdiff
index a3bf5c55ee8..ecbe1843877 100644
--- a/mysql-test/r/mysqld--help,win.rdiff
+++ b/mysql-test/r/mysqld--help,win.rdiff
@@ -126,7 +126,7 @@
  table-definition-cache 400
 -table-open-cache 431
 +table-open-cache 2000
- tc-heuristic-recover COMMIT
+ tc-heuristic-recover OFF
  thread-cache-size 151
 -thread-pool-idle-timeout 60
  thread-pool-max-threads 1000
diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result
index cc432652db8..be47bb55f42 100644
--- a/mysql-test/r/mysqld--help.result
+++ b/mysql-test/r/mysqld--help.result
@@ -577,7 +577,7 @@ The following options may be given as the first argument:
  semijoin_with_cache, join_cache_incremental, 
  join_cache_hashed, join_cache_bka, 
  optimize_join_buffer_size, table_elimination, 
- extended_keys, exists_to_in
+ extended_keys, exists_to_in, orderby_uses_equalities
  --optimizer-use-condition-selectivity=# 
  Controls selectivity of which conditions the optimizer
  takes into account to calculate cardinality of a partial
@@ -1065,7 +1065,7 @@ The following options may be given as the first argument:
  --table-open-cache=# 
  The number of cached open tables
  --tc-heuristic-recover=name 
- Decision to use in heuristic recover process. One of: 
+ Decision to use in heuristic recover process. One of: OFF,
  COMMIT, ROLLBACK
  --thread-cache-size=# 
  How many threads we should keep in a cache for reuse.
@@ -1156,10 +1156,8 @@ bulk-insert-buffer-size 8388608
 changed-page-bitmaps ON
 character-set-client-handshake TRUE
 character-set-filesystem binary
-character-set-server latin1
 character-sets-dir MYSQL_CHARSETSDIR/
 chroot (No default value)
-collation-server latin1_swedish_ci
 completion-type NO_CHAIN
 concurrent-insert AUTO
 console FALSE
@@ -1426,7 +1424,7 @@ sysdate-is-now FALSE
 table-cache 431
 table-definition-cache 400
 table-open-cache 431
-tc-heuristic-recover COMMIT
+tc-heuristic-recover OFF
 thread-cache-size 151
 thread-pool-idle-timeout 60
 thread-pool-max-threads 1000
diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result
index d98b7f5e091..222ccc809b1 100644
--- a/mysql-test/r/null.result
+++ b/mysql-test/r/null.result
@@ -1559,7 +1559,7 @@ set names utf8;
 create table t1 (f1 varchar(10));
 insert into t1 values ('2015-12-31');
 select power( timestamp( nullif( '2002-09-08', f1 ) ), 24 ) from t1;
-ERROR 22003: DOUBLE value is out of range in 'pow(cast((case when '2002-09-08' = '2015-12-31' then NULL else '2002-09-08' end) as datetime(6)),24)'
+ERROR 22003: DOUBLE value is out of range in 'pow(cast((case when '2002-09-08' = `test`.`t1`.`f1` then NULL else '2002-09-08' end) as datetime(6)),24)'
 drop table t1;
 CREATE TABLE t1 (f1 INT);
 INSERT INTO t1 VALUES (1),(2);
@@ -1575,5 +1575,22 @@ SELECT * FROM t1 WHERE NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(N
 i
 DROP TABLE t1;
 #
+# MDEV-10236 Where expression with NOT function gives incorrect result
+#
+CREATE TABLE t1 (c1 INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE ((c1 IS NOT NULL) >= (NOT TRUE)) IS NOT NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where (((`test`.`t1`.`c1` is not null) >= <cache>((not(1)))) is not null)
+SELECT * FROM t1 WHERE ((c1 IS NOT NULL) >= (NOT TRUE)) IS NOT NULL;
+c1
+1
+2
+3
+DROP TABLE t1;
+#
 # End of 10.1 tests
 #
diff --git a/mysql-test/r/openssl_1.result b/mysql-test/r/openssl_1.result
index 0aa4acb431c..12c91721381 100644
--- a/mysql-test/r/openssl_1.result
+++ b/mysql-test/r/openssl_1.result
@@ -228,3 +228,12 @@ connection default;
 DROP USER bug42158@localhost;
 set global sql_mode=default;
 End of 5.1 tests
+/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
+/*!40019 SET @@session.max_insert_delayed_threads=0*/;
+/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
+DELIMITER /*!*/;
+DELIMITER ;
+# End of log file
+ROLLBACK /* added by mysqlbinlog */;
+/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
+/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 697ebe26ad2..3897184f3b1 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -2983,3 +2983,148 @@ EXPLAIN SELECT id1 FROM t2 WHERE id2=1 AND id3=1 ORDER BY date DESC LIMIT 0,4;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ref	id_23_date,id_234_date	id_23_date	2	const,const	3	Using where
 drop table t1,t2;
+#
+# MDEV-8989: ORDER BY optimizer ignores equality propagation
+#
+set @tmp_8989=@@optimizer_switch;
+set optimizer_switch='orderby_uses_equalities=on';
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1(a int);
+insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+create table t2 (
+pk int primary key, 
+a int, b int, 
+filler char(200), 
+key(a)
+);
+insert into t2 select a, 1000-a, 1000-a, repeat('abc-',50) from t1 where a<200 limit 200;
+create table t3 (
+pk int primary key, 
+a int, b int, 
+filler char(200), 
+key(a)
+);
+insert into t3 select a,      1000-a, 1000-a, repeat('abc-',50) from t1;
+insert into t3 select a+1000, 1000+a, 1000+a, repeat('abc-',50) from t1;
+# The optimizer produces an order of 't2,t3' for this join
+#
+# Case #1 (from the bug report): 
+#  Q1 can take advantage of t2.a to resolve ORDER BY limit w/o sorting
+explain
+select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b 
+from t2, t3 where t2.a=t3.a order by t2.a limit 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	a	a	5	NULL	5	Using where
+1	SIMPLE	t3	ref	a	a	5	test.t2.a	1	
+# 
+# This is Q2 which used to have "Using temporary; using filesort" but
+#   has the same query plan as Q1:
+# 
+explain
+select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b 
+from t2, t3 where t2.a=t3.a order by t3.a limit 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	a	a	5	NULL	5	Using where
+1	SIMPLE	t3	ref	a	a	5	test.t2.a	1	
+select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b 
+from t2, t3 where t2.a=t3.a order by t2.a limit 5;
+pk	a	b	pk	a	b
+199	801	801	199	801	801
+198	802	802	198	802	802
+197	803	803	197	803	803
+196	804	804	196	804	804
+195	805	805	195	805	805
+select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b 
+from t2, t3 where t2.a=t3.a order by t3.a limit 5;
+pk	a	b	pk	a	b
+199	801	801	199	801	801
+198	802	802	198	802	802
+197	803	803	197	803	803
+196	804	804	196	804	804
+195	805	805	195	805	805
+# 
+# Case #2: here, only "Using temporary" is removed. "Using filesort" remains.
+# 
+explain
+select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b 
+from t2, t3 where t2.a=t3.a order by t2.a limit 25;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	200	Using where; Using filesort
+1	SIMPLE	t3	ref	a	a	5	test.t2.a	1	
+explain
+select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b 
+from t2, t3 where t2.a=t3.a order by t3.a limit 25;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	200	Using where; Using filesort
+1	SIMPLE	t3	ref	a	a	5	test.t2.a	1	
+select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b 
+from t2, t3 where t2.a=t3.a order by t2.a limit 25;
+pk	a	b	pk	a	b
+199	801	801	199	801	801
+198	802	802	198	802	802
+197	803	803	197	803	803
+196	804	804	196	804	804
+195	805	805	195	805	805
+194	806	806	194	806	806
+193	807	807	193	807	807
+192	808	808	192	808	808
+191	809	809	191	809	809
+190	810	810	190	810	810
+189	811	811	189	811	811
+188	812	812	188	812	812
+187	813	813	187	813	813
+186	814	814	186	814	814
+185	815	815	185	815	815
+184	816	816	184	816	816
+183	817	817	183	817	817
+182	818	818	182	818	818
+181	819	819	181	819	819
+180	820	820	180	820	820
+179	821	821	179	821	821
+178	822	822	178	822	822
+177	823	823	177	823	823
+176	824	824	176	824	824
+175	825	825	175	825	825
+select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b 
+from t2, t3 where t2.a=t3.a order by t3.a limit 25;
+pk	a	b	pk	a	b
+199	801	801	199	801	801
+198	802	802	198	802	802
+197	803	803	197	803	803
+196	804	804	196	804	804
+195	805	805	195	805	805
+194	806	806	194	806	806
+193	807	807	193	807	807
+192	808	808	192	808	808
+191	809	809	191	809	809
+190	810	810	190	810	810
+189	811	811	189	811	811
+188	812	812	188	812	812
+187	813	813	187	813	813
+186	814	814	186	814	814
+185	815	815	185	815	815
+184	816	816	184	816	816
+183	817	817	183	817	817
+182	818	818	182	818	818
+181	819	819	181	819	819
+180	820	820	180	820	820
+179	821	821	179	821	821
+178	822	822	178	822	822
+177	823	823	177	823	823
+176	824	824	176	824	824
+175	825	825	175	825	825
+#
+# Case #3: single table access (the code that decides whether we need
+#          "Using temporary" is not invoked)
+#
+explain select * from t3 where b=a order by a limit 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	index	NULL	a	5	NULL	10	Using where
+# This must not use filesort. The query plan should be like the query above:
+explain select * from t3 where b=a order by b limit 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	index	NULL	a	5	NULL	10	Using where
+drop table t0,t1,t2,t3;
+set @@optimizer_switch=@tmp_8989;
+set optimizer_switch='orderby_uses_equalities=on';
diff --git a/mysql-test/r/parser.result b/mysql-test/r/parser.result
index a35109dbec3..499f37e6dfb 100644
--- a/mysql-test/r/parser.result
+++ b/mysql-test/r/parser.result
@@ -643,6 +643,26 @@ CREATE TRIGGER trigger1 BEFORE INSERT ON t1 FOR EACH ROW
 SET default_storage_engine = NEW.INNODB;
 ERROR 42S22: Unknown column 'INNODB' in 'NEW'
 DROP TABLE t1;
+select 0==0;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '=0' at line 1
+select 1=!0,  1 = ! 0;
+1=!0	1 = ! 0
+1	1
+select !!0,     ! ! 0;
+!!0	! ! 0
+0	0
+select 2>!0,  2 > ! 0;
+2>!0	2 > ! 0
+1	1
+select 0<=!0, 0 <= !0;
+0<=!0	0 <= !0
+1	1
+select 1<<!0, 1 << !0;
+1<<!0	1 << !0
+2	2
+select 0<!0,  0 < ! 0;
+0<!0	0 < ! 0
+1	1
 #
 # MDEV-7792 - SQL Parsing Error - UNION AND ORDER BY WITH JOIN
 #
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index a0e44f91d44..268bd184c58 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -2288,5 +2288,16 @@ Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9F\x98\x8E' for column 'a' at row 1
 DROP TABLE t1;
 #
+# MDEV-10185: Assertion `tree1->keys[key_no] && tree2->keys[key_no]' failed in 
+#             sel_trees_can_be_ored(RANGE_OPT_PARAM*, SEL_TREE*, SEL_TREE*, key_map*)
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+SELECT * FROM t1 WHERE pk != 1000 OR pk IN ( 1000, 0, 1, 100, 2 ) OR pk >= 5;
+pk
+1
+2
+DROP TABLE t1;
+#
 # End of 10.1 tests
 #
diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result
index c16d3b4adae..5c6930f4323 100644
--- a/mysql-test/r/range_mrr_icp.result
+++ b/mysql-test/r/range_mrr_icp.result
@@ -2290,6 +2290,17 @@ Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9F\x98\x8E' for column 'a' at row 1
 DROP TABLE t1;
 #
+# MDEV-10185: Assertion `tree1->keys[key_no] && tree2->keys[key_no]' failed in 
+#             sel_trees_can_be_ored(RANGE_OPT_PARAM*, SEL_TREE*, SEL_TREE*, key_map*)
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+SELECT * FROM t1 WHERE pk != 1000 OR pk IN ( 1000, 0, 1, 100, 2 ) OR pk >= 5;
+pk
+1
+2
+DROP TABLE t1;
+#
 # End of 10.1 tests
 #
 set optimizer_switch=@mrr_icp_extra_tmp;
diff --git a/mysql-test/r/select_debug.result b/mysql-test/r/select_debug.result
index a056affc2cd..55882ad337a 100644
--- a/mysql-test/r/select_debug.result
+++ b/mysql-test/r/select_debug.result
@@ -6,7 +6,7 @@ insert into t1 values (2,2), (1,1);
 create table t2 (a int);
 insert into t2 values (2), (3);
 set session join_cache_level=3;
-set @@debug_dbug= 'd:t:O,/tmp/trace.out';
+set @@debug_dbug= 'd,opt';
 explain select t1.b from t1,t2 where t1.b=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
diff --git a/mysql-test/r/set_password_plugin-9835.result b/mysql-test/r/set_password_plugin-9835.result
new file mode 100644
index 00000000000..3cc723957d8
--- /dev/null
+++ b/mysql-test/r/set_password_plugin-9835.result
@@ -0,0 +1,160 @@
+set global secure_auth=0;
+create user natauth@localhost identified via 'mysql_native_password' using '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
+create user newpass@localhost identified by password '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
+create user newpassnat@localhost identified via 'mysql_native_password';
+set password for newpassnat@localhost = '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
+create user oldauth@localhost identified with 'mysql_old_password' using '378b243e220ca493';
+create user oldpass@localhost identified by password '378b243e220ca493';
+create user oldpassold@localhost identified with 'mysql_old_password';
+set password for oldpassold@localhost = '378b243e220ca493';
+select user, host, password, plugin, authentication_string from mysql.user where user != 'root';
+user	host	password	plugin	authentication_string
+natauth	localhost	*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29		
+newpass	localhost	*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29		
+newpassnat	localhost	*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29		
+oldauth	localhost	378b243e220ca493		
+oldpass	localhost	378b243e220ca493		
+oldpassold	localhost	378b243e220ca493		
+connect con,localhost,natauth,test,;
+select current_user();
+current_user()
+natauth@localhost
+disconnect con;
+connect con,localhost,newpass,test,;
+select current_user();
+current_user()
+newpass@localhost
+disconnect con;
+connect con,localhost,newpassnat,test,;
+select current_user();
+current_user()
+newpassnat@localhost
+disconnect con;
+connect con,localhost,oldauth,test,;
+select current_user();
+current_user()
+oldauth@localhost
+disconnect con;
+connect con,localhost,oldpass,test,;
+select current_user();
+current_user()
+oldpass@localhost
+disconnect con;
+connect con,localhost,oldpassold,test,;
+select current_user();
+current_user()
+oldpassold@localhost
+disconnect con;
+connection default;
+flush privileges;
+connect con,localhost,natauth,test,;
+select current_user();
+current_user()
+natauth@localhost
+disconnect con;
+connect con,localhost,newpass,test,;
+select current_user();
+current_user()
+newpass@localhost
+disconnect con;
+connect con,localhost,newpassnat,test,;
+select current_user();
+current_user()
+newpassnat@localhost
+disconnect con;
+connect con,localhost,oldauth,test,;
+select current_user();
+current_user()
+oldauth@localhost
+disconnect con;
+connect con,localhost,oldpass,test,;
+select current_user();
+current_user()
+oldpass@localhost
+disconnect con;
+connect con,localhost,oldpassold,test,;
+select current_user();
+current_user()
+oldpassold@localhost
+disconnect con;
+connection default;
+set password for natauth@localhost = PASSWORD('test2');
+set password for newpass@localhost = PASSWORD('test2');
+set password for newpassnat@localhost = PASSWORD('test2');
+set password for oldauth@localhost = PASSWORD('test2');
+set password for oldpass@localhost = PASSWORD('test2');
+set password for oldpassold@localhost = PASSWORD('test2');
+select user, host, password, plugin, authentication_string from mysql.user where user != 'root';
+user	host	password	plugin	authentication_string
+natauth	localhost	*7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E		
+newpass	localhost	*7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E		
+newpassnat	localhost	*7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E		
+oldauth	localhost	*7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E		
+oldpass	localhost	*7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E		
+oldpassold	localhost	*7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E		
+connect con,localhost,natauth,test2,;
+select current_user();
+current_user()
+natauth@localhost
+disconnect con;
+connect con,localhost,newpass,test2,;
+select current_user();
+current_user()
+newpass@localhost
+disconnect con;
+connect con,localhost,newpassnat,test2,;
+select current_user();
+current_user()
+newpassnat@localhost
+disconnect con;
+connect con,localhost,oldauth,test2,;
+select current_user();
+current_user()
+oldauth@localhost
+disconnect con;
+connect con,localhost,oldpass,test2,;
+select current_user();
+current_user()
+oldpass@localhost
+disconnect con;
+connect con,localhost,oldpassold,test2,;
+select current_user();
+current_user()
+oldpassold@localhost
+disconnect con;
+connection default;
+flush privileges;
+connect con,localhost,natauth,test2,;
+select current_user();
+current_user()
+natauth@localhost
+disconnect con;
+connect con,localhost,newpass,test2,;
+select current_user();
+current_user()
+newpass@localhost
+disconnect con;
+connect con,localhost,newpassnat,test2,;
+select current_user();
+current_user()
+newpassnat@localhost
+disconnect con;
+connect con,localhost,oldauth,test2,;
+select current_user();
+current_user()
+oldauth@localhost
+disconnect con;
+connect con,localhost,oldpass,test2,;
+select current_user();
+current_user()
+oldpass@localhost
+disconnect con;
+connect con,localhost,oldpassold,test2,;
+select current_user();
+current_user()
+oldpassold@localhost
+disconnect con;
+connection default;
+drop user natauth@localhost, newpass@localhost, newpassnat@localhost;
+drop user oldauth@localhost, oldpass@localhost, oldpassold@localhost;
+set global secure_auth=default;
diff --git a/mysql-test/r/set_statement.result b/mysql-test/r/set_statement.result
index ad6f3b7bf2e..adb50afde99 100644
--- a/mysql-test/r/set_statement.result
+++ b/mysql-test/r/set_statement.result
@@ -1056,7 +1056,7 @@ set statement slow_query_log=default for select @@slow_query_log;
 truncate table mysql.slow_log;
 set slow_query_log= 1;
 set global log_output='TABLE';
-select sql_text from mysql.slow_log;
+select sql_text from mysql.slow_log where sql_text not like 'set @@long_query_time%';
 sql_text
 set @@long_query_time=0.01;
 #should be written
@@ -1064,7 +1064,7 @@ select sleep(0.1);
 sleep(0.1)
 0
 set @@long_query_time=@save_long_query_time;
-select sql_text from mysql.slow_log;
+select sql_text from mysql.slow_log where sql_text not like 'set @@long_query_time%';
 sql_text
 select sleep(0.1)
 #---
@@ -1072,7 +1072,7 @@ select sleep(0.1)
 set statement long_query_time=0.01 for select sleep(0.1);
 sleep(0.1)
 0
-select sql_text from mysql.slow_log;
+select sql_text from mysql.slow_log where sql_text not like 'set @@long_query_time%';
 sql_text
 select sleep(0.1)
 set statement long_query_time=0.01 for select sleep(0.1)
@@ -1083,7 +1083,7 @@ set statement slow_query_log=0 for select sleep(0.1);
 sleep(0.1)
 0
 set @@long_query_time=@save_long_query_time;
-select sql_text from mysql.slow_log;
+select sql_text from mysql.slow_log where sql_text not like 'set @@long_query_time%';
 sql_text
 select sleep(0.1)
 set statement long_query_time=0.01 for select sleep(0.1)
@@ -1092,7 +1092,7 @@ set statement long_query_time=0.01 for select sleep(0.1)
 set statement long_query_time=0.01,log_slow_filter='full_scan' for select sleep(0.1);
 sleep(0.1)
 0
-select sql_text from mysql.slow_log;
+select sql_text from mysql.slow_log where sql_text not like 'set @@long_query_time%';
 sql_text
 select sleep(0.1)
 set statement long_query_time=0.01 for select sleep(0.1)
@@ -1101,7 +1101,7 @@ set statement long_query_time=0.01 for select sleep(0.1)
 set statement long_query_time=0.01,log_slow_rate_limit=9999 for select sleep(0.1);
 sleep(0.1)
 0
-select sql_text from mysql.slow_log;
+select sql_text from mysql.slow_log where sql_text not like 'set @@long_query_time%';
 sql_text
 select sleep(0.1)
 set statement long_query_time=0.01 for select sleep(0.1)
@@ -1110,7 +1110,7 @@ set statement long_query_time=0.01 for select sleep(0.1)
 set statement long_query_time=0.01,min_examined_row_limit=50 for select sleep(0.1);
 sleep(0.1)
 0
-select sql_text from mysql.slow_log;
+select sql_text from mysql.slow_log where sql_text not like 'set @@long_query_time%';
 sql_text
 select sleep(0.1)
 set statement long_query_time=0.01 for select sleep(0.1)
diff --git a/mysql-test/r/sp-threads.result b/mysql-test/r/sp-threads.result
index eb1c0beff65..886cbd812f2 100644
--- a/mysql-test/r/sp-threads.result
+++ b/mysql-test/r/sp-threads.result
@@ -43,12 +43,9 @@ lock tables t2 write;
 connection con1root;
 call bug9486();
 connection con2root;
-show processlist;
-Id	User	Host	db	Command	Time	State	Info	Progress
-#	root	localhost	test	Sleep	#		NULL	0.000
-#	root	localhost	test	Query	#	Waiting for table metadata lock	update t1, t2 set val= 1 where id1=id2	0.000
-#	root	localhost	test	Query	#	init	show processlist	0.000
-#	root	localhost	test	Sleep	#		NULL	0.000
+SELECT state,info FROM information_schema.processlist WHERE id=con1root_id;
+state	info
+Waiting for table metadata lock	update t1, t2 set val= 1 where id1=id2
 unlock tables;
 connection con1root;
 drop procedure bug9486;
diff --git a/mysql-test/r/ssl.result b/mysql-test/r/ssl.result
index c36b5c666cb..7cd18fbf539 100644
--- a/mysql-test/r/ssl.result
+++ b/mysql-test/r/ssl.result
@@ -2175,3 +2175,8 @@ still connected?
 still connected?
 connection default;
 disconnect ssl_con;
+create user mysqltest_1@localhost;
+grant usage on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA";
+Variable_name	Value
+Ssl_cipher	EDH-RSA-DES-CBC3-SHA
+drop user mysqltest_1@localhost;
diff --git a/mysql-test/r/ssl_ca.result b/mysql-test/r/ssl_ca.result
new file mode 100644
index 00000000000..ffc5671f85f
--- /dev/null
+++ b/mysql-test/r/ssl_ca.result
@@ -0,0 +1,24 @@
+#
+# Bug#21920657: SSL-CA FAILS SILENTLY IF THE PATH CANNOT BE FOUND
+#
+# try to connect with wrong '--ssl-ca' path : should fail
+ERROR 2026 (HY000): SSL connection error: SSL_CTX_set_default_verify_paths failed
+# try to connect with correct '--ssl-ca' path : should connect
+Variable_name	Value
+Ssl_cipher	DHE-RSA-AES256-SHA
+#
+# Bug#21920678: SSL-CA DOES NOT ACCEPT ~USER TILDE HOME DIRECTORY
+#               PATH SUBSTITUTION
+#
+# try to connect with '--ssl-ca' option using tilde home directoy
+# path substitution : should connect
+Variable_name	Value
+Ssl_cipher	DHE-RSA-AES256-SHA
+# try to connect with '--ssl-key' option using tilde home directoy
+# path substitution : should connect
+Variable_name	Value
+Ssl_cipher	DHE-RSA-AES256-SHA
+# try to connect with '--ssl-cert' option using tilde home directoy
+# path substitution : should connect
+Variable_name	Value
+Ssl_cipher	DHE-RSA-AES256-SHA
diff --git a/mysql-test/r/ssl_timeout-9836.result b/mysql-test/r/ssl_timeout-9836.result
new file mode 100644
index 00000000000..bc2e19e1475
--- /dev/null
+++ b/mysql-test/r/ssl_timeout-9836.result
@@ -0,0 +1,7 @@
+SET @@net_read_timeout=1;
+SELECT 1;
+1
+1
+SELECT 1;
+1
+1
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 149db6c98fd..f6b3fc7cf25 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -7151,3 +7151,15 @@ sq
 NULL
 drop view v2;
 drop table t1,t2;
+#
+# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
+# with UNION in ALL subquery
+#
+SET NAMES utf8;
+CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo');
+SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' );
+f
+foo
+drop table t1;
+SET NAMES default;
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index ac49a41ee23..961b23f1028 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -7151,6 +7151,18 @@ sq
 NULL
 drop view v2;
 drop table t1,t2;
+#
+# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
+# with UNION in ALL subquery
+#
+SET NAMES utf8;
+CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo');
+SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' );
+f
+foo
+drop table t1;
+SET NAMES default;
 set optimizer_switch=default;
 select @@optimizer_switch like '%exists_to_in=off%';
 @@optimizer_switch like '%exists_to_in=off%'
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 4f537ddb875..a7170a9dc45 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -7144,6 +7144,18 @@ sq
 NULL
 drop view v2;
 drop table t1,t2;
+#
+# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
+# with UNION in ALL subquery
+#
+SET NAMES utf8;
+CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo');
+SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' );
+f
+foo
+drop table t1;
+SET NAMES default;
 set optimizer_switch=default;
 select @@optimizer_switch like '%materialization=on%';
 @@optimizer_switch like '%materialization=on%'
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index b5a5e447921..1e9749c2eb5 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -7142,4 +7142,16 @@ sq
 NULL
 drop view v2;
 drop table t1,t2;
+#
+# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
+# with UNION in ALL subquery
+#
+SET NAMES utf8;
+CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo');
+SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' );
+f
+foo
+drop table t1;
+SET NAMES default;
 set @optimizer_switch_for_subselect_test=null;
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 78308c4a70f..23a093b2669 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -7157,6 +7157,18 @@ sq
 NULL
 drop view v2;
 drop table t1,t2;
+#
+# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
+# with UNION in ALL subquery
+#
+SET NAMES utf8;
+CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo');
+SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' );
+f
+foo
+drop table t1;
+SET NAMES default;
 set optimizer_switch=default;
 select @@optimizer_switch like '%subquery_cache=on%';
 @@optimizer_switch like '%subquery_cache=on%'
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 62dc507dee5..dd1810e47f1 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -7142,5 +7142,17 @@ sq
 NULL
 drop view v2;
 drop table t1,t2;
+#
+# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
+# with UNION in ALL subquery
+#
+SET NAMES utf8;
+CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo');
+SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' );
+f
+foo
+drop table t1;
+SET NAMES default;
 set @optimizer_switch_for_subselect_test=null;
 set @join_cache_level_for_subselect_test=NULL;
diff --git a/mysql-test/r/trigger_null-8605.result b/mysql-test/r/trigger_null-8605.result
index e024bc6b7d2..b187fc19554 100644
--- a/mysql-test/r/trigger_null-8605.result
+++ b/mysql-test/r/trigger_null-8605.result
@@ -318,9 +318,8 @@ id
 delete from t1;
 create trigger t1_bi before insert on t1 for each row begin end;
 insert t1 values (0);
-select * from t1;
-id
-0
+insert t1 (id) values (0);
+ERROR 23000: Duplicate entry '0' for key 'PRIMARY'
 drop table t1;
 create table t1 (a int not null, b int);
 create trigger trgi before update on t1 for each row do 1;
diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result
index 54f2aa91f2c..a1eeda063e4 100644
--- a/mysql-test/r/type_date.result
+++ b/mysql-test/r/type_date.result
@@ -439,6 +439,42 @@ select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1;
 1
 drop table t1;
 #
+# MDEV-9521 Least function returns 0000-00-00 for null date columns instead of null
+# MDEV-9972 Least function retuns date in date time format
+#
+CREATE TABLE t1 (
+id BIGINT NOT NULL,
+date_debut DATE NOT NULL,
+date_fin DATE DEFAULT NULL);
+CREATE TABLE t2(
+id BIGINT NOT NULL,
+date_debut DATE NOT NULL,
+date_fin DATE DEFAULT NULL);
+INSERT INTO t1 VALUES (1,'2016-01-01','2016-01-31');
+INSERT INTO t1 VALUES (2,'2016-02-01',null);
+INSERT INTO t1 VALUES (3,'2016-03-01','2016-03-31');
+INSERT INTO t1 VALUES (4,'2016-04-01',null);
+INSERT INTO t2 VALUES (1,'2016-01-01','2016-01-31');
+INSERT INTO t2 VALUES (2,'2016-02-01','2016-01-28');
+INSERT INTO t2 VALUES (3,'2016-03-01',null);
+INSERT INTO t2 VALUES (4,'2016-04-01',null);
+SELECT t1.id,
+GREATEST(t2.date_debut, t1.date_debut) AS date_debut,
+LEAST(IFNULL(t2.date_fin, IFNULL(t1.date_fin, NULL)),
+IFNULL(t1.date_fin, IFNULL(t2.date_fin, NULL))) AS date_fin
+FROM t1 LEFT JOIN t2 ON (t1.id=t2.id);
+id	date_debut	date_fin
+1	2016-01-01	2016-01-31
+2	2016-02-01	2016-01-28
+3	2016-03-01	2016-03-31
+4	2016-04-01	NULL
+DROP TABLE t1,t2;
+SELECT
+LEAST(COALESCE(DATE(NULL), DATE(NULL)), COALESCE(DATE(NULL), DATE(NULL))) AS d0,
+LEAST(IFNULL(DATE(NULL), DATE(NULL)), IFNULL(DATE(NULL), DATE(NULL))) AS d1;
+d0	d1
+NULL	NULL
+#
 # MDEV-9511 Valgrind warnings 'Invalid read' in Field_newdate::cmp and Field_newdate::val_str
 #
 CREATE TABLE t1 (f1 DATE, f2 VARCHAR(1));
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index bf3b6a6b7da..41696aa2d7d 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -834,6 +834,14 @@ a	b	a	b
 DEALLOCATE PREPARE stmt1;
 DROP TABLE t1,t2;
 #
+# MDEV-9374 having '2015-01-01 01:00:00.000001' > coalesce(NULL) returns true
+#
+CREATE TABLE t1 (c1 DATETIME(0));
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 HAVING '2015-01-01 01:00:00.000001' > COALESCE(c1);
+c1
+DROP TABLE t1;
+#
 # End of 5.5 tests
 #
 #
diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result
index 4b09b269d2f..55dde117e80 100644
--- a/mysql-test/r/type_newdecimal.result
+++ b/mysql-test/r/type_newdecimal.result
@@ -2149,6 +2149,96 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	a	a	6	const	1	Using where; Using index; Using filesort
 DROP TABLE t1;
 #
+# MDEV-8502 DECIMAL accepts out of range DEFAULT values
+#
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 10000);
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 10000.0);
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 10000e0);
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '10000.0');
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '10000.1');
+ERROR 42000: Invalid default value for 'a'
+#
+# MDEV-10277 Redundant NOTE when inserting '0.00001 ' into a DECIMAL(2,1) column
+#
+CREATE TABLE t1 (a DECIMAL(2,1));
+INSERT INTO t1 VALUES ('0.00001 ');
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+INSERT INTO t1 VALUES ('1e-10000 ');
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+INSERT INTO t1 VALUES ('0.1 ');
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+INSERT INTO t1 VALUES ('0.111 ');
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+SELECT * FROM t1;
+a
+0.0
+0.0
+0.1
+0.1
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '1e-10000');
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` decimal(2,1) DEFAULT '0.0'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '0.1 ');
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` decimal(2,1) DEFAULT '0.1'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '0.10001 ');
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` decimal(2,1) DEFAULT '0.1'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT '0.10001');
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` decimal(2,1) DEFAULT '0.1'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 0.10001);
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` decimal(2,1) DEFAULT '0.1'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(2,1) DEFAULT 0.10001e0);
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` decimal(2,1) DEFAULT '0.1'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+#
 # End of 10.1 tests
 #
 #
diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result
index a579f6930a0..69c9f68811d 100644
--- a/mysql-test/r/type_timestamp.result
+++ b/mysql-test/r/type_timestamp.result
@@ -644,6 +644,15 @@ SELECT MAX(dt) = '2011-01-06 12:34:30' FROM t1;
 MAX(dt) = '2011-01-06 12:34:30'
 1
 DROP TABLE t1;
+#
+# MDEV-9413 "datetime >= coalesce(c1(NULL))" doesn't return expected NULL
+#
+CREATE TABLE t1(c1 TIMESTAMP(6) NULL DEFAULT NULL);
+INSERT INTO t1 VALUES(NULL);
+SELECT c1, '2016-06-13 20:00:00.000003' >= COALESCE( c1 ) FROM t1;
+c1	'2016-06-13 20:00:00.000003' >= COALESCE( c1 )
+NULL	NULL
+DROP TABLE t1;
 End of 5.5 tests
 #
 # MDEV-7254: Assigned expression is evaluated twice when updating column TIMESTAMP NOT NULL
diff --git a/mysql-test/r/type_year.result b/mysql-test/r/type_year.result
index 4d28a7b8d25..5d4aa24c9c6 100644
--- a/mysql-test/r/type_year.result
+++ b/mysql-test/r/type_year.result
@@ -387,6 +387,7 @@ a
 00
 select a from t1 where a=(select 2000 from dual where 1);
 a
+00
 select a from t1 where a=y2k();
 a
 00
diff --git a/mysql-test/r/userstat.result b/mysql-test/r/userstat.result
index 351f69f3031..a56ff5771f6 100644
--- a/mysql-test/r/userstat.result
+++ b/mysql-test/r/userstat.result
@@ -138,16 +138,12 @@ disconnect ssl_con;
 set @@global.userstat=0;
 select * from information_schema.index_statistics;
 TABLE_SCHEMA	TABLE_NAME	INDEX_NAME	ROWS_READ
-test	t1	PRIMARY	2
 select * from information_schema.table_statistics;
 TABLE_SCHEMA	TABLE_NAME	ROWS_READ	ROWS_CHANGED	ROWS_CHANGED_X_INDEXES
-test	t1	6	13	13
 show table_statistics;
 Table_schema	Table_name	Rows_read	Rows_changed	Rows_changed_x_#indexes
-test	t1	6	13	13
 show index_statistics;
 Table_schema	Table_name	Index_name	Rows_read
-test	t1	PRIMARY	2
 select TOTAL_CONNECTIONS, TOTAL_SSL_CONNECTIONS, CONCURRENT_CONNECTIONS, ROWS_READ, ROWS_SENT, ROWS_DELETED, ROWS_INSERTED, ROWS_UPDATED, SELECT_COMMANDS, UPDATE_COMMANDS, OTHER_COMMANDS, COMMIT_TRANSACTIONS, ROLLBACK_TRANSACTIONS, DENIED_CONNECTIONS, LOST_CONNECTIONS, ACCESS_DENIED, EMPTY_QUERIES from information_schema.client_statistics;;
 TOTAL_CONNECTIONS	2
 TOTAL_SSL_CONNECTIONS	1
diff --git a/mysql-test/r/wait_timeout_not_windows.result b/mysql-test/r/wait_timeout_not_windows.result
new file mode 100644
index 00000000000..867787a8ed3
--- /dev/null
+++ b/mysql-test/r/wait_timeout_not_windows.result
@@ -0,0 +1,4 @@
+set global log_warnings=2;
+set @@wait_timeout=1;
+FOUND /Aborted.*Got timeout reading communication packets/ in mysqld.1.err
+set global log_warnings=@@log_warnings;