5.5. merge

7 files changed, 129 insertions, 4 deletions

diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 9049589b6db..8e50c045775 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -2186,3 +2186,47 @@ AVG(DISTINCT outr.col_int_nokey)
 7.5000
 DROP TABLE t1;
 # End of the bug#57932
+#
+# MDEV-5257: MIN/MAX Optimization (Select tables optimized away) does not work for DateTime
+# MDEV-3855: MIN/MAX optimization doesnt work for int_col > INET_ATON
+#  (correct the fix for Bug #884175)
+#
+CREATE TABLE `t1` (
+`a` int(11) NOT NULL AUTO_INCREMENT,
+`b` datetime DEFAULT NULL,
+PRIMARY KEY (`a`),
+KEY `idx_b` (`b`)
+);
+INSERT INTO `t1` (b) VALUES ('2013-01-06 23:59:59');
+INSERT INTO `t1` (b) VALUES ('2013-02-06 23:59:59');
+INSERT INTO `t1` (b) VALUES ('2013-03-06 23:59:59');
+INSERT INTO `t1` (b) VALUES ('2013-04-06 23:59:59');
+INSERT INTO `t1` (b) VALUES ('2013-05-06 23:59:59');
+INSERT INTO `t1` (b) VALUES ('2013-06-06 23:59:59');
+INSERT INTO `t1` (b) VALUES ('2013-07-06 23:59:59');
+# The following should produce "Select tables optimized away"
+EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+set names utf8;
+# Should be the same as above:
+EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+DROP TABLE t1;
+CREATE TABLE `t1` (
+`a` int(11) NOT NULL AUTO_INCREMENT,
+`b` bigint(20) DEFAULT NULL,
+PRIMARY KEY (`a`),
+KEY `idx_b` (`b`)
+);
+insert into t1 (b) values (INET_ATON('192.168.0.1'));
+insert into t1 (b) values (INET_ATON('192.168.0.2'));
+insert into t1 (b) values (INET_ATON('192.168.0.3'));
+insert into t1 (b) values (INET_ATON('192.168.0.4'));
+insert into t1 (b) values (INET_ATON('192.168.200.200'));
+# should show "Select tables optimized away"
+explain select MIN(b) from t1 where b >= inet_aton('192.168.119.32');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+DROP TABLE t1;
diff --git a/mysql-test/r/plugin_maturity.result b/mysql-test/r/plugin_maturity.result
index 3249ea9b3c1..cd47830cbd3 100644
--- a/mysql-test/r/plugin_maturity.result
+++ b/mysql-test/r/plugin_maturity.result
@@ -1,8 +1,8 @@
 INSTALL PLUGIN example SONAME 'ha_example.so';
-ERROR HY000: Can't open shared library 'ha_example.so' (errno: 0 Loading of experimental plugin EXAMPLE is prohibited by --plugin-maturity=stable)
+ERROR HY000: Can't open shared library 'ha_example.so' (errno: 1, Loading of experimental plugin EXAMPLE is prohibited by --plugin-maturity=stable)
 INSTALL SONAME 'ha_example.so';
-ERROR HY000: Can't open shared library 'ha_example.so' (errno: 0 Loading of experimental plugin EXAMPLE is prohibited by --plugin-maturity=stable)
+ERROR HY000: Can't open shared library 'ha_example.so' (errno: 1, Loading of experimental plugin EXAMPLE is prohibited by --plugin-maturity=stable)
 show warnings;
 Level	Code	Message
-Error	1126	Can't open shared library 'ha_example.so' (errno: 0 Loading of experimental plugin EXAMPLE is prohibited by --plugin-maturity=stable)
-Error	1126	Can't open shared library 'ha_example.so' (errno: 0 Loading of experimental plugin UNUSABLE is prohibited by --plugin-maturity=stable)
+Error	1126	Can't open shared library 'ha_example.so' (errno: 1, Loading of experimental plugin EXAMPLE is prohibited by --plugin-maturity=stable)
+Error	1126	Can't open shared library 'ha_example.so' (errno: 1, Loading of experimental plugin UNUSABLE is prohibited by --plugin-maturity=stable)
diff --git a/mysql-test/r/ps_max_subselect-5113.result b/mysql-test/r/ps_max_subselect-5113.result
new file mode 100644
index 00000000000..427ef628ead
--- /dev/null
+++ b/mysql-test/r/ps_max_subselect-5113.result
@@ -0,0 +1,16 @@
+CREATE TABLE t1 (b INT NOT NULL);
+INSERT INTO t1 VALUES (0),(8);
+PREPARE stmt FROM '
+ SELECT 1 FROM t1 AS o 
+   WHERE o.b >= ALL ( 
+     SELECT a2.b FROM t1 AS a1 LEFT JOIN t1 AS a2 ON ( a2.b = a1.b ) 
+     WHERE a1.b <= a2.b 
+  )
+';
+EXECUTE stmt;
+1
+1
+EXECUTE stmt;
+1
+1
+DROP TABLE t1;
diff --git a/mysql-test/r/subselect_debug.result b/mysql-test/r/subselect_debug.result
index 1d54369530b..9be53ae0473 100644
--- a/mysql-test/r/subselect_debug.result
+++ b/mysql-test/r/subselect_debug.result
@@ -11,3 +11,10 @@ REVERSE(EXISTS(SELECT RAND() FROM t1))
 0
 SET GLOBAL debug_dbug=@orig_debug;
 DROP TABLE t1;
