Tests result updated for switch optimizer_use_condition_selectivity=310.4-mdev-15253

15 files changed, 415 insertions, 137 deletions

diff --git a/mysql-test/main/cache_temporal_4265.result b/mysql-test/main/cache_temporal_4265.result
index 7f215de43fb..477c8d3aaa4 100644
--- a/mysql-test/main/cache_temporal_4265.result
+++ b/mysql-test/main/cache_temporal_4265.result
@@ -7,6 +7,8 @@ a
 2002-03-04
 Warnings:
 Note	1003	2000-01-01
+Note	1003	2000-01-01
+Note	1003	2000-01-01
 set debug_dbug='';
 drop table t1;
 create table t1 (id int not null, ut timestamp(6) not null);
diff --git a/mysql-test/main/ctype_uca.result b/mysql-test/main/ctype_uca.result
index 725e744c44e..20c4ab58969 100644
--- a/mysql-test/main/ctype_uca.result
+++ b/mysql-test/main/ctype_uca.result
@@ -6746,6 +6746,8 @@ Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='admin𝌆';
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='admin𝌆';
 ch
 Warnings:
@@ -6759,8 +6761,12 @@ INSERT INTO t1 (ch) VALUES (_utf8 0x61D080);
 INSERT INTO t1 (ch) VALUES (_utf8 0x61D680);
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆' ORDER BY ch;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆b' ORDER BY ch;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a𝌆' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -6800,6 +6806,8 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch;
 ch
 a
@@ -6819,12 +6827,15 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	ch	ch	183	NULL	#	Using where; Using index
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch;
 ch
 a
@@ -6846,12 +6857,14 @@ aЀ
 aր
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	ch	ch	183	NULL	#	Using where; Using index
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch;
 ch
 a
@@ -6873,44 +6886,57 @@ aЀ
 aր
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	ch	ch	183	NULL	#	Using where; Using index
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch;
 ch
 z
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	ch	ch	183	NULL	#	Using where; Using index
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch;
 ch
 z
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 ALTER TABLE t1 DROP KEY ch;
 # 0xD18F would be a good 2-byte character, 0xD1 is an incomplete sequence
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,'''');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xD1' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,'b''');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xD1b' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch<''a', 0xD1,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
@@ -6932,38 +6958,54 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xD1' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch>''a', 0xD1,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xD1' for column 'ch' at row 1
 # 0xEA9A96 would be a good 3-byte character, 0xEA9A is an incomplete sequence
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xEA\x9A' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,'b'' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xEA\x9Ab' for column 'ch' at row 1
 # 0x8F is a bad byte sequence (an mb2tail without mb2head)
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8F' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,'b'' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8Fb' for column 'ch' at row 1
 # 0x8F8F is a bad byte sequence (an mb2tail without mb2head, two times)
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8F\x8F' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,'b'' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8F\x8Fb' for column 'ch' at row 1
 DROP TABLE t1;
 #
 # End of ctype_utf8_ilseq.inc
@@ -6990,6 +7032,8 @@ Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='admin𝌆';
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='admin𝌆';
 ch
 Warnings:
@@ -7003,8 +7047,12 @@ INSERT INTO t1 (ch) VALUES (_utf8 0x61D080);
 INSERT INTO t1 (ch) VALUES (_utf8 0x61D680);
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆' ORDER BY ch;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆b' ORDER BY ch;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a𝌆' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -7044,6 +7092,8 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch;
 ch
 a
@@ -7063,10 +7113,14 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	ch	NULL	NULL	NULL	#	Using where; Using filesort
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch;
 ch
 a
@@ -7086,10 +7140,14 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	ch	NULL	NULL	NULL	#	Using where; Using filesort
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch;
 ch
 a
@@ -7109,36 +7167,54 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	ch	NULL	NULL	NULL	#	Using where; Using filesort
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	ch	NULL	NULL	NULL	#	Using where; Using filesort
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 ALTER TABLE t1 DROP KEY ch;
 # 0xD18F would be a good 2-byte character, 0xD1 is an incomplete sequence
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,'''');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xD1' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,'b''');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xD1b' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch<''a', 0xD1,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
@@ -7160,38 +7236,54 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xD1' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch>''a', 0xD1,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xD1' for column 'ch' at row 1
 # 0xEA9A96 would be a good 3-byte character, 0xEA9A is an incomplete sequence
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xEA\x9A' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,'b'' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xEA\x9Ab' for column 'ch' at row 1
 # 0x8F is a bad byte sequence (an mb2tail without mb2head)
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8F' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,'b'' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8Fb' for column 'ch' at row 1
 # 0x8F8F is a bad byte sequence (an mb2tail without mb2head, two times)
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8F\x8F' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,'b'' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8F\x8Fb' for column 'ch' at row 1
 DROP TABLE t1;
 #
 # End of ctype_utf8_ilseq.inc
@@ -8572,9 +8664,9 @@ SELECT * FROM t1 WHERE c1='ä' AND c1 LIKE 'ae';
 c1
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1='ä' AND c1 LIKE 'ae';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where `test`.`t1`.`c1` = 'ä' and `test`.`t1`.`c1` like 'ae'
+Note	1003	select 'ä' AS `c1` from `test`.`t1` where 0
 SELECT * FROM t1 WHERE CONCAT(c1)='ä';
 c1
 ä
diff --git a/mysql-test/main/ctype_uca_innodb.result b/mysql-test/main/ctype_uca_innodb.result
index c04a99c8cd3..2442fadb0ee 100644
--- a/mysql-test/main/ctype_uca_innodb.result
+++ b/mysql-test/main/ctype_uca_innodb.result
@@ -27,6 +27,8 @@ Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='admin𝌆';
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='admin𝌆';
 ch
 Warnings:
