5.5 merge

28 files changed, 375 insertions, 116 deletions

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 ab23992fe10..2b936af8dec 100644
--- a/mysql-test/r/dyncol.result
+++ b/mysql-test/r/dyncol.result
@@ -1439,6 +1439,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/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 3a7dfffc228..45457e6a4fd 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -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);
diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result
index e771d48b6d2..091719095fb 100644
--- a/mysql-test/r/index_merge_myisam.result
+++ b/mysql-test/r/index_merge_myisam.result
@@ -1643,4 +1643,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 1c94f867a54..de905454a63 100644
--- a/mysql-test/r/mdl_sync.result
+++ b/mysql-test/r/mdl_sync.result
@@ -1843,15 +1843,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.
 #
@@ -1888,16 +1884,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 ... .
 #
@@ -1931,14 +1921,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.
@@ -2426,12 +2412,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/mysql_tzinfo_to_sql_symlink.result b/mysql-test/r/mysql_tzinfo_to_sql_symlink.result
index fce61687c83..7fe3962b44f 100644
--- a/mysql-test/r/mysql_tzinfo_to_sql_symlink.result
+++ b/mysql-test/r/mysql_tzinfo_to_sql_symlink.result
@@ -5,18 +5,18 @@ TRUNCATE TABLE time_zone;
 TRUNCATE TABLE time_zone_name;
 TRUNCATE TABLE time_zone_transition;
 TRUNCATE TABLE time_zone_transition_type;
+Warning: Skipping directory 'MYSQLTEST_VARDIR/zoneinfo/posix/posix': to avoid infinite symlink recursion.
 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_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')
 ;
 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_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')
 ;
-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/select.result b/mysql-test/r/select.result
index 55b2ac7bc3a..670c1460ba7 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 <> 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 32a4b0e30a8..81eda02fda9 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 <> 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 55b2ac7bc3a..670c1460ba7 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 <> 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/subselect.result b/mysql-test/r/subselect.result
index b15b9c262f7..4d9bbf93168 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 735ad0d7443..e6f30472777 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2295,6 +2295,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 01d67686003..f9d4c140936 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 9ba4c1c0279..5850e9028ce 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 648a2516bee..2ac977f4574 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 70c9be1eaf4..213eeff5572 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 293a9f0083c..d05a703e647 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/type_datetime.result b/mysql-test/r/type_datetime.result
index ff81029d127..61845c7b2d4 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..d1be5e233df 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -1721,6 +1721,21 @@ 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;
+End of 5.3 tests
+#
 # Bug#57986 ORDER BY clause is not used after a UNION, 
 #           if embedded in a SELECT
 #