5.5 merge

45 files changed, 404 insertions, 40 deletions

diff --git a/mysql-test/r/comments.result b/mysql-test/r/comments.result
index ce817b5012f..c13eb510326 100644
--- a/mysql-test/r/comments.result
+++ b/mysql-test/r/comments.result
@@ -35,6 +35,12 @@ select 1 /*M!50000 +1 */;
 select 1 /*M!50300 +1 */;
 1  +1
 2
+select 2 /*M!99999 +1 */;
+2  +1
+3
+select 2 /*M!100000 +1 */;
+2  +1
+3
 select 2 /*M!999999 +1 */;
 2
 2
diff --git a/mysql-test/r/ctype_utf16.result b/mysql-test/r/ctype_utf16.result
index a1bd49d1926..5d70764f3f8 100644
--- a/mysql-test/r/ctype_utf16.result
+++ b/mysql-test/r/ctype_utf16.result
@@ -671,6 +671,21 @@ FF9D	EFBE9D
 D800DF84	F0908E84
 DBC0DC00	F4808080
 DROP TABLE IF EXISTS t1;
+#
+# BUG#16691598 - ORDER BY LOWER(COLUMN) PRODUCES
+#                OUT-OF-ORDER RESULTS
+#
+CREATE TABLE  t1 SELECT ('a a') as n;
+INSERT INTO t1 VALUES('a b');
+SELECT * FROM t1 ORDER BY LOWER(n) ASC;
+n
+a a
+a b
+SELECT * FROM t1 ORDER BY LOWER(n) DESC;
+n
+a b
+a a
+DROP TABLE t1;
 select @@collation_connection;
 @@collation_connection
 utf16_bin
diff --git a/mysql-test/r/ctype_utf32.result b/mysql-test/r/ctype_utf32.result
index a46bcb33cc6..92327821b84 100644
--- a/mysql-test/r/ctype_utf32.result
+++ b/mysql-test/r/ctype_utf32.result
@@ -670,6 +670,21 @@ HEX(a)	HEX(CONVERT(a USING utf8mb4))
 00010384	F0908E84
 00100000	F4808080
 DROP TABLE IF EXISTS t1;
+#
+# BUG#16691598 - ORDER BY LOWER(COLUMN) PRODUCES
+#                OUT-OF-ORDER RESULTS
+#
+CREATE TABLE  t1 SELECT ('a a') as n;
+INSERT INTO t1 VALUES('a b');
+SELECT * FROM t1 ORDER BY LOWER(n) ASC;
+n
+a a
+a b
+SELECT * FROM t1 ORDER BY LOWER(n) DESC;
+n
+a b
+a a
+DROP TABLE t1;
 select @@collation_connection;
 @@collation_connection
 utf32_bin
diff --git a/mysql-test/r/ctype_utf8mb4.result b/mysql-test/r/ctype_utf8mb4.result
index 2935da39387..003647b69fa 100644
--- a/mysql-test/r/ctype_utf8mb4.result
+++ b/mysql-test/r/ctype_utf8mb4.result
@@ -1046,6 +1046,21 @@ EFBE9D	EFBE9D
 F0908E84	F0908E84
 F4808080	F4808080
 DROP TABLE IF EXISTS t1;
