10.0-base merge

37 files changed, 668 insertions, 115 deletions

diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index 9d31ceab4d1..699d3279e3e 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -465,6 +465,20 @@ t1.val=t3.val
 ;
 ERROR 42S22: Unknown column 'v.val' in 'field list'
 drop table t1, t2;
+#
+# MDEV-5353: server crash on subselect if WHERE applied to some
+# result field
+#
+SELECT * FROM 
+( SELECT 100 a, subsel.b FROM ( SELECT 200 b ) subsel ) tmp
+WHERE tmp.b;
+a	b
+100	200
+SELECT * FROM 
+( SELECT 100 a, subsel.b FROM ( SELECT 200 b ) subsel ) tmp
+WHERE tmp.a;
+a	b
+100	200
 # End of 5.3 tests
 #
 # Bug#58730 Assertion failed: table->key_read == 0 in close_thread_table,
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index c0daabc148f..2b041448d3b 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -2234,7 +2234,7 @@ DROP VIEW v;
 DROP TABLE t1,t2;
 #
 # mdev-5105: memory overwrite in multi-table update 
-#            using natuaral join with a view
+#            using natural join with a view
 #
 create table t1(a int,b tinyint,c tinyint)engine=myisam;
 create table t2(a tinyint,b float,c int, d int, e int, f int, key (b), key(c), key(d), key(e), key(f))engine=myisam;
@@ -2244,6 +2244,19 @@ update t3 natural join v1 set a:=1;
 drop view v1;
 drop table t1,t2,t3;
 #
+# mdev-5288: assertion failure for query over a view with ORDER BY
+#
+CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (4,1);
+CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1;
+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
+DROP VIEW v1;
+DROP TABLE t1;
+#
 # end of 5.3 tests
 #
 set optimizer_switch=@exit_optimizer_switch;
diff --git a/mysql-test/r/dyncol.result b/mysql-test/r/dyncol.result
index 6014f5d0149..efe519fe3f9 100644
--- a/mysql-test/r/dyncol.result
+++ b/mysql-test/r/dyncol.result
@@ -1437,6 +1437,12 @@ DROP table t1;
 #
 # end of 5.3 tests
 #
+select column_get(column_create(1, "18446744073709552001" as char), 1 as int);
+column_get(column_create(1, "18446744073709552001" as char), 1 as int)
+-1
+Warnings:
+Warning	1918	Encountered illegal value '18446744073709552001' when converting to INT
+Note	1105	Cast to signed converted positive out-of-range integer to it's negative complement
 #
 # test of symbolic names
 #
diff --git a/mysql-test/r/explain_slowquerylog.result b/mysql-test/r/explain_slowquerylog.result
index 9d25cf06275..8acbdc69a4c 100644
--- a/mysql-test/r/explain_slowquerylog.result
+++ b/mysql-test/r/explain_slowquerylog.result
@@ -39,3 +39,15 @@ SELECT 'Server still alive?' as 'Yes';
 Yes
 Server still alive?
 DROP TABLE t1;
+#
+# MDEV-5308 Crash when running with slow_query_log=1
+#
+SET @save1= @@log_slow_rate_limit;
+SET @save2= @@long_query_time;
+SET log_slow_rate_limit=1000;
+SET long_query_time=0.000001;
+SELECT 1;
+1
+1
+SET log_slow_rate_limit=@save1;
+SET long_query_time=@save2;
diff --git a/mysql-test/r/func_equal.result b/mysql-test/r/func_equal.result
index 02593529a91..f20b259191c 100644
--- a/mysql-test/r/func_equal.result
+++ b/mysql-test/r/func_equal.result
@@ -43,46 +43,3 @@ a
 4828532208463511553
 drop table t1;
 #End of 4.1 tests