@@ -40,8 +42,12 @@ INSERT INTO t1 (ch) VALUES (_utf8 0x61D080);
 INSERT INTO t1 (ch) VALUES (_utf8 0x61D680);
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆' ORDER BY ch;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆b' ORDER BY ch;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a𝌆' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -81,6 +87,8 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch;
 ch
 a
@@ -100,12 +108,15 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	ch	ch	183	NULL	#	Using where; Using index
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch;
 ch
 a
@@ -127,12 +138,14 @@ aЀ
 aր
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	ch	ch	183	NULL	#	Using where; Using index
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch;
 ch
 a
@@ -154,44 +167,57 @@ aЀ
 aր
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	ch	ch	183	NULL	#	Using where; Using index
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch;
 ch
 z
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	ch	ch	183	NULL	#	Using where; Using index
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch;
 ch
 z
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 ALTER TABLE t1 DROP KEY ch;
 # 0xD18F would be a good 2-byte character, 0xD1 is an incomplete sequence
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,'''');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xD1' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,'b''');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xD1b' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch<''a', 0xD1,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
@@ -213,38 +239,54 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xD1' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch>''a', 0xD1,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xD1' for column 'ch' at row 1
 # 0xEA9A96 would be a good 3-byte character, 0xEA9A is an incomplete sequence
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xEA\x9A' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,'b'' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xEA\x9Ab' for column 'ch' at row 1
 # 0x8F is a bad byte sequence (an mb2tail without mb2head)
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8F' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,'b'' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8Fb' for column 'ch' at row 1
 # 0x8F8F is a bad byte sequence (an mb2tail without mb2head, two times)
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8F\x8F' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,'b'' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8F\x8Fb' for column 'ch' at row 1
 DROP TABLE t1;
 #
 # End of ctype_utf8_ilseq.inc
diff --git a/mysql-test/main/ctype_utf8.result b/mysql-test/main/ctype_utf8.result
index 10d0efeff3c..4c0482882dd 100644
--- a/mysql-test/main/ctype_utf8.result
+++ b/mysql-test/main/ctype_utf8.result
@@ -5459,6 +5459,8 @@ Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='admin𝌆';
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='admin𝌆';
 ch
 Warnings:
@@ -5472,8 +5474,12 @@ INSERT INTO t1 (ch) VALUES (_utf8 0x61D080);
 INSERT INTO t1 (ch) VALUES (_utf8 0x61D680);
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆' ORDER BY ch;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆b' ORDER BY ch;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a𝌆' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -5513,6 +5519,8 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch;
 ch
 a
@@ -5532,12 +5540,15 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	ch	ch	183	NULL	#	Using where; Using index
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch;
 ch
 a
@@ -5559,12 +5570,14 @@ aЀ
 aր
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	ch	ch	183	NULL	#	Using where; Using index
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch;
 ch
 a
@@ -5586,44 +5599,57 @@ aЀ
 aր
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	ch	ch	183	NULL	#	Using where; Using index
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch;
 ch
 z
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	ch	ch	183	NULL	#	Using where; Using index
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch;
 ch
 z
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 ALTER TABLE t1 DROP KEY ch;
 # 0xD18F would be a good 2-byte character, 0xD1 is an incomplete sequence
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,'''');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xD1' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,'b''');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xD1b' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch<''a', 0xD1,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
@@ -5645,38 +5671,54 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xD1' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch>''a', 0xD1,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xD1' for column 'ch' at row 1
 # 0xEA9A96 would be a good 3-byte character, 0xEA9A is an incomplete sequence
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xEA\x9A' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,'b'' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xEA\x9Ab' for column 'ch' at row 1
 # 0x8F is a bad byte sequence (an mb2tail without mb2head)
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8F' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,'b'' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8Fb' for column 'ch' at row 1
 # 0x8F8F is a bad byte sequence (an mb2tail without mb2head, two times)
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8F\x8F' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,'b'' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8F\x8Fb' for column 'ch' at row 1
 DROP TABLE t1;
 #
 # End of ctype_utf8_ilseq.inc
@@ -5703,6 +5745,8 @@ Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='admin𝌆';
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='admin𝌆';
 ch
 Warnings:
@@ -5716,8 +5760,12 @@ INSERT INTO t1 (ch) VALUES (_utf8 0x61D080);
 INSERT INTO t1 (ch) VALUES (_utf8 0x61D680);
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆' ORDER BY ch;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆b' ORDER BY ch;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a𝌆' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -5757,6 +5805,8 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch;
 ch
 a
@@ -5776,12 +5826,15 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	ch	ch	183	NULL	#	Using where; Using index
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch;
 ch
 a
@@ -5803,12 +5856,14 @@ aЀ
 aր
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	ch	ch	183	NULL	#	Using where; Using index
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch;
 ch
 a