+#
+# BUG#16691598 - ORDER BY LOWER(COLUMN) PRODUCES
+#                OUT-OF-ORDER RESULTS
+#
+CREATE TABLE  t1 SELECT ('a a') as n;
+INSERT INTO t1 VALUES('a b');
+SELECT * FROM t1 ORDER BY LOWER(n) ASC;
+n
+a a
+a b
+SELECT * FROM t1 ORDER BY LOWER(n) DESC;
+n
+a b
+a a
+DROP TABLE t1;
 select @@collation_connection;
 @@collation_connection
 utf8mb4_bin
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index cbf5900d57c..80d2d64dced 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -2253,7 +2253,7 @@ EXPLAIN EXTENDED SELECT a FROM v1 WHERE a > 100 ORDER BY b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select 4 AS `a` from dual where (4 > 100) order by 1
+Note	1003	select 4 AS `a` from dual where 0 order by 1
 DROP VIEW v1;
 DROP TABLE t1;
 CREATE TABLE IF NOT EXISTS `galleries` ( 
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index 019099bde14..6f68483f684 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -999,4 +999,44 @@ c
 11112222
 33334444
 DROP TABLE t1;
+#
+# Bug#16539979 BASIC SELECT COUNT(DISTINCT ID) IS BROKEN.
+# Bug#17867117 ERROR RESULT WHEN "COUNT + DISTINCT + CASE WHEN" NEED MERGE_WALK 
+#
+SET @tmp_table_size_save= @@tmp_table_size;
+SET @@tmp_table_size= 1024;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
+INSERT INTO t1 SELECT a+8 FROM t1;
+INSERT INTO t1 SELECT a+16 FROM t1;
+INSERT INTO t1 SELECT a+32 FROM t1;
+INSERT INTO t1 SELECT a+64 FROM t1;
+INSERT INTO t1 VALUE(NULL);
+SELECT COUNT(DISTINCT a) FROM t1;
+COUNT(DISTINCT a)
+128
+SELECT COUNT(DISTINCT (a+0)) FROM t1;
+COUNT(DISTINCT (a+0))
+128
+DROP TABLE t1;
+create table tb(
+id int auto_increment primary key,
+v varchar(32))
+engine=myisam charset=gbk;
+insert into tb(v) values("aaa");
+insert into tb(v) (select v from tb);
+insert into tb(v) (select v from tb);
+insert into tb(v) (select v from tb);
+insert into tb(v) (select v from tb);
+insert into tb(v) (select v from tb);
+insert into tb(v) (select v from tb);
+update tb set v=concat(v, id);
+select count(distinct case when id<=64 then id end) from tb;
+count(distinct case when id<=64 then id end)
+64
+select count(distinct case when id<=63 then id end) from tb;
+count(distinct case when id<=63 then id end)
+63
+drop table tb;
+SET @@tmp_table_size= @tmp_table_size_save;
 End of 5.5 tests
diff --git a/mysql-test/r/error_simulation.result b/mysql-test/r/error_simulation.result
index d2f5a24ef1d..8c16ebf482e 100644
--- a/mysql-test/r/error_simulation.result
+++ b/mysql-test/r/error_simulation.result
@@ -94,7 +94,7 @@ INSERT INTO t1 VALUES (1),(2);
 INSERT INTO t2 VALUES (1),(2);
 SET SESSION debug_dbug="+d,bug11747970_raise_error";
 INSERT IGNORE INTO t2 SELECT f1 FROM t1 a WHERE NOT EXISTS (SELECT 1 FROM t2 b WHERE a.f1 = b.f1);
-ERROR HY000: Unknown error
+ERROR 70100: Query execution was interrupted
 SET SESSION debug_dbug = DEFAULT;
 DROP TABLE t1,t2;
 #
diff --git a/mysql-test/r/events_trans.result b/mysql-test/r/events_trans.result
index 37951c30787..084587079ba 100644
--- a/mysql-test/r/events_trans.result
+++ b/mysql-test/r/events_trans.result
@@ -135,3 +135,4 @@ SELECT * FROM t2;
 a
 ROLLBACK WORK TO SAVEPOINT A;
 DROP TABLE t1, t2;
+DROP EVENT e1;
diff --git a/mysql-test/r/func_compress.result b/mysql-test/r/func_compress.result
index c2f1e5bf273..011dec4d555 100644
--- a/mysql-test/r/func_compress.result
+++ b/mysql-test/r/func_compress.result
@@ -102,7 +102,6 @@ a
 foo
 Warnings:
 Warning	1259	ZLIB: Input data corrupted
-Warning	1259	ZLIB: Input data corrupted
 explain select *, uncompress(a) from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 8e50c045775..8e2bdeae93c 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1458,6 +1458,8 @@ DROP TABLE derived1;
 DROP TABLE D;
 CREATE TABLE t1 (a INT, b INT);
 INSERT INTO t1 VALUES (1,1), (1,2), (1,3);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (3),(4);
 SET SQL_MODE='ONLY_FULL_GROUP_BY';
 SELECT COUNT(*) FROM t1;
 COUNT(*)
@@ -1473,12 +1475,19 @@ COUNT(*)
 SELECT COUNT(*), (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a) 
 FROM t1 outr;
 ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT COUNT(*) FROM t1 outr, (SELECT b, count(*) FROM t2) as t3;
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT COUNT(*) FROM t1 outr where (1,1) in (SELECT a, count(*) FROM t2);
+COUNT(*)
+0
 SELECT COUNT(*) FROM t1 a JOIN t1 outr 
 ON a.a= (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a);
 COUNT(*)
 0
+SELECT * FROM (SELECT a FROM t1 GROUP BY a) sq JOIN t2 ON a = b;
+a	b
 SET SQL_MODE=default;
-DROP TABLE t1;
+DROP TABLE t1,t2;
 End of 5.0 tests
 #
 # BUG#47280 - strange results from count(*) with order by multiple 
diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result
index a033c91bc05..1d1300a96db 100644
--- a/mysql-test/r/func_misc.result
+++ b/mysql-test/r/func_misc.result
@@ -264,6 +264,13 @@ INET_NTOA(0)
 SELECT '1' IN ('1', INET_NTOA(0));
 '1' IN ('1', INET_NTOA(0))
 1
+SELECT NAME_CONST('a', -(1 OR 2)) OR 1;
+ERROR HY000: Incorrect arguments to NAME_CONST
+SELECT NAME_CONST('a', -(1 AND 2)) AND 1;
+ERROR HY000: Incorrect arguments to NAME_CONST
+SELECT NAME_CONST('a', -(1)) OR 1;
+NAME_CONST('a', -(1)) OR 1
+1
 #
 # Bug #52165: Assertion failed: file .\dtoa.c, line 465
 # 
@@ -561,6 +568,3 @@ ERROR 42000: Identifier name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
 #
 # End of 5.5 tests
 #
-#
-# End of tests
-#
diff --git a/mysql-test/r/func_regexp.result b/mysql-test/r/func_regexp.result
index 78b8e5d908a..f405a2297cb 100644
--- a/mysql-test/r/func_regexp.result
+++ b/mysql-test/r/func_regexp.result
@@ -52,7 +52,7 @@ explain extended select * from t1 where xxx regexp('is a test of some long text
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 Warnings:
-Note	1003	select 'this is a test of some long text to see what happens' AS `xxx` from dual where ('this is a test of some long text to see what happens' regexp 'is a test of some long text to')
+Note	1003	select 'this is a test of some long text to see what happens' AS `xxx` from dual where 1
 select * from t1 where xxx regexp('is a test of some long text to ');
 xxx
 this is a test of some long text to see what happens
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index a588d89b919..b1750df326d 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -1153,7 +1153,6 @@ str	num
 notnumber	0
 Warnings:
 Warning	1292	Truncated incorrect DOUBLE value: 'notnumber'
-Warning	1292	Truncated incorrect DOUBLE value: 'notnumber'
 SELECT * FROM t1, t2 WHERE num=substring(str from 1 for 6);
 str	num
 notnumber	0
diff --git a/mysql-test/r/gis-precise.result b/mysql-test/r/gis-precise.result
index 3b07be3930a..71eed65b2ea 100644
--- a/mysql-test/r/gis-precise.result
+++ b/mysql-test/r/gis-precise.result
@@ -452,3 +452,6 @@ ST_NUMPOINTS(ST_EXTERIORRING(ST_BUFFER( POLYGONFROMTEXT( 'POLYGON( ( 0.0 -3.0,
   0.0 -3.0
 ))' ),
 136
+select astext(buffer(st_linestringfromwkb(linestring(point(-1,1), point(-1,-2))),-1));
+astext(buffer(st_linestringfromwkb(linestring(point(-1,1), point(-1,-2))),-1))
+GEOMETRYCOLLECTION EMPTY
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index eb9f1a57c32..c00f07e7a81 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -1574,6 +1574,27 @@ SELECT 1 FROM g1 WHERE a >= ANY
 1
 DROP TABLE g1;
 #
+# Bug#16451878 GEOMETRY QUERY CRASHES SERVER
+#
+# should not crash
+SELECT ASTEXT(0x0100000000030000000100000000000010);
+ASTEXT(0x0100000000030000000100000000000010)
+NULL
+#should not crash
+SELECT ENVELOPE(0x0100000000030000000100000000000010);
+ENVELOPE(0x0100000000030000000100000000000010)
+NULL
+#should not crash
+SELECT
+GEOMETRYN(0x0100000000070000000100000001030000000200000000000000ffff0000, 1);
+GEOMETRYN(0x0100000000070000000100000001030000000200000000000000ffff0000, 1)
+NULL
+#should not crash
+SELECT
+GEOMETRYN(0x0100000000070000000100000001030000000200000000000000ffffff0f, 1);
+GEOMETRYN(0x0100000000070000000100000001030000000200000000000000ffffff0f, 1)
+NULL
+#
 # MDEV-3819 missing constraints for spatial column types
 #
 create table t1 (pt point);
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 4a806e0831c..e303c288552 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1311,7 +1311,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	100.00	
 Warnings:
-Note	1003	select 1 AS `f1`,NULL AS `f2`,3 AS `f3`,NULL AS `f1`,NULL AS `f2` from `test`.`t2` where ((coalesce(1,NULL),3) in ((1,3),(2,2)))
+Note	1003	select 1 AS `f1`,NULL AS `f2`,3 AS `f3`,NULL AS `f1`,NULL AS `f2` from `test`.`t2` where 1
 SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2
 WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2));
 f1	f2	f3	f1	f2
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index 6a543f920e4..88f2fd7c630 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -1322,7 +1322,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	100.00	
 Warnings:
-Note	1003	select 1 AS `f1`,NULL AS `f2`,3 AS `f3`,NULL AS `f1`,NULL AS `f2` from `test`.`t2` where ((coalesce(1,NULL),3) in ((1,3),(2,2)))
+Note	1003	select 1 AS `f1`,NULL AS `f2`,3 AS `f3`,NULL AS `f1`,NULL AS `f2` from `test`.`t2` where 1
 SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2
 WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2));
 f1	f2	f3	f1	f2
diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result
index 5971a103d0a..ea3a345de28 100644
--- a/mysql-test/r/mysqld--help.result
+++ b/mysql-test/r/mysqld--help.result
@@ -1116,7 +1116,7 @@ port 3306
 port-open-timeout 0
 preload-buffer-size 32768
 profiling-history-size 15
-progress-report-time 56
+progress-report-time 5
 query-alloc-block-size 8192
 query-cache-limit 1048576
 query-cache-min-res-unit 4096
diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index 131bd6fd593..882da55b7fb 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -5264,6 +5264,20 @@ slow_log	CREATE TABLE `slow_log` (
 SET @@global.log_output=       @old_log_output_state;
 SET @@global.slow_query_log=   @old_slow_query_log_state;
 SET @@global.general_log=      @old_general_log_state;
+# MDEV-5481 mysqldump fails to dump geometry types properly
+create table t1 (g GEOMETRY) CHARSET koi8r;
+create table t2 (g GEOMETRY) CHARSET koi8r;
+insert into t1 values (point(1,1)), (point(2,2));
+##################################################
+\0\0\0\0

\0\0\0\0\0\0\0\0\0ð?\0\0\0\0\0\0ð?
+\0\0\0\0

\0\0\0\0\0\0\0\0\0\0@\0\0\0\0\0\0\0@
+##################################################
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET koi8r;
+select astext(g) from t2;
+astext(g)
+POINT(1 1)
+POINT(2 2)
+drop table t1, t2;
 #
 # End of 5.1 tests
 #
diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result
index c621bd8bf43..3b80ae1d474 100644
--- a/mysql-test/r/partition.result
+++ b/mysql-test/r/partition.result
@@ -2493,6 +2493,73 @@ i
 3
 4
 DROP TABLE t1;
+#
+# MDEV-5177: ha_partition and innodb index intersection produce fewer rows (MySQL Bug#70703)
+#
+create table t1 (
+a int not null,
+b int not null,
+pk int not null,
+primary key (pk),
+key(a),
+key(b)
+) partition by hash(pk) partitions 10;
+insert into t1 values (1,2,4);
+insert into t1 values (1,0,17);
+insert into t1 values (1,2,25);
+insert into t1 values (10,20,122);
+insert into t1 values (10,20,123);
+create table t2 (a int);
+insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+insert into t1 select 1,2, 200 + A.a + 10*B.a + 100*C.a from t2 A, t2 B, t2 C;
+insert into t1 select 10+A.a + 10*B.a + 100*C.a + 1000*D.a,
+10+A.a + 10*B.a + 100*C.a  + 1000*D.a, 
+2000 + A.a + 10*B.a + 100*C.a + 1000*D.a
+from t2 A, t2 B, t2 C ,t2 D;
+explain select * from t1 where a=1 and b=2 and  pk between 1 and 999999 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	PRIMARY,a,b	b	4	const	982	Using where
+create temporary table t3 as
+select * from t1 where a=1 and b=2 and  pk between 1 and 999 ;
+select count(*) from t3;
+count(*)
+802
+drop table t3;
+create temporary table t3 as
+select * from t1 ignore index(a,b)  where a=1 and b=2 and  pk between 1 and 999 ;
+select count(*) from t3;
+count(*)
+802
+drop table t3;
+drop table t1,t2;
+#
+# MDEV-5555: Incorrect index_merge on BTREE indices
+#
+CREATE TABLE t1 (
+id bigint(20) unsigned NOT NULL,
+id2 bigint(20) unsigned NOT NULL,
+dob date DEFAULT NULL,
+address char(100) DEFAULT NULL,
+city char(35) DEFAULT NULL,
+hours_worked_per_week smallint(5) unsigned DEFAULT NULL,
+weeks_worked_last_year tinyint(3) unsigned DEFAULT NULL,
+KEY dob (dob),
+KEY address (address),
+KEY city (city),
+KEY hours_worked_per_week (hours_worked_per_week),
+KEY weeks_worked_last_year (weeks_worked_last_year)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+PARTITION BY KEY (id) PARTITIONS 5;
+# Insert some rows 
+select * from t1 where hours_worked_per_week = 40 and weeks_worked_last_year = 52 and dob < '1949-11-21';
+id	id2	dob	address	city	hours_worked_per_week	weeks_worked_last_year
+16	16	1949-11-07	address16	city16	40	52
+50	50	1923-09-08	address50	city50	40	52
+select * from t1 IGNORE INDEX(dob, weeks_worked_last_year, hours_worked_per_week) where hours_worked_per_week = 40 and weeks_worked_last_year = 52 and dob < '1949-11-21';
+id	id2	dob	address	city	hours_worked_per_week	weeks_worked_last_year
+16	16	1949-11-07	address16	city16	40	52
+50	50	1923-09-08	address50	city50	40	52
+drop table t1;
 CREATE TABLE t1 ( d DATE NOT NULL)
 PARTITION BY RANGE( YEAR(d) ) (
 PARTITION p0 VALUES LESS THAN (1960),
diff --git a/mysql-test/r/partition_order.result b/mysql-test/r/partition_order.result
index 78ff7cd3121..06c1b63a382 100644
--- a/mysql-test/r/partition_order.result
+++ b/mysql-test/r/partition_order.result
@@ -752,8 +752,8 @@ select * from t1 force index (b) where b < 10 ORDER BY b DESC;
 a	b
 6	6
 4	5
-2	4
 30	4
+2	4
 3	3
 35	2
 7	1
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 53d0a4f85db..1ecb5c143ec 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -4041,3 +4041,14 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW v2;
 DROP TABLE t1, t2;
 # End of 5.3 tests
+#
+# MDEV-5505: Assertion `! is_set()' fails on PREPARE SELECT
+# with out of range in GROUP BY
+#
+CREATE TABLE t1 (a INT);
+PREPARE stmt FROM "SELECT 1 FROM t1 GROUP BY 0 OR 18446744073709551615+1";
+ERROR 22003: BIGINT UNSIGNED value is out of range in '(18446744073709551615 + 1)'
+SELECT 1 FROM t1 GROUP BY 0 OR 18446744073709551615+1;
+ERROR 22003: BIGINT UNSIGNED value is out of range in '(18446744073709551615 + 1)'
+drop table t1;
+# End of 5.3 tests
diff --git a/mysql-test/r/rpl_mysqldump_slave.result b/mysql-test/r/rpl_mysqldump_slave.result
index 1b13ebb79c1..6ff7ca23352 100644
--- a/mysql-test/r/rpl_mysqldump_slave.result
+++ b/mysql-test/r/rpl_mysqldump_slave.result
@@ -11,4 +11,11 @@ START SLAVE;
 STOP SLAVE;
 CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_MYPORT, MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=BINLOG_START;
 START SLAVE;
+slave start;
+Warnings:
+Note	1254	Slave is already running
+CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=BINLOG_START;
+slave start;
+Warnings:
+Note	1254	Slave is already running
 include/rpl_end.inc
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 670c1460ba7..11ccf2fbd6c 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -5315,7 +5315,7 @@ SELECT * FROM t1 WHERE (1=2 OR t1.pk=2) AND t1.a <> 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select 2 AS `pk`,0 AS `a` from `test`.`t1` where (0 <> 0)
+Note	1003	select 2 AS `pk`,0 AS `a` from `test`.`t1` where 0
 DROP TABLE t1;
 SELECT * FROM mysql.time_zone 
 WHERE ( NOT (Use_leap_seconds <= Use_leap_seconds AND Time_zone_id != 1) 
@@ -5338,7 +5338,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	idx	NULL	NULL	NULL	1	100.00	
 1	SIMPLE	t2	ref	idx	idx	5	const	1	100.00	Using index
 Warnings:
-Note	1003	select 8 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` = 8) and 1)
+Note	1003	select 8 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`c` = 8)
 SELECT * FROM t1 INNER JOIN t2 ON ( c = a ) 
 WHERE 1 IS NULL OR b < 33 AND b = c;
 a	b	c
@@ -5462,7 +5462,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	system	PRIMARY	NULL	NULL	NULL	1	100.00	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` = 1) and ((`test`.`t1`.`b1` = 6) or 0))
+Note	1003	select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` = 1) and (`test`.`t1`.`b1` = 6))
 INSERT INTO t1 VALUES (3,1,6);
 SELECT * FROM t1, t2
 WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL );
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index 81eda02fda9..d6c775ff16d 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -5326,7 +5326,7 @@ SELECT * FROM t1 WHERE (1=2 OR t1.pk=2) AND t1.a <> 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select 2 AS `pk`,0 AS `a` from `test`.`t1` where (0 <> 0)
+Note	1003	select 2 AS `pk`,0 AS `a` from `test`.`t1` where 0
 DROP TABLE t1;
 SELECT * FROM mysql.time_zone 
 WHERE ( NOT (Use_leap_seconds <= Use_leap_seconds AND Time_zone_id != 1) 
@@ -5349,7 +5349,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	idx	NULL	NULL	NULL	1	100.00	
 1	SIMPLE	t2	ref	idx	idx	5	const	1	100.00	Using index
 Warnings:
-Note	1003	select 8 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` = 8) and 1)
+Note	1003	select 8 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`c` = 8)
 SELECT * FROM t1 INNER JOIN t2 ON ( c = a ) 
 WHERE 1 IS NULL OR b < 33 AND b = c;
 a	b	c