-#
-# MDEV-5103: server crashed on singular Item_equal
-#
-CREATE TABLE `t1` (
-`tipo` enum('p','r') NOT NULL DEFAULT 'r',
-`arquivo_id` bigint(20) unsigned NOT NULL DEFAULT '0',
-`arquivo_md5` char(32) NOT NULL,
-`conteudo` longblob NOT NULL,
-`usuario` varchar(15) NOT NULL,
-`datahora_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
-`tipo_arquivo` varchar(255) NOT NULL,
-`nome_arquivo` varchar(255) NOT NULL,
-`tamanho_arquivo` bigint(20) unsigned NOT NULL DEFAULT '0',
-PRIMARY KEY (`tipo`,`arquivo_id`),
-UNIQUE KEY `tipo` (`tipo`,`arquivo_md5`)
-) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
-INSERT INTO `t1` (`tipo`, `arquivo_id`, `arquivo_md5`, `conteudo`, `usuario`, `datahora_gmt`, `tipo_arquivo`, `nome_arquivo`, `tamanho_arquivo`) VALUES
-('r', 1, 'ad18832202b199728921807033a8a515', '', 'rspadim', '2013-10-05 13:55:50', '001_cbr643', 'CBR6431677410201314132.ret', 21306);
-CREATE TABLE `t2` (
-`tipo` enum('p','r') NOT NULL DEFAULT 'p',
-`arquivo_id` bigint(20) NOT NULL DEFAULT '0',
-`usuario` varchar(25) NOT NULL,
-`datahora` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
-`erros` longblob NOT NULL,
-`importados` bigint(20) unsigned NOT NULL DEFAULT '0',
-`n_importados` bigint(20) unsigned NOT NULL DEFAULT '0',
-PRIMARY KEY (`tipo`,`arquivo_id`,`datahora`)
-) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
-INSERT INTO `t2` (`tipo`, `arquivo_id`, `usuario`, `datahora`, `erros`, `importados`, `n_importados`) VALUES
-('r', 1, 'rspadim', '2013-10-05 14:25:30', '', 32, 0);
-SELECT
-arquivo_id,usuario,datahora_gmt,tipo_arquivo,nome_arquivo,tamanho_arquivo
-FROM t1 AS a
-WHERE datahora_gmt>='0000-00-00 00:00:00' AND
-datahora_gmt<='2013-10-07 02:59:59' AND tipo='r' AND
-(tipo_arquivo,arquivo_id) NOT IN
-(SELECT tipo_arquivo,arquivo_id
-FROM t2
-WHERE (tipo_arquivo,arquivo_id)=(a.tipo_arquivo,a.arquivo_id))
-ORDER BY arquivo_id DESC;
-arquivo_id	usuario	datahora_gmt	tipo_arquivo	nome_arquivo	tamanho_arquivo
-drop table t2, t1;
-#End of 5.3 tests
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index ee97371d344..4bd11aa2fc4 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -365,7 +365,7 @@ extract(DAY_MINUTE FROM "02 10:11:12")
 21011
 select extract(DAY_SECOND FROM "225 10:11:12");
 extract(DAY_SECOND FROM "225 10:11:12")
-8385959
+34225959
 Warnings:
 Warning	1292	Truncated incorrect time value: '225 10:11:12'
 select extract(HOUR FROM "1999-01-02 10:11:12");
@@ -948,10 +948,10 @@ sec_to_time(1) + 0, from_unixtime(1) + 0;
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `now() - now()` decimal(20,0) NOT NULL DEFAULT '0',
-  `curtime() - curtime()` decimal(11,0) NOT NULL DEFAULT '0',
-  `sec_to_time(1) + 0` decimal(11,0) DEFAULT NULL,
-  `from_unixtime(1) + 0` decimal(20,0) DEFAULT NULL
+  `now() - now()` bigint(21) NOT NULL DEFAULT '0',
+  `curtime() - curtime()` bigint(12) NOT NULL DEFAULT '0',
+  `sec_to_time(1) + 0` bigint(12) DEFAULT NULL,
+  `from_unixtime(1) + 0` bigint(21) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 SELECT SEC_TO_TIME(3300000);
@@ -1025,7 +1025,7 @@ Note	1105	Cast to unsigned converted negative integer to it's positive complemen
 Warning	1292	Truncated incorrect time value: '18446744073709551615:00:00'
 SELECT EXTRACT(HOUR FROM '10000:02:03');
 EXTRACT(HOUR FROM '10000:02:03')
-838
+22
 Warnings:
 Warning	1292	Truncated incorrect time value: '10000:02:03'
 CREATE TABLE t1(f1 TIME);
@@ -2347,3 +2347,30 @@ DROP TABLE t1;
 SELECT MAKETIME(0, 0, -0.1);
 MAKETIME(0, 0, -0.1)
 NULL
+#
+# MDEV-4857 Wrong result of HOUR('1 00:00:00')
+#
+SELECT HOUR('1 02:00:00'), HOUR('26:00:00');
+HOUR('1 02:00:00')	HOUR('26:00:00')
+26	26
+SELECT HOUR(TIME'1 02:00:00'), HOUR(TIME'26:00:00');
+HOUR(TIME'1 02:00:00')	HOUR(TIME'26:00:00')
+26	26
+SELECT HOUR(TIME('1 02:00:00')), HOUR(TIME('26:00:00'));
+HOUR(TIME('1 02:00:00'))	HOUR(TIME('26:00:00'))
+26	26
+SELECT DAY(TIME('1 02:00:00')), DAY(TIME('26:00:00'));
+DAY(TIME('1 02:00:00'))	DAY(TIME('26:00:00'))
+0	0
+SELECT EXTRACT(HOUR FROM '1 02:00:00'), EXTRACT(HOUR FROM '26:00:00');
+EXTRACT(HOUR FROM '1 02:00:00')	EXTRACT(HOUR FROM '26:00:00')
+2	2
+SELECT EXTRACT(HOUR FROM TIME'1 02:00:00'), EXTRACT(HOUR FROM TIME'26:00:00');
+EXTRACT(HOUR FROM TIME'1 02:00:00')	EXTRACT(HOUR FROM TIME'26:00:00')
+2	2
+SELECT EXTRACT(HOUR FROM TIME('1 02:00:00')), EXTRACT(HOUR FROM TIME('26:00:00'));
+EXTRACT(HOUR FROM TIME('1 02:00:00'))	EXTRACT(HOUR FROM TIME('26:00:00'))
+2	2
+SELECT EXTRACT(DAY FROM TIME('1 02:00:00')), EXTRACT(DAY FROM TIME('26:00:00'));
+EXTRACT(DAY FROM TIME('1 02:00:00'))	EXTRACT(DAY FROM TIME('26:00:00'))
+1	1
diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result
index 1db3eb50bd2..2fa2d5bcce1 100644
--- a/mysql-test/r/index_merge_myisam.result
+++ b/mysql-test/r/index_merge_myisam.result
@@ -1645,4 +1645,64 @@ AND (population_rate = 25 OR area_rate BETWEEN 2 AND 25 OR code BETWEEN 'MA' AND
 pk	code	population_rate	area_rate
 1	WI	20	23
 DROP TABLE t1;
+#
+# MDEV-5069: Server crashes in SEL_ARG::increment_use_count with index_merge+index_merge_sort_union, FORCE INDEX
+#
+CREATE TABLE t1 (
+c1 varchar(64),
+i int,
+pk integer auto_increment,
+c2 varchar(64),
+index (c1),
+index (i),
+primary key (pk),
+key (c2)) ENGINE=myisam;
+ALTER TABLE t1 DISABLE KEYS;
+INSERT INTO t1 VALUES 
+('West Virginia', 6121, NULL, 'California'),('Georgia', 60177, NULL, 'Arkansas'),
+('Delaware', 90, NULL, 'Oregon'),('Wyoming', 7, NULL, 'Missouri'),(
+'Delaware', 2, NULL, 'Utah'),('Wisconsin', 0, NULL, 'Iowa'),
+('Kansas', 0, NULL, 'Florida'),('Ohio', 34358, NULL, 'Colorado'),
+('Maine', 118, NULL, 'Texas'),('Mississippi', 0, NULL, 'Georgia'),
+('Tennessee', 4, NULL, 'N/A'),('Georgia', 0, NULL, 'New Hampshire'),
+('Wyoming', 2, NULL, 'N/A'),('Florida', 0, NULL, 'Arizona'),
+('Rhode Island', -24002, NULL, 'Michigan'),('Alabama', 142, NULL, 'Indiana'),
+('Colorado', 0, NULL, 'Louisiana'),('Michigan', 21194, NULL, 'Louisiana'),
+('Oklahoma', 31475, NULL, 'Alabama'),('Pennsylvania', 0, NULL, 'Oklahoma'),
+('Texas', 0, NULL, 'Texas'),('West Virginia', 5, NULL, 'Utah'),
+('Florida', 49653, NULL, 'Kentucky'),('Tennessee', 19075, NULL, 'Oregon'),
+('Maine', 3, NULL, 'Kansas, Kentucky, Iowa'),('Iowa', 1, NULL, 'South Dakota'),
+('Kansas', -4037, NULL, 'Virginia'),('Delaware', 22550, NULL, 'Utah'),
+('Illinois', 14634, NULL, 'South Carolina, Colorado'),
+('Kansas', 6, NULL, 'South Dakota'),('Delaware', 9, NULL, ''),
+('', 0, NULL, 'Utah, Delaware, Florida, Georgia, Nevada'),
+('Colorado', 8, NULL, 'Montana'),('Maryland', 2689, NULL, 'Hawaii'),
+('Florida', -12306, NULL, 'Delaware'),
+('Indiana', 38567, NULL, 'Iowa, Minnesota, Maine'),
+('Oklahoma', 9, NULL, 'Delaware, Kansas, Oregon, Nebraska, Maryland, Minnesota'),
+('Tennessee', 12460, NULL, NULL),('Kentucky', 0, NULL, 'Ohio'),
+('Nevada', 7, NULL, 'Vermont, Oregon, Oklahoma, Montana'),
+('Nebraska', 61966, NULL, 'Nevada'),('Alaska', 131, NULL, 'Louisiana, Maine'),
+('Wisconsin', 4, NULL, 'Nevada'),('South Carolina', 0, NULL, 'Washington'),
+('West Virginia', 51314, NULL, 'Ohio'),('Louisiana', 0, NULL, ''),
+('Pennsylvania', 0, NULL, 'Iowa, Idaho'),('Arkansas', 14010, NULL, 'Indiana'),
+('Wyoming', -15514, NULL, 'Maine'),('Georgia', 0, NULL, 'N/A'),
+('Kentucky', 1, NULL, 'Idaho'),('Wyoming', 60249, NULL, 'Indiana, Iowa'),
+('Pennsylvania', 69, NULL, 'W'),
+('New Mexico', 11480, NULL, 'Florida, Georgia, Hawaii'),
+('South Carolina', 9, NULL, 'Iowa'),('Virginia', 0, NULL, 'Connecticut'),
+('Mississippi', 19749, NULL, 'Rhode Island'),('Illinois', 5, NULL, 'Virginia'),
+('Texas', -1749, NULL, 'Tennessee'),('Arizona', 28, NULL, 'California'),
+('Florida', 62151, NULL, 'Kansas'),('California', 172, NULL, 'SC'),
+('New Jersey', 0, NULL, 'North Carolina'),('Wyoming', 4, NULL, 'I'),
+('Kansas', 10683, NULL, 'California'),('Arkansas', -14275, NULL, 'K'),
+('Arizona', 5, NULL, 'California, Delaware, Rhode Island, Maryland'),
+('Florida', 0, NULL, 'T'),('Alaska', 241, NULL, 'Virginia');
+ALTER TABLE t1 ENABLE KEYS;
+EXPLAIN
+SELECT  * FROM t1 FORCE KEY (PRIMARY , i , c1 , c2) 
+WHERE pk = 255 OR i = 22 OR (pk  IN (1 , 136) AND c2  IN ('c' , 'w') AND (c1 NOT BETWEEN 'e' AND 'i' OR  c2 > 'g'))  OR pk != 1  ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	PRIMARY,c1,i,c2	NULL	NULL	NULL	69	Using where
+DROP TABLE t1;
 set optimizer_switch= @optimizer_switch_save;
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index 476f3e92caa..7ca28c28b1e 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -5637,6 +5637,25 @@ c
 set join_buffer_size=default;
 set optimizer_switch=@tmp_optimizer_switch;
 DROP table t1,t2,t3;
+set join_buffer_size= default;
+set @@optimizer_switch=@save_optimizer_switch;
+#
+# MDEV-5293: outer join, join buffering, and order by - invalid query plan
+#
+create table t0 (a int primary key) engine=myisam;
+insert into t0 values (1);
+create table t1(a int) engine=myisam;
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+alter table t1 add b int;
+create table t2 like t1;
+insert into t2 select * from t1;
+#The following must use "Using temporary; Using filesort" and not just "Using filesort":
+explain select * from t0,t1 left join t2 on t1.b=t2.b order by t0.a, t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	system	NULL	NULL	NULL	NULL	1	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
+drop table t0,t1,t2;
 #
 # MDEV-5123 Remove duplicated conditions pushed both to join_tab->select_cond and join_tab->cache_select->cond for blocked joins.
 #
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index c9b694c9532..4a806e0831c 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -2054,7 +2054,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	const	PRIMARY,idx	PRIMARY	4	const	1	100.00	
 1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
 Warnings:
-Note	1003	select 5 AS `pk` from `test`.`t2` join `test`.`t1` where ((((1 between 5 and 6) and isnull(5)) or 1)) order by 5
+Note	1003	select 5 AS `pk` from `test`.`t2` join `test`.`t1` where 1 order by 5
 SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a
 WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
 ORDER BY t1.pk;
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index eae21a090c4..6a543f920e4 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -2065,7 +2065,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	const	PRIMARY,idx	PRIMARY	4	const	1	100.00	
 1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
 Warnings:
-Note	1003	select 5 AS `pk` from `test`.`t2` join `test`.`t1` where ((((1 between 5 and 6) and isnull(5)) or 1)) order by 5
+Note	1003	select 5 AS `pk` from `test`.`t2` join `test`.`t1` where 1 order by 5
 SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a
 WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
 ORDER BY t1.pk;
diff --git a/mysql-test/r/mdl_sync.result b/mysql-test/r/mdl_sync.result
index d71498f90dd..990f074175b 100644
--- a/mysql-test/r/mdl_sync.result
+++ b/mysql-test/r/mdl_sync.result
@@ -1977,15 +1977,11 @@ rename table t2 to t0, t1 to t2, t0 to t1;;
 # for 'deadlock_con1' which holds shared metadata lock on 't2'.
 # 
 # The below statement should not wait as doing so will cause deadlock.
-# Instead it should fail and emit ER_LOCK_DEADLOCK statement.
+# Instead it should fail and emit ER_LOCK_DEADLOCK statement and 
+# transaction should be rolled back.
 select * from t1;
 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
 #
-# Let us check that failure of the above statement has not released
-# metadata lock on table 't1', i.e. that RENAME TABLE is still blocked.
-# Commit transaction to unblock RENAME TABLE.
-commit;
-#
 # Switching to connection 'default'.
 # Reap RENAME TABLE.
 #
@@ -2022,16 +2018,10 @@ unlock tables;
 # Switching to connection 'deadlock_con1'.
 # Since the latest RENAME TABLE entered in deadlock with SELECT
 # statement the latter should be aborted and emit ER_LOCK_DEADLOCK
-# error.
+# error and transaction should be rolled back.
 # Reap SELECT * FROM t1.
 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
 #
-# Again let us check that failure of the SELECT statement has not
-# released metadata lock on table 't2', i.e. that the latest RENAME
-# is blocked.
-# Commit transaction to unblock this RENAME TABLE.
-commit;
-#
 # Switching to connection 'deadlock_con2'.
 # Reap RENAME TABLE ... .
 #
@@ -2065,14 +2055,10 @@ alter table t1 add column j int, rename to t2;;
 # metadata lock on 't2' and starts waiting for connection
 # 'deadlock_con1' which holds shared lock on 't1'.
 # The below statement should not wait as it will cause deadlock.
-# An appropriate error should be reported instead.
+# An appropriate error should be reported instead and transaction
+# should be rolled back.
 select * from t2;
 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
-# Again let us check that failure of the above statement has not
-# released all metadata locks in connection 'deadlock_con1' and
-# so ALTER TABLE ... RENAME is still blocked.
-# Commit transaction to unblock ALTER TABLE ... RENAME.
-commit;
 #
 # Switching to connection 'default'.
 # Reap ALTER TABLE ... RENAME.
@@ -2581,12 +2567,6 @@ set debug_sync='mdl_acquire_lock_wait SIGNAL alter_go';
 update t1 set c3=c3+1 where c2 = 3;
 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
 #
-# Let us check that failure of the above statement has not released
-# metadata lock on table 't1', i.e. that ALTER TABLE is still blocked.
-# Unblock ALTER TABLE by commiting transaction and thus releasing
-# metadata lock on 't1'.
-commit;
-#
 # Switching to connection 'con46273'.
 # Reap ALTER TABLE.
 #
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index f806829b969..0feb1cdce98 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -2394,6 +2394,13 @@ REPAIR TABLE m1;
 Table	Op	Msg_type	Msg_text
 test.m1	repair	note	The storage engine for the table doesn't support repair
 DROP TABLE m1, t1;
+create temporary table t1_temp(i int);
+create temporary table tm_temp_temp (i int) engine=merge union=(t1_temp) insert_method=last;
+alter table tm_temp_temp insert_method=first;
+check table tm_temp_temp;
+Table	Op	Msg_type	Msg_text
+test.tm_temp_temp	check	status	OK
+drop temporary table t1_temp, tm_temp_temp;
 End of 5.1 tests
 # 
 # MDEV-4277: Crash inside mi_killed_in_mariadb() with myisammrg
diff --git a/mysql-test/r/mysql_tzinfo_to_sql_symlink.result b/mysql-test/r/mysql_tzinfo_to_sql_symlink.result
new file mode 100644
index 00000000000..fce61687c83
--- /dev/null
+++ b/mysql-test/r/mysql_tzinfo_to_sql_symlink.result
@@ -0,0 +1,22 @@
+#
+# MDEV-5226 mysql_tzinfo_to_sql errors with tzdata 2013f and above
+#
+TRUNCATE TABLE time_zone;
+TRUNCATE TABLE time_zone_name;
+TRUNCATE TABLE time_zone_transition;
+TRUNCATE TABLE time_zone_transition_type;
+INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
+SET @time_zone_id= LAST_INSERT_ID();
+INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('GMT', @time_zone_id);
+INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
+ (@time_zone_id, 0, 0, 0, 'GMT')
+;
+INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
+SET @time_zone_id= LAST_INSERT_ID();
+INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('posix/GMT', @time_zone_id);
+INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
+ (@time_zone_id, 0, 0, 0, 'GMT')
+;
+Warning: Skipping directory 'MYSQLTEST_VARDIR/zoneinfo/posix/posix': to avoid infinite symlink recursion.
+ALTER TABLE time_zone_transition ORDER BY Time_zone_id, Transition_time;
+ALTER TABLE time_zone_transition_type ORDER BY Time_zone_id, Transition_type_id;
diff --git a/mysql-test/r/plugin_auth.result b/mysql-test/r/plugin_auth.result
index 9a1dd3c43c1..3a55055acce 100644
--- a/mysql-test/r/plugin_auth.result
+++ b/mysql-test/r/plugin_auth.result
@@ -12,10 +12,8 @@ test_plugin_server	plug_dest
 ERROR 28000: Access denied for user 'plug'@'localhost' (using password: YES)
 GRANT PROXY ON plug_dest TO plug;
 test proxies_priv columns
-SELECT * FROM mysql.proxies_priv;
+SELECT * FROM mysql.proxies_priv WHERE user !='root';
 Host	User	Proxied_host	Proxied_user	With_grant	Grantor	Timestamp
-xx	root			1		xx
-xx	root			1		xx
 xx	plug	%	plug_dest	0	root@localhost	xx
 test mysql.proxies_priv;
 SHOW CREATE TABLE mysql.proxies_priv;
diff --git a/mysql-test/r/plugin_auth_qa_2.result b/mysql-test/r/plugin_auth_qa_2.result
index aa3a59b4f25..fa88530be6a 100644
--- a/mysql-test/r/plugin_auth_qa_2.result
+++ b/mysql-test/r/plugin_auth_qa_2.result
@@ -96,49 +96,31 @@ CREATE USER qa_test_6_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_6_dest'
 CREATE USER qa_test_6_dest IDENTIFIED BY 'dest_passwd';
 GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_6_dest identified by 'dest_passwd';
 GRANT PROXY ON qa_test_6_dest TO qa_test_6_user;
-SELECT user,plugin,authentication_string,password FROM mysql.user;
+SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root';
 user	plugin	authentication_string	password
 qa_test_6_dest			*DFCACE76914AD7BD801FC1A1ECF6562272621A22
 qa_test_6_user	qa_auth_interface	qa_test_6_dest	
-root			
-root			
-root			
-root			
 exec MYSQL -h localhost -P MASTER_MYPORT --user=qa_test_6_user  --password=qa_test_6_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
 ERROR 1045 (28000): Access denied for user 'qa_test_6_user'@'localhost' (using password: YES)
 GRANT PROXY ON qa_test_6_dest TO root IDENTIFIED WITH qa_auth_interface AS 'qa_test_6_dest';
-SELECT user,plugin,authentication_string,password FROM mysql.user;
+SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root';
 user	plugin	authentication_string	password
 qa_test_6_dest			*DFCACE76914AD7BD801FC1A1ECF6562272621A22
 qa_test_6_user	qa_auth_interface	qa_test_6_dest	
-root			
-root			
-root			
-root			
-root	qa_auth_interface	qa_test_6_dest	
 exec MYSQL -h localhost -P MASTER_MYPORT --user=root  --password=qa_test_6_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
 REVOKE PROXY ON qa_test_6_dest FROM root;
-SELECT user,plugin,authentication_string FROM mysql.user;
+SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
 user	plugin	authentication_string
 qa_test_6_dest		
 qa_test_6_user	qa_auth_interface	qa_test_6_dest
-root		
-root		
-root		
-root		
-root	qa_auth_interface	qa_test_6_dest
 exec MYSQL -h localhost -P MASTER_MYPORT --user=root  --password=qa_test_6_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
 DROP USER qa_test_6_user;
 DROP USER qa_test_6_dest;
 DELETE FROM mysql.user WHERE user='root' AND plugin='qa_auth_interface';
-SELECT user,plugin,authentication_string,password FROM mysql.user;
+SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root';
 user	plugin	authentication_string	password
-root			
-root			
-root			
-root			
 === Test of the --default_auth option for clients ====
 CREATE USER qa_test_11_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_11_dest';
 CREATE USER qa_test_11_dest IDENTIFIED BY 'dest_passwd';
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 50fb5e1e55b..bfec91192a9 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -3996,4 +3996,27 @@ Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
 deallocate prepare st;
 drop table t1;
+#
+# Bug mdev-5410: crash at the execution of PS with subselect
+#                formed by UNION with global ORDER BY
+#
+CREATE TABLE t1 (a int DEFAULT NULL);
+INSERT INTO t1 VALUES (2), (4);
+CREATE TABLE t2 (b int DEFAULT NULL);
+INSERT INTO t2 VALUES (1), (3);
+PREPARE stmt FROM "
+SELECT c1 FROM (SELECT (SELECT a FROM t1 WHERE t1.a <= t2.b
+                        UNION ALL
+                        SELECT a FROM t1 WHERE t1.a+3<= t2.b
+                        ORDER BY a DESC) AS c1 FROM t2) t3;
+";
+EXECUTE stmt;
+c1
+NULL
+2
+EXECUTE stmt;
+c1
+NULL
+2
+DROP TABLE t1,t2;
 # End of 5.3 tests
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 6d3d9ce555d..005dd52c449 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -5321,7 +5321,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 <> 0)
 DROP TABLE t1;
 SELECT * FROM mysql.time_zone 
 WHERE ( NOT (Use_leap_seconds <= Use_leap_seconds AND Time_zone_id != 1) 
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index c4f071aedb0..0113791b799 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -5332,7 +5332,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 <> 0)
 DROP TABLE t1;
 SELECT * FROM mysql.time_zone 
 WHERE ( NOT (Use_leap_seconds <= Use_leap_seconds AND Time_zone_id != 1) 
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index 6d3d9ce555d..005dd52c449 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -5321,7 +5321,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 <> 0)
 DROP TABLE t1;
 SELECT * FROM mysql.time_zone 
 WHERE ( NOT (Use_leap_seconds <= Use_leap_seconds AND Time_zone_id != 1) 
diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index ac8771bd90a..3704812eb04 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -1129,6 +1129,43 @@ set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivit
 drop table t1;
 set use_stat_tables=@save_use_stat_tables;
 #
+# Bug mdev-5200: impossible where with a semijoin subquery   
+#                when optimizer_use_condition_selectivity=2
+#
+set use_stat_tables = 'preferably';
+set optimizer_use_condition_selectivity = 2;
+CREATE TABLE t1 (i1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (0), (1);
+CREATE TABLE t2 (pk2 int, i2 int, c2 char(1), PRIMARY KEY(pk2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,8,'m'), (2,9,'b');
+CREATE TABLE t3 (c3 char(1), INDEX(c3)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('v'), ('c');
+ANALYZE TABLE t1,t2,t3;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	OK
+test.t3	analyze	status	OK
+SELECT * FROM t1
+WHERE 2 IN ( SELECT pk2 FROM t2 LEFT JOIN t3 ON (c3 = c2 ) WHERE i2 = 3 );
+i1
+EXPLAIN EXTENDED
+SELECT * FROM t1
+WHERE 2 IN ( SELECT pk2 FROM t2 LEFT JOIN t3 ON (c3 = c2 ) WHERE i2 = 3 );
+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	1003	select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` left join `test`.`t3` on((`test`.`t3`.`c3` = 'b'))) where 0
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+DROP TABLE t1,t2,t3;
+#
+# Bug mdev-5415: query over an information schema table   
+#                when optimizer_use_condition_selectivity=3
+#
+set optimizer_use_condition_selectivity = 3;
+SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE SQL_MODE != '';
+TRIGGER_CATALOG	TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_MANIPULATION	EVENT_OBJECT_CATALOG	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_ORDER	ACTION_CONDITION	ACTION_STATEMENT	ACTION_ORIENTATION	ACTION_TIMING	ACTION_REFERENCE_OLD_TABLE	ACTION_REFERENCE_NEW_TABLE	ACTION_REFERENCE_OLD_ROW	ACTION_REFERENCE_NEW_ROW	CREATED	SQL_MODE	DEFINER	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+#
 # Bug mdev-4429: join with  range condition whose selectivity == 0   
 #                when optimizer_use_condition_selectivity=3
 #
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 9bd92d7c7c8..a3b6049c4d0 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -1137,6 +1137,43 @@ set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivit
 drop table t1;
 set use_stat_tables=@save_use_stat_tables;
 #