+create table t1 (i int);
+insert into t1 values (1),(2);
+select * from t1 where (i < 200 or i = 300) and i in (select i from t1);
+i
+1
+2
+drop table t1;
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 05635dd0985..22a5c3abd78 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2038,6 +2038,27 @@ SELECT * FROM t1 WHERE 8 IN (SELECT MIN(pk) FROM t1) AND (pk = a OR pk = b);
 pk	a	b
 DROP TABLE t1;
 # End of 5.3 tests
+#
+# MDEV-5056: Wrong result (extra rows) with materialization+semijoin, IN subqueries
+#
+set @tmp_mdev5056=@@join_cache_level;
+SET join_cache_level = 2;
+CREATE TABLE t1 ( c1 VARCHAR(2), c2 VARCHAR(2), INDEX(c1) ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES 
+('JP','OM'),('VA','JP'),('CA','ML'),('ML','EG'),('DK','CA'),
+('DK','QA'),('YE','PL'),('TR','ZW'),('DK','SK'),('SK','DK'),
+('RO','ML'),('ML','BG'),('BG','ZW'),('ZW','GE'),('GE','JP'),
+('PL','EG'),('QA','YE'),('WF','DK'),('DK','JP'),('EG','OM');
+CREATE TABLE t2 ( c3 VARCHAR(2), c4 VARCHAR(2) ) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('CA','ML'),('IN','HU'),('HU','IN');
+SELECT * FROM t1 AS alias1, t1 AS alias2 
+WHERE ( alias2.c2, alias1.c1 ) IN ( SELECT c4, c3 FROM t2 ) AND alias1.c1 IN ( SELECT c2 FROM t1 );
+c1	c2	c1	c2
+CA	ML	CA	ML
+CA	ML	RO	ML
+DROP TABLE t1,t2;
+set join_cache_level=@tmp_mdev5056;
+# End of 5.5 tests
 set @subselect_mat_test_optimizer_switch_value=null;
 set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index 15cb9eeaa0e..a5629d33bfe 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2078,3 +2078,24 @@ SELECT * FROM t1 WHERE 8 IN (SELECT MIN(pk) FROM t1) AND (pk = a OR pk = b);
 pk	a	b
 DROP TABLE t1;
 # End of 5.3 tests
+#
+# MDEV-5056: Wrong result (extra rows) with materialization+semijoin, IN subqueries
+#
+set @tmp_mdev5056=@@join_cache_level;
+SET join_cache_level = 2;
+CREATE TABLE t1 ( c1 VARCHAR(2), c2 VARCHAR(2), INDEX(c1) ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES 
+('JP','OM'),('VA','JP'),('CA','ML'),('ML','EG'),('DK','CA'),
+('DK','QA'),('YE','PL'),('TR','ZW'),('DK','SK'),('SK','DK'),
+('RO','ML'),('ML','BG'),('BG','ZW'),('ZW','GE'),('GE','JP'),
+('PL','EG'),('QA','YE'),('WF','DK'),('DK','JP'),('EG','OM');
+CREATE TABLE t2 ( c3 VARCHAR(2), c4 VARCHAR(2) ) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('CA','ML'),('IN','HU'),('HU','IN');
+SELECT * FROM t1 AS alias1, t1 AS alias2 
+WHERE ( alias2.c2, alias1.c1 ) IN ( SELECT c4, c3 FROM t2 ) AND alias1.c1 IN ( SELECT c2 FROM t1 );
+c1	c2	c1	c2
+CA	ML	CA	ML
+CA	ML	RO	ML
+DROP TABLE t1,t2;
+set join_cache_level=@tmp_mdev5056;
+# End of 5.5 tests
diff --git a/mysql-test/r/userstat-badlogin-4824.result b/mysql-test/r/userstat-badlogin-4824.result
new file mode 100644
index 00000000000..8362e8d5239
--- /dev/null
+++ b/mysql-test/r/userstat-badlogin-4824.result
@@ -0,0 +1,16 @@
+create user foo@localhost identified by 'foo';
+flush user_statistics;
+set global userstat=1;
+select 1;
+1
+1
+select user, bytes_received from information_schema.user_statistics where user = 'foo';
+user	bytes_received
+foo	18
+connect(localhost,foo,bar,test,MASTER_PORT,MASTER_SOCKET);
+ERROR 28000: Access denied for user 'foo'@'localhost' (using password: YES)
+select user, bytes_received from information_schema.user_statistics where user = 'foo';
+user	bytes_received
+foo	18
+drop user foo@localhost;
+set global userstat=0;