@@ -5473,7 +5473,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	system	PRIMARY	NULL	NULL	NULL	1	100.00	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` = 1) and ((`test`.`t1`.`b1` = 6) or 0))
+Note	1003	select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` = 1) and (`test`.`t1`.`b1` = 6))
 INSERT INTO t1 VALUES (3,1,6);
 SELECT * FROM t1, t2
 WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL );
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index 670c1460ba7..11ccf2fbd6c 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -5315,7 +5315,7 @@ SELECT * FROM t1 WHERE (1=2 OR t1.pk=2) AND t1.a <> 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select 2 AS `pk`,0 AS `a` from `test`.`t1` where (0 <> 0)
+Note	1003	select 2 AS `pk`,0 AS `a` from `test`.`t1` where 0
 DROP TABLE t1;
 SELECT * FROM mysql.time_zone 
 WHERE ( NOT (Use_leap_seconds <= Use_leap_seconds AND Time_zone_id != 1) 
@@ -5338,7 +5338,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	idx	NULL	NULL	NULL	1	100.00	
 1	SIMPLE	t2	ref	idx	idx	5	const	1	100.00	Using index
 Warnings:
-Note	1003	select 8 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` = 8) and 1)
+Note	1003	select 8 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`c` = 8)
 SELECT * FROM t1 INNER JOIN t2 ON ( c = a ) 
 WHERE 1 IS NULL OR b < 33 AND b = c;
 a	b	c
@@ -5462,7 +5462,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	system	PRIMARY	NULL	NULL	NULL	1	100.00	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` = 1) and ((`test`.`t1`.`b1` = 6) or 0))
+Note	1003	select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` = 1) and (`test`.`t1`.`b1` = 6))
 INSERT INTO t1 VALUES (3,1,6);
 SELECT * FROM t1, t2
 WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL );
diff --git a/mysql-test/r/stat_tables_par.result b/mysql-test/r/stat_tables_par.result
index 497844ec68d..f9b2a7921cb 100644
--- a/mysql-test/r/stat_tables_par.result
+++ b/mysql-test/r/stat_tables_par.result
@@ -212,7 +212,7 @@ analyze table lineitem persistent for all;
 set debug_sync='open_and_process_table WAIT_FOR parker';
 set debug_sync='statistics_read_start SIGNAL go1 WAIT_FOR go2';
 use dbt3_s001;
-select * from mysql.index_stats, lineitem where index_name= 'i_l_shipdate' and l_orderkey=1 and l_partkey=68;
+select * from mysql.index_stats, lineitem where index_name= 'i_l_shipdate' and l_orderkey=1 and l_partkey=68 order by prefix_arity;;
 db_name	table_name	index_name	prefix_arity	avg_frequency	l_orderkey	l_partkey	l_suppkey	l_linenumber	l_quantity	l_extendedprice	l_discount	l_tax	l_returnflag	l_linestatus	l_shipDATE	l_commitDATE	l_receiptDATE	l_shipinstruct	l_shipmode	l_comment
 dbt3_s001	lineitem	i_l_shipdate	1	2.6500	1	68	9	2	36	34850.16	0.09	0.06	N	O	1996-04-12	1996-02-28	1996-04-20	TAKE BACK RETURN	MAIL	slyly bold pinto beans detect s
 set debug_sync='RESET';
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 4d9bbf93168..f7263938108 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -561,7 +561,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	100.00	Using index
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
-Note	1003	select 3 AS `numreponse` from `test`.`t1` where (1 = '1')
+Note	1003	select 3 AS `numreponse` from `test`.`t1` where 1
 drop table t1;
 CREATE TABLE t1 (a int(1));
 INSERT INTO t1 VALUES (1);
@@ -6098,7 +6098,7 @@ FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
 sq4_alias1.col_varchar_key = @var3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+1	SIMPLE	sq4_alias1	system	NULL	NULL	NULL	NULL	0	const row not found
 SELECT @var3:=12, sq4_alias1.*
 FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index e6f30472777..4be5e7a99e5 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2317,6 +2317,33 @@ WHERE a='r' AND (c,b) NOT IN (SELECT c,b FROM t2 WHERE (c,b)=(t.c,t.b));
 a	b	c	d
 DROP TABLE t1, t2;
 #
+# MDEV-5468: assertion failure with a simplified condition in subselect
+#
+CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,1);
+CREATE TABLE t2 ( pk int PRIMARY KEY, c INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,4), (2,6);
+SELECT ( SELECT MAX(b) FROM t1, t2 WHERE pk = a AND b < from_sq.c ) AS select_sq,
+COUNT( DISTINCT from_sq.c ) 
+FROM ( SELECT DISTINCT t2_1.* FROM t2 AS t2_1, t2 AS t2_2 ) AS from_sq
+GROUP BY select_sq ;
+select_sq	COUNT( DISTINCT from_sq.c )
+1	2
+DROP TABLE t1,t2;
+CREATE TABLE t1 (id int, a2 char(2), a3 char(3)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'BE','BEL');
+CREATE TABLE t2 (id int, a2 char(2), a3 char(3)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,'BE','BEL'), (2,'MX','MEX');
+CREATE VIEW v2 AS SELECT DISTINCT * FROM t2;
+SELECT * FROM t1 AS outer_t1, v2  
+WHERE v2.a3 = outer_t1.a3 
+AND EXISTS ( SELECT * FROM t1 WHERE a2 < v2.a2 AND id = outer_t1.id )
+AND outer_t1.a3 < 'J'    
+ORDER BY v2.id;
+id	a2	a3	id	a2	a3
+DROP VIEW v2;
+DROP TABLE t1,t2;
+#
 # MDEV-3899  Valgrind warnings (blocks are definitely lost) in filesort on IN subquery with SUM and DISTINCT
 #
 CREATE TABLE t1 (a INT) ENGINE=MyISAM;
diff --git a/mysql-test/r/subselect_exists_to_in.result b/mysql-test/r/subselect_exists_to_in.result
index f9d4c140936..02c0ac747ce 100644
--- a/mysql-test/r/subselect_exists_to_in.result
+++ b/mysql-test/r/subselect_exists_to_in.result
@@ -565,7 +565,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	100.00	Using index
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
-Note	1003	select 3 AS `numreponse` from `test`.`t1` where (1 = '1')
+Note	1003	select 3 AS `numreponse` from `test`.`t1` where 1
 drop table t1;
 CREATE TABLE t1 (a int(1));
 INSERT INTO t1 VALUES (1);
@@ -6106,7 +6106,7 @@ FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
 sq4_alias1.col_varchar_key = @var3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+1	SIMPLE	sq4_alias1	system	NULL	NULL	NULL	NULL	0	const row not found
 SELECT @var3:=12, sq4_alias1.*
 FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
diff --git a/mysql-test/r/subselect_extra.result b/mysql-test/r/subselect_extra.result
index 5849ccda631..47cb8bca734 100644
--- a/mysql-test/r/subselect_extra.result
+++ b/mysql-test/r/subselect_extra.result
@@ -46,7 +46,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
 Note	1276	Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1
-Note	1003	select 1 AS `id`,'2007-04-25 18:30:22' AS `cur_date` from (dual) where ('2007-04-25 18:30:22' = 0)
+Note	1003	select 1 AS `id`,'2007-04-25 18:30:22' AS `cur_date` from (dual) where 0
 select * from t1
 where id in (select id from t1 as x1 where (t1.cur_date is null));
 id	cur_date
@@ -57,7 +57,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
 Note	1276	Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1
-Note	1003	select 1 AS `id`,'2007-04-25' AS `cur_date` from (dual) where ('2007-04-25' = 0)
+Note	1003	select 1 AS `id`,'2007-04-25' AS `cur_date` from (dual) where 0
 select * from t2
 where id in (select id from t2 as x1 where (t2.cur_date is null));
 id	cur_date
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 5850e9028ce..740fe27b297 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -568,7 +568,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	100.00	Using index
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
-Note	1003	select 3 AS `numreponse` from `test`.`t1` where (1 = '1')
+Note	1003	select 3 AS `numreponse` from `test`.`t1` where 1
 drop table t1;
 CREATE TABLE t1 (a int(1));
 INSERT INTO t1 VALUES (1);
@@ -6097,7 +6097,7 @@ FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
 sq4_alias1.col_varchar_key = @var3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+1	SIMPLE	sq4_alias1	system	NULL	NULL	NULL	NULL	0	const row not found
 SELECT @var3:=12, sq4_alias1.*
 FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 2ac977f4574..1fb76e7353f 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -564,7 +564,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	100.00	Using index
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
-Note	1003	select 3 AS `numreponse` from `test`.`t1` where (1 = '1')
+Note	1003	select 3 AS `numreponse` from `test`.`t1` where 1
 drop table t1;
 CREATE TABLE t1 (a int(1));
 INSERT INTO t1 VALUES (1);
@@ -6093,7 +6093,7 @@ FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
 sq4_alias1.col_varchar_key = @var3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+1	SIMPLE	sq4_alias1	system	NULL	NULL	NULL	NULL	0	const row not found
 SELECT @var3:=12, sq4_alias1.*
 FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 213eeff5572..d235f0b7e3b 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -567,7 +567,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	100.00	Using index
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
-Note	1003	select 3 AS `numreponse` from `test`.`t1` where (1 = '1')
+Note	1003	select 3 AS `numreponse` from `test`.`t1` where 1
 drop table t1;
 CREATE TABLE t1 (a int(1));
 INSERT INTO t1 VALUES (1);
@@ -6104,7 +6104,7 @@ FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
 sq4_alias1.col_varchar_key = @var3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+1	SIMPLE	sq4_alias1	system	NULL	NULL	NULL	NULL	0	const row not found
 SELECT @var3:=12, sq4_alias1.*
 FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index d05a703e647..a9cd1c6dcb5 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -564,7 +564,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	100.00	Using index
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
-Note	1003	select 3 AS `numreponse` from `test`.`t1` where (1 = '1')
+Note	1003	select 3 AS `numreponse` from `test`.`t1` where 1
 drop table t1;
 CREATE TABLE t1 (a int(1));
 INSERT INTO t1 VALUES (1);
@@ -6093,7 +6093,7 @@ FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
 sq4_alias1.col_varchar_key = @var3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+1	SIMPLE	sq4_alias1	system	NULL	NULL	NULL	NULL	0	const row not found
 SELECT @var3:=12, sq4_alias1.*
 FROM t1 AS sq4_alias1
 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index b5263163cb1..c76d40d1a5f 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -2793,6 +2793,22 @@ mysql
 information_schema
 DROP TABLE t1;
 #
+# MDEV-5581: Server crashes in in JOIN::prepare on 2nd execution of PS with materialization+semijoin
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (2),(3);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (8),(9);
+CREATE TABLE t3 (c INT, INDEX(c));
+INSERT INTO t2 VALUES (5),(6);
+PREPARE stmt FROM 
+"SELECT * FROM t1 WHERE ( 9, 5 ) IN ( SELECT b, COUNT(*) FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) )";
+EXECUTE stmt;
+a
+EXECUTE stmt;
+a
+DROP TABLE t1,t2,t3;
+#
 # MySQL Bug#13340270: assertion table->sort.record_pointers == __null
 #
 CREATE TABLE t1 (
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index d87514923c4..2975176c64a 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -1109,6 +1109,23 @@ t3 AS t3a INNER JOIN t3 AS t3b ON ( t3a.i3 = t3b.i3 )
 );
 i1
 DROP TABLE t1,t2,t3;
+#
+# MDEV-5582: Plugin 'MEMORY' has ref_count=1 after shutdown with materialization+semijoin
+#
+CREATE TABLE t1 (a INT) engine=innodb;
+INSERT INTO t1 VALUES (8),(9);
+CREATE TABLE t2 (b INT) engine=innodb;
+INSERT INTO t2 VALUES (2),(3);
+CREATE TABLE t3 (c INT, INDEX(c)) engine=innodb;
+INSERT INTO t2 VALUES (4),(5);
+explain
+SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+3	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) );
+a
+DROP TABLE t1,t2,t3;
 DROP TABLE IF EXISTS t1,t2,t3,t4;
 #
 # MDEV-4782: Valgrind warnings (Conditional jump or move depends on uninitialised value) with InnoDB, semijoin
diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index 00e393635a2..ff97882c469 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -1124,6 +1124,23 @@ t3 AS t3a INNER JOIN t3 AS t3b ON ( t3a.i3 = t3b.i3 )
 );
 i1
 DROP TABLE t1,t2,t3;
+#
+# MDEV-5582: Plugin 'MEMORY' has ref_count=1 after shutdown with materialization+semijoin
+#
+CREATE TABLE t1 (a INT) engine=innodb;
+INSERT INTO t1 VALUES (8),(9);
+CREATE TABLE t2 (b INT) engine=innodb;
+INSERT INTO t2 VALUES (2),(3);
+CREATE TABLE t3 (c INT, INDEX(c)) engine=innodb;
+INSERT INTO t2 VALUES (4),(5);
+explain
+SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+3	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) );
+a
+DROP TABLE t1,t2,t3;
 DROP TABLE IF EXISTS t1,t2,t3,t4;
 #
 # MDEV-4782: Valgrind warnings (Conditional jump or move depends on uninitialised value) with InnoDB, semijoin
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index 2306f19606c..f4d7d986a9d 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -1111,6 +1111,23 @@ t3 AS t3a INNER JOIN t3 AS t3b ON ( t3a.i3 = t3b.i3 )
 );
 i1
 DROP TABLE t1,t2,t3;
+#
+# MDEV-5582: Plugin 'MEMORY' has ref_count=1 after shutdown with materialization+semijoin
+#
+CREATE TABLE t1 (a INT) engine=innodb;
+INSERT INTO t1 VALUES (8),(9);
+CREATE TABLE t2 (b INT) engine=innodb;
+INSERT INTO t2 VALUES (2),(3);
+CREATE TABLE t3 (c INT, INDEX(c)) engine=innodb;
+INSERT INTO t2 VALUES (4),(5);
+explain
+SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+3	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) );
+a
+DROP TABLE t1,t2,t3;
 DROP TABLE IF EXISTS t1,t2,t3,t4;
 #
 # MDEV-4782: Valgrind warnings (Conditional jump or move depends on uninitialised value) with InnoDB, semijoin
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 60e309cdf4a..92ad951325b 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -2807,6 +2807,22 @@ information_schema
 mysql
 DROP TABLE t1;
 #
+# MDEV-5581: Server crashes in in JOIN::prepare on 2nd execution of PS with materialization+semijoin
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (2),(3);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (8),(9);
+CREATE TABLE t3 (c INT, INDEX(c));
+INSERT INTO t2 VALUES (5),(6);
+PREPARE stmt FROM 
+"SELECT * FROM t1 WHERE ( 9, 5 ) IN ( SELECT b, COUNT(*) FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) )";
+EXECUTE stmt;
+a
+EXECUTE stmt;
+a
+DROP TABLE t1,t2,t3;
+#
 # MySQL Bug#13340270: assertion table->sort.record_pointers == __null
 #
 CREATE TABLE t1 (
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 61845c7b2d4..51966147bdd 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -519,7 +519,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
 Note	1276	Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1
-Note	1003	select 1 AS `id`,'2007-04-25 18:30:22' AS `cur_date` from (dual) where ('2007-04-25 18:30:22' = 0)
+Note	1003	select 1 AS `id`,'2007-04-25 18:30:22' AS `cur_date` from (dual) where 0
 select * from t1
 where id in (select id from t1 as x1 where (t1.cur_date is null));
 id	cur_date
@@ -530,7 +530,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
 Note	1276	Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1
-Note	1003	select 1 AS `id`,'2007-04-25' AS `cur_date` from (dual) where ('2007-04-25' = 0)
+Note	1003	select 1 AS `id`,'2007-04-25' AS `cur_date` from (dual) where 0
 select * from t2
 where id in (select id from t2 as x1 where (t2.cur_date is null));
 id	cur_date
diff --git a/mysql-test/r/udf_debug_sync.result b/mysql-test/r/udf_debug_sync.result
new file mode 100644
index 00000000000..2db75f9aa76
--- /dev/null
+++ b/mysql-test/r/udf_debug_sync.result
@@ -0,0 +1,20 @@
+CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
+CREATE VIEW v1 AS SELECT myfunc_int(1);
+SET debug_sync='mysql_create_function_after_lock SIGNAL locked WAIT_FOR go';
+CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB";
+SET debug_sync='now WAIT_FOR locked';
+SET debug_sync='find_udf_before_lock SIGNAL go';
+SELECT * FROM v1;
+myfunc_int(1)
+1
+FLUSH TABLES;
+SET debug_sync='mysql_drop_function_after_lock SIGNAL locked WAIT_FOR go';
+DROP FUNCTION myfunc_double;
+SET debug_sync='now WAIT_FOR locked';
+SET debug_sync='find_udf_before_lock SIGNAL go';
+SELECT * FROM v1;
+myfunc_int(1)
+1
+SET debug_sync='RESET';
+DROP VIEW v1;
+DROP FUNCTION myfunc_int;
diff --git a/mysql-test/r/union_crash-714.result b/mysql-test/r/union_crash-714.result
new file mode 100644
index 00000000000..4a51f88b76f
--- /dev/null
+++ b/mysql-test/r/union_crash-714.result
@@ -0,0 +1,5 @@
+create table t1 (i tinyint);
+set debug_dbug='+d,bug11747970_raise_error';
+insert into t1 (i) select i from t1 union select i from t1;
+ERROR 70100: Query execution was interrupted
+drop table t1;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 8097d088aca..9d3a254015e 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -4417,7 +4417,7 @@ WHERE f1<>0 OR f2<>0 AND f4='v' AND (f2<>0 OR f3<>0 AND f5<>0 OR f4 LIKE '%b%');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 Warnings:
-Note	1003	select 'r' AS `f4` from dual where (20 <> 0)
+Note	1003	select 'r' AS `f4` from dual where 1
 DROP VIEW v1;
 DROP TABLE t1;
 #
diff --git a/mysql-test/r/xml.result b/mysql-test/r/xml.result
index 999178a0d52..6c78e9eac55 100644
--- a/mysql-test/r/xml.result
+++ b/mysql-test/r/xml.result
@@ -1161,6 +1161,9 @@ UPDATEXML('<a><c><a>x</a></c></a>','(a)//a','<b />')
 SELECT ExtractValue('<a><a>aa</a><b>bb</b></a>','(a)/a|(a)/b');
 ExtractValue('<a><a>aa</a><b>bb</b></a>','(a)/a|(a)/b')
 aa bb
+SELECT ExtractValue('<a><b>abc</b><c>2</c><d>1</d></a>','substring(/a/b,..)');
+ExtractValue('<a><b>abc</b><c>2</c><d>1</d></a>','substring(/a/b,..)')
+
 #
 # End of 5.5 tests
 #