@@ -5830,44 +5885,57 @@ aЀ
 aր
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	ch	ch	183	NULL	#	Using where; Using index
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch;
 ch
 z
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	ch	ch	183	NULL	#	Using where; Using index
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch;
 ch
 z
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 ALTER TABLE t1 DROP KEY ch;
 # 0xD18F would be a good 2-byte character, 0xD1 is an incomplete sequence
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,'''');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xD1' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,'b''');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xD1b' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch<''a', 0xD1,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
@@ -5889,38 +5957,54 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xD1' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch>''a', 0xD1,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xD1' for column 'ch' at row 1
 # 0xEA9A96 would be a good 3-byte character, 0xEA9A is an incomplete sequence
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xEA\x9A' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,'b'' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xEA\x9Ab' for column 'ch' at row 1
 # 0x8F is a bad byte sequence (an mb2tail without mb2head)
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8F' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,'b'' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8Fb' for column 'ch' at row 1
 # 0x8F8F is a bad byte sequence (an mb2tail without mb2head, two times)
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8F\x8F' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,'b'' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8F\x8Fb' for column 'ch' at row 1
 DROP TABLE t1;
 #
 # End of ctype_utf8_ilseq.inc
@@ -5947,6 +6031,8 @@ Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='admin𝌆';
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='admin𝌆';
 ch
 Warnings:
@@ -5960,8 +6046,12 @@ INSERT INTO t1 (ch) VALUES (_utf8 0x61D080);
 INSERT INTO t1 (ch) VALUES (_utf8 0x61D680);
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆' ORDER BY ch;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆b' ORDER BY ch;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a𝌆' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -6001,6 +6091,8 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch;
 ch
 a
@@ -6020,10 +6112,14 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	ch	NULL	NULL	NULL	#	Using where; Using filesort
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch;
 ch
 a
@@ -6043,10 +6139,14 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	ch	NULL	NULL	NULL	#	Using where; Using filesort
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch;
 ch
 a
@@ -6066,36 +6166,54 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	ch	NULL	NULL	NULL	#	Using where; Using filesort
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1
 EXPLAIN
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	ch	NULL	NULL	NULL	#	Using where; Using filesort
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1
 ALTER TABLE t1 DROP KEY ch;
 # 0xD18F would be a good 2-byte character, 0xD1 is an incomplete sequence
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,'''');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xD1' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,'b''');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xD1b' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch<''a', 0xD1,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
@@ -6117,38 +6235,54 @@ ab
 az
 aЀ
 aր
+Warnings:
+Warning	1366	Incorrect string value: '\xD1' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch>''a', 0xD1,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
 z