+# Bug mdev-5200: impossible where with a semijoin subquery   
+#                when optimizer_use_condition_selectivity=2
+#
+set use_stat_tables = 'preferably';
+set optimizer_use_condition_selectivity = 2;
+CREATE TABLE t1 (i1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (0), (1);
+CREATE TABLE t2 (pk2 int, i2 int, c2 char(1), PRIMARY KEY(pk2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,8,'m'), (2,9,'b');
+CREATE TABLE t3 (c3 char(1), INDEX(c3)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('v'), ('c');
+ANALYZE TABLE t1,t2,t3;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	OK
+test.t3	analyze	status	OK
+SELECT * FROM t1
+WHERE 2 IN ( SELECT pk2 FROM t2 LEFT JOIN t3 ON (c3 = c2 ) WHERE i2 = 3 );
+i1
+EXPLAIN EXTENDED
+SELECT * FROM t1
+WHERE 2 IN ( SELECT pk2 FROM t2 LEFT JOIN t3 ON (c3 = c2 ) WHERE i2 = 3 );
+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	1003	select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` left join `test`.`t3` on((`test`.`t3`.`c3` = 'b'))) where 0
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+DROP TABLE t1,t2,t3;
+#
+# Bug mdev-5415: query over an information schema table   
+#                when optimizer_use_condition_selectivity=3
+#
+set optimizer_use_condition_selectivity = 3;
+SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE SQL_MODE != '';
+TRIGGER_CATALOG	TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_MANIPULATION	EVENT_OBJECT_CATALOG	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_ORDER	ACTION_CONDITION	ACTION_STATEMENT	ACTION_ORIENTATION	ACTION_TIMING	ACTION_REFERENCE_OLD_TABLE	ACTION_REFERENCE_NEW_TABLE	ACTION_REFERENCE_OLD_ROW	ACTION_REFERENCE_NEW_ROW	CREATED	SQL_MODE	DEFINER	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+#
 # Bug mdev-4429: join with  range condition whose selectivity == 0   
 #                when optimizer_use_condition_selectivity=3
 #
diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result
index 6905725a6a2..63c65e23c19 100644
--- a/mysql-test/r/stat_tables.result
+++ b/mysql-test/r/stat_tables.result
@@ -381,3 +381,39 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
 DROP TABLE t1;
 set use_stat_tables=@save_use_stat_tables;
+#
+# Bug mdev-5204: invalid impossible where after reading const tables  
+#                when use_stat_tables = 'preferably'
+#
+set use_stat_tables = 'preferably';
+CREATE TABLE t1 (id int PRIMARY KEY) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+CREATE TABLE t2 (name char(3)) ENGINE=MyISAM;
+ANALYZE TABLE t2;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	Table is already up to date
+INSERT INTO t2 VALUES ('USA'),('AUS');
+SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
+id	name
+1	AUS
+EXPLAIN
+SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	0	Using where
+ANALYZE TABLE t2;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	OK
+SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
+id	name
+1	AUS
+EXPLAIN
+SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+DROP TABLE t1,t2;
+set use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result
index 51e1567309b..c601271c4d0 100644
--- a/mysql-test/r/stat_tables_innodb.result
+++ b/mysql-test/r/stat_tables_innodb.result
@@ -408,5 +408,41 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
 DROP TABLE t1;
 set use_stat_tables=@save_use_stat_tables;
+#
+# Bug mdev-5204: invalid impossible where after reading const tables  
+#                when use_stat_tables = 'preferably'
+#
+set use_stat_tables = 'preferably';
+CREATE TABLE t1 (id int PRIMARY KEY) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+CREATE TABLE t2 (name char(3)) ENGINE=MyISAM;
+ANALYZE TABLE t2;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	Table is already up to date
+INSERT INTO t2 VALUES ('USA'),('AUS');
+SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
+id	name
+1	AUS
+EXPLAIN
+SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	0	Using where
+ANALYZE TABLE t2;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	OK
+SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
+id	name
+1	AUS
+EXPLAIN
+SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+DROP TABLE t1,t2;
+set use_stat_tables=@save_use_stat_tables;
 set optimizer_switch=@save_optimizer_switch_for_stat_tables_test;
 SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 3ed473c43f3..fc98607e38a 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 = '1')
 drop table t1;
 CREATE TABLE t1 (a int(1));
 INSERT INTO t1 VALUES (1);
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index b096d20c331..2c826cc9172 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2282,6 +2282,28 @@ d	NULL	NULL	NULL
 drop table t1,t2;
 set @@optimizer_switch = @optimizer_switch_MDEV4056;
 #
+# MDEV-5103: server crashed on singular Item_equal
+#
+CREATE TABLE t1 (
+a enum('p','r') NOT NULL DEFAULT 'r',
+b int NOT NULL DEFAULT '0',
+c char(32) NOT NULL,
+d varchar(255) NOT NULL,
+PRIMARY KEY (a, b), UNIQUE KEY idx(a, c)
+);
+INSERT INTO t1 VALUES ('r', 1, 'ad18832202b199728921807033a8a515', '001_cbr643');
+CREATE TABLE t2 (
+a enum('p','r') NOT NULL DEFAULT 'r',
+b int NOT NULL DEFAULT '0',
+e datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+PRIMARY KEY (a, b, e)
+);
+INSERT INTO t2 VALUES ('r', 1, '2013-10-05 14:25:30');
+SELECT * FROM t1 AS t 
+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-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 17a86843965..a70e6df3d00 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 = '1')
 drop table t1;
 CREATE TABLE t1 (a int(1));
 INSERT INTO t1 VALUES (1);
diff --git a/mysql-test/r/subselect_extra.result b/mysql-test/r/subselect_extra.result
index b3507c97086..5849ccda631 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 ('2007-04-25 18:30:22' = 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 ('2007-04-25' = 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 831f70fcd35..16f67820e0e 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 = '1')
 drop table t1;
 CREATE TABLE t1 (a int(1));
 INSERT INTO t1 VALUES (1);
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 649fa4eee0b..bc2cc71b0fa 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 = '1')
 drop table t1;
 CREATE TABLE t1 (a int(1));
 INSERT INTO t1 VALUES (1);
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 088445d9691..40a63afb1c6 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 = '1')
 drop table t1;
 CREATE TABLE t1 (a int(1));
 INSERT INTO t1 VALUES (1);
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 6ac0f7a4754..1fc2d98c0e8 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 = '1')
 drop table t1;
 CREATE TABLE t1 (a int(1));
 INSERT INTO t1 VALUES (1);
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index b8040731cb5..873f8eb662e 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -2920,4 +2920,54 @@ COUNT(*)
 DROP TABLE t1, t2, t3;
 set join_buffer_size = @tmp_join_buffer_size;
 set max_heap_table_size = @tmp_max_heap_table_size;
+#
+# MDEV-5161: Wrong result (missing rows) with semijoin, LEFT JOIN, ORDER BY, constant table
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY, c1 VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'v'),(2,'v'),(3,'c'),(4,NULL),(5,'x');
+CREATE TABLE t2 (c2 VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('x');
+CREATE TABLE t3 (c3 VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('x'),('d');
+SELECT * FROM t1, t2 WHERE pk IN ( SELECT pk FROM t1 LEFT JOIN t3 ON (c1 = c3 ) ) ORDER BY c2, c1;
+pk	c1	c2
+4	NULL	x
+3	c	x
+1	v	x
+2	v	x
+5	x	x
+# This should show that "t1 left join t3" is still in the semi-join nest:
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2 WHERE pk IN ( SELECT pk FROM t1 LEFT JOIN t3 ON (c1 = c3 ) ) ORDER BY c2, c1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	100.00	Using temporary; Using filesort
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	5	100.00	Start temporary
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t1.pk	1	100.00	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; End temporary
+Warnings:
+Note	1003	select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1` AS `c1`,'x' AS `c2` from `test`.`t1` semi join (`test`.`t1` left join `test`.`t3` on((`test`.`t1`.`c1` = `test`.`t3`.`c3`))) where (`test`.`t1`.`pk` = `test`.`t1`.`pk`) order by 'x',`test`.`t1`.`c1`
+DROP TABLE t1,t2,t3;
+# 
+# MDEV-5059: Wrong result (missing row) wih semijoin, join_cache_level > 2, LEFT JOIN, ORDER BY
+# 
+SET @tmp_mdev5059=@@join_cache_level;
+SET join_cache_level = 3;
+set @tmp_os_mdev5059= @@optimizer_switch;
+set optimizer_switch=default;
+CREATE TABLE t1 (pk1 INT PRIMARY KEY, i1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,4),(2,5);
+CREATE TABLE t2 (i2 INT, c2 CHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,'v'),(7,'s');
+CREATE TABLE t3 (pk3 INT PRIMARY KEY, i3 INT, c3 CHAR(1), INDEX(i3), INDEX(c3,i3)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (1,7,'g'),(2,4,'p'),(3,1,'q');
+CREATE TABLE t4 (i4 INT) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (1);
+SELECT * FROM t1, t2 LEFT JOIN t3 ON (c3 = c2) 
+WHERE ( i2, pk1 )  IN ( SELECT i3, 1 FROM t3, t4 ) ORDER BY i1, c2;
+pk1	i1	i2	c2	pk3	i3	c3
+1	4	7	s	NULL	NULL	NULL
+1	4	1	v	NULL	NULL	NULL
+SET join_cache_level=@tmp_mdev5059;
+set optimizer_switch=@tmp_os_mdev5059;
+DROP TABLE t1,t2,t3,t4;
 set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index a42fc1efaad..16e30253ea9 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -2934,6 +2934,56 @@ COUNT(*)
 DROP TABLE t1, t2, t3;
 set join_buffer_size = @tmp_join_buffer_size;
 set max_heap_table_size = @tmp_max_heap_table_size;
+#
+# MDEV-5161: Wrong result (missing rows) with semijoin, LEFT JOIN, ORDER BY, constant table
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY, c1 VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'v'),(2,'v'),(3,'c'),(4,NULL),(5,'x');
+CREATE TABLE t2 (c2 VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('x');
+CREATE TABLE t3 (c3 VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('x'),('d');
+SELECT * FROM t1, t2 WHERE pk IN ( SELECT pk FROM t1 LEFT JOIN t3 ON (c1 = c3 ) ) ORDER BY c2, c1;
+pk	c1	c2
+4	NULL	x
+3	c	x
+1	v	x
+2	v	x
+5	x	x
+# This should show that "t1 left join t3" is still in the semi-join nest:
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2 WHERE pk IN ( SELECT pk FROM t1 LEFT JOIN t3 ON (c1 = c3 ) ) ORDER BY c2, c1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	100.00	Using temporary; Using filesort
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	5	100.00	Start temporary
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t1.pk	1	100.00	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; End temporary; Using join buffer (incremental, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1` AS `c1`,'x' AS `c2` from `test`.`t1` semi join (`test`.`t1` left join `test`.`t3` on((`test`.`t1`.`c1` = `test`.`t3`.`c3`))) where (`test`.`t1`.`pk` = `test`.`t1`.`pk`) order by 'x',`test`.`t1`.`c1`
+DROP TABLE t1,t2,t3;
+# 
+# MDEV-5059: Wrong result (missing row) wih semijoin, join_cache_level > 2, LEFT JOIN, ORDER BY
+# 
+SET @tmp_mdev5059=@@join_cache_level;
+SET join_cache_level = 3;
+set @tmp_os_mdev5059= @@optimizer_switch;
+set optimizer_switch=default;
+CREATE TABLE t1 (pk1 INT PRIMARY KEY, i1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,4),(2,5);
+CREATE TABLE t2 (i2 INT, c2 CHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,'v'),(7,'s');
+CREATE TABLE t3 (pk3 INT PRIMARY KEY, i3 INT, c3 CHAR(1), INDEX(i3), INDEX(c3,i3)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (1,7,'g'),(2,4,'p'),(3,1,'q');
+CREATE TABLE t4 (i4 INT) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (1);
+SELECT * FROM t1, t2 LEFT JOIN t3 ON (c3 = c2) 
+WHERE ( i2, pk1 )  IN ( SELECT i3, 1 FROM t3, t4 ) ORDER BY i1, c2;
+pk1	i1	i2	c2	pk3	i3	c3
+1	4	7	s	NULL	NULL	NULL
+1	4	1	v	NULL	NULL	NULL
+SET join_cache_level=@tmp_mdev5059;
+set optimizer_switch=@tmp_os_mdev5059;
+DROP TABLE t1,t2,t3,t4;
 set optimizer_switch=@subselect_sj_tmp;
 #
 # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off
diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result
index 59054607acb..ff488fea427 100644
--- a/mysql-test/r/table_elim.result
+++ b/mysql-test/r/table_elim.result
@@ -610,6 +610,34 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 drop view v1;
 DROP TABLE t1,t2,t3;
 #
+# MDEV-5344: LEFT OUTER JOIN table data is lost in ON DUPLICATE KEY UPDATE section
+#
+create table t1 (
+id int(10) unsigned NOT NULL DEFAULT '0',
+v int(10) unsigned DEFAULT '0',
+PRIMARY KEY (id)
+);
+create table t2 (
+id int(10) unsigned NOT NULL DEFAULT '0',
+PRIMARY KEY (id)
+) ;
+create table t3 (
+id int(10) unsigned NOT NULL DEFAULT '0',
+v int(10) unsigned DEFAULT '0',
+PRIMARY KEY (id)
+);
+insert into t1 values (1, 10), (2, 10);
+insert into t2 values (1), (2);
+insert into t3 values (1, 20);
+insert into t1 
+select t2.id, 5 from t2 LEFT OUTER JOIN t3 ON t2.id = t3.id
+on duplicate key update t1.v = t3.v;
+select * from t1;
+id	v
+1	20
+2	NULL
+drop table t1,t2,t3;
+#
 # BUG#919878: Assertion `!eliminated_tables...
 #
 CREATE TABLE t1 ( a INT );
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 8cfda40a11a..1c8039f6b53 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 ('2007-04-25 18:30:22' = 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 ('2007-04-25' = 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/type_time.result b/mysql-test/r/type_time.result
index 32bbb6baa0b..bdbfba84994 100644
--- a/mysql-test/r/type_time.result
+++ b/mysql-test/r/type_time.result
@@ -192,6 +192,113 @@ SELECT CONCAT(GREATEST(TIME('32 00:00:01'),TIME('00:00:00')));
 CONCAT(GREATEST(TIME('32 00:00:01'),TIME('00:00:00')))
 768:00:01
 #
+# MDEV-4859 Wrong value and data type of "SELECT MAX(time_column) + 1 FROM t1"
+#
+CREATE TABLE t1 (t0 TIME);
+INSERT INTO t1 VALUES ('10:10:10');
+SELECT MAX(t0)+1 FROM t1;
+MAX(t0)+1
+101011
+CREATE TABLE t2 AS SELECT MAX(t0)+1 FROM t1;
+SELECT * FROM t2;
+MAX(t0)+1
+101011
+SHOW COLUMNS FROM t2;
+Field	Type	Null	Key	Default	Extra
+MAX(t0)+1	bigint(12)	YES		NULL	
+DROP TABLE t2,t1;
+CREATE TABLE t1 (t0 TIME);
+INSERT INTO t1 VALUES ('10:10:10');
+SELECT MAX(t0)+1.1 FROM t1;
+MAX(t0)+1.1
+101011.1
+CREATE TABLE t2 AS SELECT MAX(t0)+1.1 FROM t1;
+SELECT * FROM t2;
+MAX(t0)+1.1
+101011.1
+SHOW COLUMNS FROM t2;
+Field	Type	Null	Key	Default	Extra
+MAX(t0)+1.1	decimal(12,1)	YES		NULL	
+DROP TABLE t2,t1;
+CREATE TABLE t1 (t0 TIME);
+INSERT INTO t1 VALUES ('10:10:10');
+SELECT MAX(t0)+1e0 FROM t1;
+MAX(t0)+1e0
+101011
+CREATE TABLE t2 AS SELECT MAX(t0)+1e0 FROM t1;
+SELECT * FROM t2;
+MAX(t0)+1e0
+101011
+SHOW COLUMNS FROM t2;
+Field	Type	Null	Key	Default	Extra
+MAX(t0)+1e0	double	YES		NULL	
+DROP TABLE t2,t1;
+CREATE TABLE t1 (t1 TIME(1));
+INSERT INTO t1 VALUES ('10:10:10');
+SELECT MAX(t1)+1 FROM t1;
+MAX(t1)+1
+101011.0
+CREATE TABLE t2 AS SELECT MAX(t1)+1 FROM t1;
+SELECT * FROM t2;
+MAX(t1)+1
+101011.0
+SHOW COLUMNS FROM t2;
+Field	Type	Null	Key	Default	Extra
+MAX(t1)+1	decimal(13,1)	YES		NULL	
+DROP TABLE t2,t1;
+CREATE TABLE t1 (t0 DATETIME);
+INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
+SELECT MAX(t0)+1 FROM t1;
+MAX(t0)+1
+20010101101011
+CREATE TABLE t2 AS SELECT MAX(t0)+1 FROM t1;
+SELECT * FROM t2;
+MAX(t0)+1
+20010101101011
+SHOW COLUMNS FROM t2;
+Field	Type	Null	Key	Default	Extra
+MAX(t0)+1	bigint(21)	YES		NULL	
+DROP TABLE t2,t1;
+CREATE TABLE t1 (t0 DATETIME);
+INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
+SELECT MAX(t0)+1.1 FROM t1;
+MAX(t0)+1.1
+20010101101011.1
+CREATE TABLE t2 AS SELECT MAX(t0)+1.1 FROM t1;
+SELECT * FROM t2;
+MAX(t0)+1.1
+20010101101011.1
+SHOW COLUMNS FROM t2;
+Field	Type	Null	Key	Default	Extra
+MAX(t0)+1.1	decimal(21,1)	YES		NULL	
+DROP TABLE t2,t1;
+CREATE TABLE t1 (t0 DATETIME);
+INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
+SELECT MAX(t0)+1e0 FROM t1;
+MAX(t0)+1e0
+20010101101011
+CREATE TABLE t2 AS SELECT MAX(t0)+1e0 FROM t1;
+SELECT * FROM t2;
+MAX(t0)+1e0
+20010101101011
+SHOW COLUMNS FROM t2;
+Field	Type	Null	Key	Default	Extra
+MAX(t0)+1e0	double	YES		NULL	
+DROP TABLE t2,t1;
+CREATE TABLE t1 (t1 DATETIME(1));
+INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
+SELECT MAX(t1)+1 FROM t1;
+MAX(t1)+1
+20010101101011.0
+CREATE TABLE t2 AS SELECT MAX(t1)+1 FROM t1;
+SELECT * FROM t2;
+MAX(t1)+1
+20010101101011.0
+SHOW COLUMNS FROM t2;
+Field	Type	Null	Key	Default	Extra
+MAX(t1)+1	decimal(22,1)	YES		NULL	
+DROP TABLE t2,t1;
+#
 # End of 5.3 tests
 #
 CREATE TABLE t1 (f1 TIME);
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 75255d558c4..a3ad63c035a 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -1721,6 +1721,36 @@ foo
 deallocate prepare stmt1;
 End of 5.1 tests
 #
+# mdev-5091: Asseirtion failure for UNION with ORDER BY 
+#            in one of selects
+#
+CREATE TABLE t1 (i int, c char(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (6,'b');
+CREATE VIEW v1 AS SELECT * FROM t1;
+( SELECT i FROM v1 GROUP BY i ORDER BY CONCAT( c, c ) LIMIT 1 )
+UNION 
+( SELECT i FROM t1 );
+i
+6
+DROP VIEW v1;
+DROP TABLE t1;
+#
+# mdev-5382:  UNION with ORDER BY in subselect
+#
+CREATE TABLE t1 (a int DEFAULT NULL);
+INSERT INTO t1 VALUES (2), (4);
+CREATE TABLE t2 (b int DEFAULT NULL);
+INSERT INTO t2 VALUES (1), (3);
+SELECT c1 FROM (SELECT (SELECT a FROM t1 WHERE t1.a <= t2.b
+UNION ALL
+SELECT a FROM t1 WHERE t1.a+3<= t2.b
+ORDER BY a DESC) AS c1 FROM t2) t3;
+c1
+NULL
+2
+DROP TABLE t1,t2;
+End of 5.3 tests
+#
 # Bug#57986 ORDER BY clause is not used after a UNION, 
 #           if embedded in a SELECT
 #