+Warnings:
+Warning	1366	Incorrect string value: '\xD1' for column 'ch' at row 1
 # 0xEA9A96 would be a good 3-byte character, 0xEA9A is an incomplete sequence
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xEA\x9A' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,'b'' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\xEA\x9Ab' for column 'ch' at row 1
 # 0x8F is a bad byte sequence (an mb2tail without mb2head)
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8F' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,'b'' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8Fb' for column 'ch' at row 1
 # 0x8F8F is a bad byte sequence (an mb2tail without mb2head, two times)
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,''' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8F\x8F' for column 'ch' at row 1
 SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,'b'' ORDER BY ch');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 ch
+Warnings:
+Warning	1366	Incorrect string value: '\x8F\x8Fb' for column 'ch' at row 1
 DROP TABLE t1;
 #
 # End of ctype_utf8_ilseq.inc
@@ -7088,9 +7222,9 @@ SELECT * FROM t1 WHERE c1='ä' AND c1 LIKE 'ae';
 c1
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1='ä' AND c1 LIKE 'ae';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where `test`.`t1`.`c1` = 'ä' and `test`.`t1`.`c1` like 'ae'
+Note	1003	select 'ä' AS `c1` from `test`.`t1` where 0
 SELECT * FROM t1 WHERE CONCAT(c1)='ä';
 c1
 ä
diff --git a/mysql-test/main/explain.result b/mysql-test/main/explain.result
index f593e0dfaba..45b413ab1a0 100644
--- a/mysql-test/main/explain.result
+++ b/mysql-test/main/explain.result
@@ -265,10 +265,10 @@ INSERT INTO t2 VALUES (NULL), (0);
 EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
-2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
 Note	1276	Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
-Note	1003	/* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where `test`.`t2`.`d` = NULL) AS `(SELECT 1 FROM t2 WHERE d = c)` from dual
+Note	1003	/* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where 0) AS `(SELECT 1 FROM t2 WHERE d = c)` from dual
 DROP TABLE t1, t2;
 #
 # Bug#30302: Tables that were optimized away are printed in the
diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result
index 3541cc6d9db..db69473917e 100644
--- a/mysql-test/main/group_min_max.result
+++ b/mysql-test/main/group_min_max.result
@@ -1716,7 +1716,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using index for group-by
 explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	128	100.00	Using where; Using index
+1	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	128	0.39	Using where; Using index
 Warnings:
 Note	1003	select distinct `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`b` = 'a' and `test`.`t1`.`c` = 'i121' and `test`.`t1`.`a2` >= 'b'
 explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
@@ -1733,7 +1733,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using index for group-by
 explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	100.00	Using where; Using index
+1	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	0.28	Using where; Using index
 Warnings:
 Note	1003	select distinct `test`.`t2`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where `test`.`t2`.`b` = 'a' and `test`.`t2`.`c` = 'i121' and `test`.`t2`.`a2` >= 'b'
 explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
@@ -2084,13 +2084,13 @@ Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,min(`test`.`t
 explain extended select a1,a2,b,min(c),max(c) from t1
 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	idx_t1_0,idx_t1_1,idx_t1_2	NULL	NULL	NULL	128	59.38	Using where; Using temporary; Using filesort
+1	SIMPLE	t1	ALL	idx_t1_0,idx_t1_1,idx_t1_2	NULL	NULL	NULL	128	39.58	Using where; Using temporary; Using filesort
 Warnings:
 Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,min(`test`.`t1`.`c`) AS `min(c)`,max(`test`.`t1`.`c`) AS `max(c)` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`d` > 'xy2' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
 explain extended select a1,a2,b,c from t1
 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	idx_t1_0,idx_t1_1,idx_t1_2	NULL	NULL	NULL	128	59.38	Using where; Using temporary; Using filesort
+1	SIMPLE	t1	ALL	idx_t1_0,idx_t1_1,idx_t1_2	NULL	NULL	NULL	128	39.58	Using where; Using temporary; Using filesort
 Warnings:
 Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`d` > 'xy2' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`,`test`.`t1`.`c`
 explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1;
diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result
index fe8c843a92e..ecb11b57a79 100644
--- a/mysql-test/main/join_cache.result
+++ b/mysql-test/main/join_cache.result
@@ -79,9 +79,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
+1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where
+1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where; Using join buffer (flat, BNL join)
 1	SIMPLE	CountryLanguage	ALL	NULL	NULL	NULL	NULL	984	Using where; Using join buffer (flat, BNL join)
-1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where; Using join buffer (flat, BNL join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -157,9 +157,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
-1	SIMPLE	CountryLanguage	ALL	NULL	NULL	NULL	NULL	984	Using where; Using join buffer (flat, BNL join)
-1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where
+1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	CountryLanguage	ALL	NULL	NULL	NULL	NULL	984	Using where; Using join buffer (incremental, BNL join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -236,8 +236,8 @@ CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
-1	SIMPLE	CountryLanguage	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	984	Using where; Using join buffer (flat, BNLH join)
 1	SIMPLE	City	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	CountryLanguage	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	984	Using where; Using join buffer (flat, BNLH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -314,8 +314,8 @@ CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
-1	SIMPLE	CountryLanguage	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	984	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	City	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	4079	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	City	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	CountryLanguage	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	984	Using where; Using join buffer (incremental, BNLH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -402,7 +402,7 @@ ON City.Country=Country.Code AND City.Population > 5000000
 WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
-1	SIMPLE	City	hash_range	City_Population	#hash#$hj:City_Population	3:4	world.Country.Code	25	Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join)
+1	SIMPLE	City	range	City_Population	City_Population	4	NULL	25	Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)
 SELECT Country.Name, Country.Population, City.Name, City.Population 
 FROM Country LEFT JOIN City
 ON City.Country=Country.Code AND City.Population > 5000000
@@ -497,9 +497,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
+1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	CountryLanguage	ALL	NULL	NULL	NULL	NULL	984	Using where; Using join buffer (flat, BNL join)
-1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where; Using join buffer (flat, BNL join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -575,9 +575,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
+1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	CountryLanguage	ALL	NULL	NULL	NULL	NULL	984	Using where; Using join buffer (flat, BNL join)
-1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where; Using join buffer (incremental, BNL join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -653,9 +653,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
-1	SIMPLE	CountryLanguage	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	984	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	City	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where
+1	SIMPLE	CountryLanguage	hash_ALL	NULL	#hash#$hj	3	world.City.Country	984	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	Country	hash_ALL	NULL	#hash#$hj	3	world.City.Country	239	Using where; Using join buffer (flat, BNLH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -731,9 +731,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
-1	SIMPLE	CountryLanguage	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	984	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	City	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	4079	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where
+1	SIMPLE	CountryLanguage	hash_ALL	NULL	#hash#$hj	3	world.City.Country	984	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	Country	hash_ALL	NULL	#hash#$hj	3	world.City.Country	239	Using where; Using join buffer (incremental, BNLH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -848,9 +848,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
-1	SIMPLE	Country	hash_ALL	PRIMARY	#hash#PRIMARY	3	world.CountryLanguage.Country	239	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	City	hash_ALL	Country	#hash#Country	3	world.CountryLanguage.Country	4079	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
+1	SIMPLE	CountryLanguage	hash_ALL	PRIMARY,Percentage	#hash#PRIMARY	3	world.City.Country	984	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	Country	hash_ALL	PRIMARY	#hash#PRIMARY	3	world.City.Country	239	Using where; Using join buffer (flat, BNLH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1048,9 +1048,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
-1	SIMPLE	Country	hash_ALL	PRIMARY	#hash#PRIMARY	3	world.CountryLanguage.Country	239	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	City	hash_ALL	Country	#hash#Country	3	world.CountryLanguage.Country	4079	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
+1	SIMPLE	CountryLanguage	hash_ALL	PRIMARY,Percentage	#hash#PRIMARY	3	world.City.Country	984	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	Country	hash_ALL	PRIMARY	#hash#PRIMARY	3	world.City.Country	239	Using where; Using join buffer (incremental, BNLH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1307,9 +1307,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
-1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	17	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1318,34 +1318,34 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 Name	Name	Language
-La Matanza	Argentina	Spanish
 Lomas de Zamora	Argentina	Spanish
+La Matanza	Argentina	Spanish
 Lauro de Freitas	Brazil	Portuguese
 Los Angeles	Chile	Spanish
 Las Palmas de Gran Canaria	Spain	Spanish
-L´Hospitalet de Llobregat	Spain	Spanish
 Lleida (Lérida)	Spain	Spanish
+L´Hospitalet de Llobregat	Spain	Spanish
 Liupanshui	China	Chinese
-Lianyungang	China	Chinese
 Liangcheng	China	Chinese
+Lianyungang	China	Chinese
 Lengshuijiang	China	Chinese
 Lázaro Cárdenas	Mexico	Spanish
 Lagos de Moreno	Mexico	Spanish
 Las Margaritas	Mexico	Spanish
 Lashio (Lasho)	Myanmar	Burmese
 Lalitapur	Nepal	Nepali
-Ludwigshafen am Rhein	Germany	German
 Leverkusen	Germany	German
+Ludwigshafen am Rhein	Germany	German
 Luchou	Taiwan	Min
 Lungtan	Taiwan	Min
 Lower Hutt	New Zealand	English
 Los Teques	Venezuela	Spanish
 Leninsk-Kuznetski	Russian Federation	Russian
-Los Angeles	United States	English
 Long Beach	United States	English
 Lexington-Fayette	United States	English
 Louisville	United States	English
 Little Rock	United States	English
+Los Angeles	United States	English
 EXPLAIN
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -1504,9 +1504,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
-1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	17	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
+1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1515,34 +1515,34 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 Name	Name	Language
-La Matanza	Argentina	Spanish
 Lomas de Zamora	Argentina	Spanish
+La Matanza	Argentina	Spanish
 Lauro de Freitas	Brazil	Portuguese
 Los Angeles	Chile	Spanish
 Las Palmas de Gran Canaria	Spain	Spanish
-L´Hospitalet de Llobregat	Spain	Spanish
 Lleida (Lérida)	Spain	Spanish
+L´Hospitalet de Llobregat	Spain	Spanish
 Liupanshui	China	Chinese
-Lianyungang	China	Chinese
 Liangcheng	China	Chinese
 Lengshuijiang	China	Chinese
-Lázaro Cárdenas	Mexico	Spanish
-Lagos de Moreno	Mexico	Spanish
+Lianyungang	China	Chinese
 Las Margaritas	Mexico	Spanish
+Lagos de Moreno	Mexico	Spanish
+Lázaro Cárdenas	Mexico	Spanish
 Lashio (Lasho)	Myanmar	Burmese
 Lalitapur	Nepal	Nepali
-Ludwigshafen am Rhein	Germany	German
 Leverkusen	Germany	German
+Ludwigshafen am Rhein	Germany	German
 Luchou	Taiwan	Min
 Lungtan	Taiwan	Min
 Lower Hutt	New Zealand	English
 Los Teques	Venezuela	Spanish
 Leninsk-Kuznetski	Russian Federation	Russian
-Los Angeles	United States	English
-Long Beach	United States	English
-Lexington-Fayette	United States	English
-Louisville	United States	English
 Little Rock	United States	English
+Louisville	United States	English
+Lexington-Fayette	United States	English
+Long Beach	United States	English
+Los Angeles	United States	English
 EXPLAIN
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -1701,9 +1701,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
-1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
-1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	17	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1716,30 +1716,30 @@ La Matanza	Argentina	Spanish
 Lomas de Zamora	Argentina	Spanish
 Lauro de Freitas	Brazil	Portuguese
 Los Angeles	Chile	Spanish
+Lleida (Lérida)	Spain	Spanish
 Las Palmas de Gran Canaria	Spain	Spanish
 L´Hospitalet de Llobregat	Spain	Spanish
-Lleida (Lérida)	Spain	Spanish
-Liupanshui	China	Chinese
 Lianyungang	China	Chinese
 Liangcheng	China	Chinese
 Lengshuijiang	China	Chinese
-Lázaro Cárdenas	Mexico	Spanish
+Liupanshui	China	Chinese
 Lagos de Moreno	Mexico	Spanish
 Las Margaritas	Mexico	Spanish
+Lázaro Cárdenas	Mexico	Spanish
 Lashio (Lasho)	Myanmar	Burmese
 Lalitapur	Nepal	Nepali
-Ludwigshafen am Rhein	Germany	German
 Leverkusen	Germany	German
+Ludwigshafen am Rhein	Germany	German
 Luchou	Taiwan	Min
 Lungtan	Taiwan	Min
 Lower Hutt	New Zealand	English
 Los Teques	Venezuela	Spanish
 Leninsk-Kuznetski	Russian Federation	Russian
-Los Angeles	United States	English
-Long Beach	United States	English
 Lexington-Fayette	United States	English
 Louisville	United States	English
+Los Angeles	United States	English
 Little Rock	United States	English
+Long Beach	United States	English
 EXPLAIN
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -1898,9 +1898,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
-1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
-1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	17	Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan
+1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1913,28 +1913,28 @@ La Matanza	Argentina	Spanish
 Lomas de Zamora	Argentina	Spanish
 Lauro de Freitas	Brazil	Portuguese
 Los Angeles	Chile	Spanish
+Lleida (Lérida)	Spain	Spanish
 Las Palmas de Gran Canaria	Spain	Spanish
 L´Hospitalet de Llobregat	Spain	Spanish
-Lleida (Lérida)	Spain	Spanish
-Liupanshui	China	Chinese
 Lianyungang	China	Chinese
 Liangcheng	China	Chinese
 Lengshuijiang	China	Chinese
-Lázaro Cárdenas	Mexico	Spanish
+Liupanshui	China	Chinese
 Lagos de Moreno	Mexico	Spanish
+Lázaro Cárdenas	Mexico	Spanish
 Las Margaritas	Mexico	Spanish
 Lashio (Lasho)	Myanmar	Burmese
 Lalitapur	Nepal	Nepali
-Ludwigshafen am Rhein	Germany	German
 Leverkusen	Germany	German
+Ludwigshafen am Rhein	Germany	German
 Luchou	Taiwan	Min
 Lungtan	Taiwan	Min
 Lower Hutt	New Zealand	English
 Los Teques	Venezuela	Spanish
 Leninsk-Kuznetski	Russian Federation	Russian
+Lexington-Fayette	United States	English
 Los Angeles	United States	English
 Long Beach	United States	English
-Lexington-Fayette	United States	English
 Louisville	United States	English
 Little Rock	United States	English
 EXPLAIN
@@ -2099,9 +2099,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
-1	SIMPLE	Country	hash_ALL	PRIMARY	#hash#PRIMARY	3	world.CountryLanguage.Country	239	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	City	hash_ALL	Country	#hash#Country	3	world.CountryLanguage.Country	4079	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
+1	SIMPLE	CountryLanguage	hash_ALL	PRIMARY,Percentage	#hash#PRIMARY	3	world.City.Country	984	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	Country	hash_ALL	PRIMARY	#hash#PRIMARY	3	world.City.Country	239	Using where; Using join buffer (flat, BNLH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2203,9 +2203,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
-1	SIMPLE	Country	hash_ALL	PRIMARY	#hash#PRIMARY	3	world.CountryLanguage.Country	239	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	City	hash_ALL	Country	#hash#Country	3	world.CountryLanguage.Country	4079	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
+1	SIMPLE	CountryLanguage	hash_ALL	PRIMARY,Percentage	#hash#PRIMARY	3	world.City.Country	984	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	Country	hash_ALL	PRIMARY	#hash#PRIMARY	3	world.City.Country	239	Using where; Using join buffer (incremental, BNLH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2307,9 +2307,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
-1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	17	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2411,9 +2411,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
-1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	17	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
+1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2515,9 +2515,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
-1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
-1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	17	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2534,12 +2534,12 @@ Las Palmas de Gran Canaria	Spain	Spanish
 L´Hospitalet de Llobregat	Spain	Spanish
 Lleida (Lérida)	Spain	Spanish
 Liupanshui	China	Chinese
-Lianyungang	China	Chinese
 Liangcheng	China	Chinese
+Lianyungang	China	Chinese
 Lengshuijiang	China	Chinese
 Lázaro Cárdenas	Mexico	Spanish
-Lagos de Moreno	Mexico	Spanish
 Las Margaritas	Mexico	Spanish
+Lagos de Moreno	Mexico	Spanish
 Lashio (Lasho)	Myanmar	Burmese
 Lalitapur	Nepal	Nepali
 Ludwigshafen am Rhein	Germany	German
@@ -2549,9 +2549,9 @@ Lungtan	Taiwan	Min
 Lower Hutt	New Zealand	English
 Los Teques	Venezuela	Spanish
 Leninsk-Kuznetski	Russian Federation	Russian
+Lexington-Fayette	United States	English
 Los Angeles	United States	English
 Long Beach	United States	English
-Lexington-Fayette	United States	English
 Louisville	United States	English
 Little Rock	United States	English
 EXPLAIN
@@ -2619,9 +2619,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
 CountryLanguage.Percentage > 50 AND
 LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
-1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
-1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	17	Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan
+1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result
index 013d1486cd4..227aec5126a 100644
--- a/mysql-test/main/mysqld--help.result
+++ b/mysql-test/main/mysqld--help.result
@@ -1533,7 +1533,7 @@ optimizer-prune-level 1
 optimizer-search-depth 62
 optimizer-selectivity-sampling-limit 100
 optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on
-optimizer-use-condition-selectivity 2
+optimizer-use-condition-selectivity 3
 performance-schema FALSE
 performance-schema-accounts-size -1
 performance-schema-consumer-events-stages-current FALSE
diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result
index 0ecae5bf157..6bc940654bb 100644
--- a/mysql-test/main/opt_tvc.result
+++ b/mysql-test/main/opt_tvc.result
@@ -623,9 +623,9 @@ SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL);
 i
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, 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
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select `test`.`t1`.`i` AS `i` from `test`.`t1` where `test`.`t1`.`i` in (NULL,NULL,NULL,NULL,NULL)
+Note	1003	select NULL AS `i` from `test`.`t1` where 0
 SET in_predicate_conversion_threshold= 5;
 SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL);
 i
diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
index 4767bbf2360..d64408ef1ac 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -2094,14 +2094,18 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	ix_fd	ix_fd	63	NULL	#	Using where; Using index
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
 select count(*) from t1 where fd <'😁';
 count(*)
 40960
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
 select count(*) from t1 ignore index (ix_fd) where fd <'😁';
 count(*)
 40960
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
 drop table t1;
 set names default;
 create table t2 (a int, b int, c int, d int, key x(a, b));
@@ -2276,7 +2280,7 @@ explain extended select * from t2 where c < 44 or (b > 25 and b < 15);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	1000	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where `test`.`t2`.`c` < 44 or `test`.`t2`.`b` > 25 and `test`.`t2`.`b` < 15
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where `test`.`t2`.`c` < 44
 # EXPLAIN EXTENDED should show that 'b > 25 and b < 15' is removed from the WHERE:
 explain extended select * from t2 where (b > 25 and b < 15) or c < 44;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result
index ed54ef09924..fb1b9759376 100644
--- a/mysql-test/main/range_mrr_icp.result
+++ b/mysql-test/main/range_mrr_icp.result
@@ -2096,14 +2096,18 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	ix_fd	ix_fd	63	NULL	#	Using where; Using index
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
 select count(*) from t1 where fd <'😁';
 count(*)
 40960
 Warnings:
 Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
+Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
 select count(*) from t1 ignore index (ix_fd) where fd <'😁';
 count(*)
 40960
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
 drop table t1;
 set names default;
 create table t2 (a int, b int, c int, d int, key x(a, b));
@@ -2278,7 +2282,7 @@ explain extended select * from t2 where c < 44 or (b > 25 and b < 15);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	1000	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where `test`.`t2`.`c` < 44 or `test`.`t2`.`b` > 25 and `test`.`t2`.`b` < 15
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where `test`.`t2`.`c` < 44
 # EXPLAIN EXTENDED should show that 'b > 25 and b < 15' is removed from the WHERE:
 explain extended select * from t2 where (b > 25 and b < 15) or c < 44;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result
index a7c29a5f743..4ca90bd1213 100644
--- a/mysql-test/main/stat_tables.result
+++ b/mysql-test/main/stat_tables.result
@@ -64,10 +64,10 @@ and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
 group by n_name
 order by revenue desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	supplier	ALL	PRIMARY,i_s_nationkey	NULL	NULL	NULL	10	Using where; Using temporary; Using filesort
-1	SIMPLE	nation	eq_ref	PRIMARY,i_n_regionkey	PRIMARY	4	dbt3_s001.supplier.s_nationkey	1	Using where
-1	SIMPLE	region	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.nation.n_regionkey	1	Using where
-1	SIMPLE	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.supplier.s_nationkey	6	
+1	SIMPLE	region	ALL	PRIMARY	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+1	SIMPLE	nation	ref	PRIMARY,i_n_regionkey	i_n_regionkey	5	dbt3_s001.region.r_regionkey	5	
+1	SIMPLE	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	
+1	SIMPLE	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
 1	SIMPLE	orders	ref	PRIMARY,i_o_orderdate,i_o_custkey	i_o_custkey	5	dbt3_s001.customer.c_custkey	15	Using where
 1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
 select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
@@ -171,10 +171,10 @@ and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
 group by n_name
 order by revenue desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	supplier	ALL	PRIMARY,i_s_nationkey	NULL	NULL	NULL	10	Using where; Using temporary; Using filesort
-1	SIMPLE	nation	eq_ref	PRIMARY,i_n_regionkey	PRIMARY	4	dbt3_s001.supplier.s_nationkey	1	Using where
-1	SIMPLE	region	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.nation.n_regionkey	1	Using where
-1	SIMPLE	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.supplier.s_nationkey	6	
+1	SIMPLE	region	ALL	PRIMARY	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+1	SIMPLE	nation	ref	PRIMARY,i_n_regionkey	i_n_regionkey	5	dbt3_s001.region.r_regionkey	5	
+1	SIMPLE	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	
+1	SIMPLE	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	
 1	SIMPLE	orders	ref	PRIMARY,i_o_orderdate,i_o_custkey	i_o_custkey	5	dbt3_s001.customer.c_custkey	15	Using where
 1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
 select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
@@ -207,14 +207,14 @@ and o_orderdate between date '1995-01-01' and date '1996-12-31'
 group by o_year
 order by o_year;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	orders	ALL	PRIMARY,i_o_orderdate,i_o_custkey	NULL	NULL	NULL	1500	Using where; Using temporary; Using filesort
-1	SIMPLE	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	Using where
-1	SIMPLE	n1	eq_ref	PRIMARY,i_n_regionkey	PRIMARY	4	dbt3_s001.customer.c_nationkey	1	Using where
-1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
+1	SIMPLE	region	ALL	PRIMARY	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+1	SIMPLE	part	ALL	PRIMARY	NULL	NULL	NULL	200	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	i_l_suppkey_partkey	5	dbt3_s001.part.p_partkey	30	Using index condition
 1	SIMPLE	supplier	eq_ref	PRIMARY,i_s_nationkey	PRIMARY	4	dbt3_s001.lineitem.l_suppkey	1	Using where
-1	SIMPLE	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_partkey	1	Using where
-1	SIMPLE	region	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.n1.n_regionkey	1	Using where
 1	SIMPLE	n2	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.supplier.s_nationkey	1	
+1	SIMPLE	orders	eq_ref	PRIMARY,i_o_orderdate,i_o_custkey	PRIMARY	4	dbt3_s001.lineitem.l_orderkey	1	Using where
+1	SIMPLE	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	
+1	SIMPLE	n1	ref	PRIMARY,i_n_regionkey	i_n_regionkey	5	dbt3_s001.region.r_regionkey	5	Using where
 select o_year,
 sum(case when nation = 'UNITED STATES' then volume else 0 end) /
 sum(volume) as mkt_share
diff --git a/mysql-test/main/stat_tables_innodb.result b/mysql-test/main/stat_tables_innodb.result
index 22bcb079a27..4eef34c0cd6 100644
--- a/mysql-test/main/stat_tables_innodb.result
+++ b/mysql-test/main/stat_tables_innodb.result
@@ -67,12 +67,12 @@ and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
 group by n_name
 order by revenue desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	orders	range	PRIMARY,i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	213	Using where; Using temporary; Using filesort
-1	SIMPLE	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	Using where
-1	SIMPLE	nation	eq_ref	PRIMARY,i_n_regionkey	PRIMARY	4	dbt3_s001.customer.c_nationkey	1	Using where
-1	SIMPLE	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.customer.c_nationkey	1	Using index
+1	SIMPLE	region	ALL	PRIMARY	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+1	SIMPLE	nation	ref	PRIMARY,i_n_regionkey	i_n_regionkey	5	dbt3_s001.region.r_regionkey	5	
+1	SIMPLE	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	Using index
+1	SIMPLE	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	Using index
+1	SIMPLE	orders	ref	PRIMARY,i_o_orderdate,i_o_custkey	i_o_custkey	5	dbt3_s001.customer.c_custkey	15	Using where
 1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
-1	SIMPLE	region	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.nation.n_regionkey	1	Using where
 select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
 from customer, orders, lineitem, supplier, nation, region
 where c_custkey = o_custkey and l_orderkey = o_orderkey
@@ -83,7 +83,7 @@ and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
 group by n_name
 order by revenue desc;
 n_name	revenue
-PERU	321915.8715
+PERU	321915.87150000007
 ARGENTINA	69817.1451
 set optimizer_switch=@save_optimizer_switch;
 delete from mysql.index_stats;
@@ -198,12 +198,12 @@ and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
 group by n_name
 order by revenue desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	orders	range	PRIMARY,i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	213	Using where; Using temporary; Using filesort
-1	SIMPLE	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	Using where
-1	SIMPLE	nation	eq_ref	PRIMARY,i_n_regionkey	PRIMARY	4	dbt3_s001.customer.c_nationkey	1	Using where
-1	SIMPLE	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.customer.c_nationkey	1	Using index
+1	SIMPLE	region	ALL	PRIMARY	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+1	SIMPLE	nation	ref	PRIMARY,i_n_regionkey	i_n_regionkey	5	dbt3_s001.region.r_regionkey	5	
+1	SIMPLE	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	Using index
+1	SIMPLE	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	Using index
+1	SIMPLE	orders	ref	PRIMARY,i_o_orderdate,i_o_custkey	i_o_custkey	5	dbt3_s001.customer.c_custkey	15	Using where
 1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
-1	SIMPLE	region	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.nation.n_regionkey	1	Using where
 select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
 from customer, orders, lineitem, supplier, nation, region
 where c_custkey = o_custkey and l_orderkey = o_orderkey
@@ -214,7 +214,7 @@ and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
 group by n_name
 order by revenue desc;
 n_name	revenue
-PERU	321915.8715
+PERU	321915.87150000007
 ARGENTINA	69817.1451
 set optimizer_switch=@save_optimizer_switch;
 EXPLAIN select o_year,
@@ -234,14 +234,14 @@ and o_orderdate between date '1995-01-01' and date '1996-12-31'
 group by o_year
 order by o_year;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	orders	ALL	PRIMARY,i_o_orderdate,i_o_custkey	NULL	NULL	NULL	1500	Using where; Using temporary; Using filesort
-1	SIMPLE	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	Using where
-1	SIMPLE	n1	eq_ref	PRIMARY,i_n_regionkey	PRIMARY	4	dbt3_s001.customer.c_nationkey	1	Using where
-1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
+1	SIMPLE	region	ALL	PRIMARY	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+1	SIMPLE	part	ALL	PRIMARY	NULL	NULL	NULL	200	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	i_l_suppkey_partkey	5	dbt3_s001.part.p_partkey	30	Using index condition
 1	SIMPLE	supplier	eq_ref	PRIMARY,i_s_nationkey	PRIMARY	4	dbt3_s001.lineitem.l_suppkey	1	Using where
-1	SIMPLE	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_partkey	1	Using where
-1	SIMPLE	region	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.n1.n_regionkey	1	Using where
 1	SIMPLE	n2	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.supplier.s_nationkey	1	
+1	SIMPLE	orders	eq_ref	PRIMARY,i_o_orderdate,i_o_custkey	PRIMARY	4	dbt3_s001.lineitem.l_orderkey	1	Using where
+1	SIMPLE	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	Using where
+1	SIMPLE	n1	eq_ref	PRIMARY,i_n_regionkey	PRIMARY	4	dbt3_s001.customer.c_nationkey	1	Using where
 select o_year,
 sum(case when nation = 'UNITED STATES' then volume else 0 end) /
 sum(volume) as mkt_share
diff --git a/mysql-test/main/type_date.result b/mysql-test/main/type_date.result
index 897f42a2ec4..c5a6cc077e6 100644
--- a/mysql-test/main/type_date.result
+++ b/mysql-test/main/type_date.result
@@ -192,7 +192,7 @@ COUNT(*)
 0
 EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW();
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 SET timestamp=DEFAULT;
 DROP TABLE t1;
 CREATE TABLE t1 (a DATE);
@@ -761,15 +761,15 @@ a
 EXPLAIN EXTENDED
 SELECT * FROM t1 WHERE LENGTH(a)=10 AND a=TIMESTAMP'2015-08-30 00:00:00.1';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2015-08-30 00:00:00.1'
+Note	1003	select '2015-08-30' AS `a` from `test`.`t1` where 0
 EXPLAIN EXTENDED
 SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2015-08-30 00:00:00.1';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2015-08-30 00:00:00.1' and <cache>(octet_length(DATE'2015-08-30')) = 30 + rand()
+Note	1003	select '2015-08-30' AS `a` from `test`.`t1` where 0
 DROP TABLE t1;
 CREATE TABLE t1 (a DATE);
 INSERT INTO t1 VALUES ('2015-08-30'),('2015-08-31');
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index 5cfc8ceea66..7fb9d00214a 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -2443,7 +2443,7 @@ static Sys_var_ulong Sys_optimizer_use_condition_selectivity(
        "5 - additionally use selectivity of certain non-range predicates "
        "calculated on record samples",
        SESSION_VAR(optimizer_use_condition_selectivity), CMD_LINE(REQUIRED_ARG),
-       VALID_RANGE(1, 5), DEFAULT(2), BLOCK_SIZE(1));
+       VALID_RANGE(1, 5), DEFAULT(3), BLOCK_SIZE(1));
 
 static Sys_var_ulong Sys_optimizer_search_depth(
        "optimizer_search_depth",