5.5. merge

17 files changed, 285 insertions, 28 deletions

diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl
index 2672d130a5f..5e6677254b4 100755
--- a/mysql-test/mysql-test-run.pl
+++ b/mysql-test/mysql-test-run.pl
@@ -1900,7 +1900,7 @@ sub collect_mysqld_features {
 
   my @list= split '\n', $list;
   mtr_error("Could not find version of MariaDB")
-     unless shift(@list) =~ /^$exe_mysqld\s+Ver\s(\d+)\.(\d+)\.(\d+)(\S*)/;
+     unless shift(@list) =~ /^\Q$exe_mysqld\E\s+Ver\s(\d+)\.(\d+)\.(\d+)(\S*)/;
   $mysql_version_id= $1*10000 + $2*100 + $3;
   $mysql_version_extra= $4;
   mtr_report("MariaDB Version $1.$2.$3$4");
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;
diff --git a/mysql-test/suite/rpl/t/rpl_mysql_upgrade.test b/mysql-test/suite/rpl/t/rpl_mysql_upgrade.test
index ec40d23f9eb..c315c6a32c8 100644
--- a/mysql-test/suite/rpl/t/rpl_mysql_upgrade.test
+++ b/mysql-test/suite/rpl/t/rpl_mysql_upgrade.test
@@ -24,8 +24,7 @@ connection master;
 let $before_position= query_get_value(SHOW MASTER STATUS, Position, 1);
 
 #With '--force' option, mysql_upgrade always executes all sql statements for upgrading.
-#--skip-write-binlog option disables binlog.
---exec $MYSQL_UPGRADE --skip-write-binlog --skip-verbose --force --user=root > $MYSQLTEST_VARDIR/log/mysql_upgrade.log 2>&1
+--exec $MYSQL_UPGRADE --skip-verbose --force --user=root > $MYSQLTEST_VARDIR/log/mysql_upgrade.log 2>&1
 sync_slave_with_master;
 
 connection master;
@@ -43,7 +42,7 @@ source include/wait_for_slave_sql_to_stop.inc;
 
 connection master;
 #With '--force' option, mysql_upgrade always executes all sql statements for upgrading.
---exec $MYSQL_UPGRADE --skip-verbose --force --user=root > $MYSQLTEST_VARDIR/log/mysql_upgrade.log 2>&1
+--exec $MYSQL_UPGRADE --skip-verbose --write-binlog --force --user=root > $MYSQLTEST_VARDIR/log/mysql_upgrade.log 2>&1
 
 connection master;
 let $after_file= query_get_value(SHOW MASTER STATUS, File, 1);
diff --git a/mysql-test/suite/sys_vars/r/character_sets_dir_basic.result b/mysql-test/suite/sys_vars/r/character_sets_dir_basic.result
index ef8ec0cd9b8..c04e5dac199 100644
--- a/mysql-test/suite/sys_vars/r/character_sets_dir_basic.result
+++ b/mysql-test/suite/sys_vars/r/character_sets_dir_basic.result
@@ -1,20 +1,20 @@
 select @@global.character_sets_dir;
 @@global.character_sets_dir
-MYSQL_CHARSETSDIR
+MYSQL_CHARSETSDIR/
 select @@session.character_sets_dir;
 ERROR HY000: Variable 'character_sets_dir' is a GLOBAL variable
 show global variables like 'character_sets_dir';
 Variable_name	Value
-character_sets_dir	MYSQL_CHARSETSDIR
+character_sets_dir	MYSQL_CHARSETSDIR/
 show session variables like 'character_sets_dir';
 Variable_name	Value
-character_sets_dir	MYSQL_CHARSETSDIR
+character_sets_dir	MYSQL_CHARSETSDIR/
 select * from information_schema.global_variables where variable_name='character_sets_dir';
 VARIABLE_NAME	VARIABLE_VALUE
-CHARACTER_SETS_DIR	MYSQL_CHARSETSDIR
+CHARACTER_SETS_DIR	MYSQL_CHARSETSDIR/
 select * from information_schema.session_variables where variable_name='character_sets_dir';
 VARIABLE_NAME	VARIABLE_VALUE
-CHARACTER_SETS_DIR	MYSQL_CHARSETSDIR
+CHARACTER_SETS_DIR	MYSQL_CHARSETSDIR/
 set global character_sets_dir="foo";
 ERROR HY000: Variable 'character_sets_dir' is a read only variable
 set session character_sets_dir="foo";
diff --git a/mysql-test/suite/sys_vars/t/character_sets_dir_basic.test b/mysql-test/suite/sys_vars/t/character_sets_dir_basic.test
index 5baff405d28..0bbfd04133d 100644
--- a/mysql-test/suite/sys_vars/t/character_sets_dir_basic.test
+++ b/mysql-test/suite/sys_vars/t/character_sets_dir_basic.test
@@ -2,26 +2,17 @@
 #
 # show the global and session values;
 #
-
-# workaround to adjust for the directory separators being different in 
-# different OSes : slash on unixes and backslash or slash on windows.
-# TODO: fix with a proper comparison in mysqltest
-let $rcd= `SELECT REPLACE('$MYSQL_CHARSETSDIR', '\\\\\', '.')`;
-let $rcd= `SELECT REPLACE('$rcd', '/', '.')`;
-let $regex_charsetdir= `SELECT '/$rcd[[:punct:]]/MYSQL_CHARSETSDIR/'`;
-
---replace_regex $regex_charsetdir
+--replace_result $MYSQL_CHARSETSDIR MYSQL_CHARSETSDIR
 select @@global.character_sets_dir;
-
 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
 select @@session.character_sets_dir;
---replace_regex $regex_charsetdir
+--replace_result $MYSQL_CHARSETSDIR MYSQL_CHARSETSDIR
 show global variables like 'character_sets_dir';
---replace_regex $regex_charsetdir
+--replace_result $MYSQL_CHARSETSDIR MYSQL_CHARSETSDIR
 show session variables like 'character_sets_dir';
---replace_regex $regex_charsetdir
+--replace_result $MYSQL_CHARSETSDIR MYSQL_CHARSETSDIR
 select * from information_schema.global_variables where variable_name='character_sets_dir';
---replace_regex $regex_charsetdir
+--replace_result $MYSQL_CHARSETSDIR MYSQL_CHARSETSDIR
 select * from information_schema.session_variables where variable_name='character_sets_dir';
 
 #
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index 0c2f28ab25d..cf5f00c3ee1 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -1465,3 +1465,56 @@ SELECT AVG(DISTINCT outr.col_int_nokey) FROM t1 AS outr LEFT JOIN t1 AS outr2 ON
 outr.col_int_nokey = outr2.col_int_nokey;
 DROP TABLE t1;
 --echo # End of the bug#57932
+
+
+--echo #
+--echo # MDEV-5257: MIN/MAX Optimization (Select tables optimized away) does not work for DateTime
+--echo # MDEV-3855: MIN/MAX optimization doesnt work for int_col > INET_ATON
+--echo #  (correct the fix for Bug #884175)
+--echo #
+
+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');
+
+--echo # The following should produce "Select tables optimized away"
+EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59';
+
+-- connect (con1,localhost,root,,)
+-- connection con1
+set names utf8;
+-- echo # Should be the same as above:
+EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59';
+
+--connection default
+--disconnect con1
+
+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'));
+--echo # should show "Select tables optimized away"
+explain select MIN(b) from t1 where b >= inet_aton('192.168.119.32');
+DROP TABLE t1;
+
diff --git a/mysql-test/t/mysqld--help.test b/mysql-test/t/mysqld--help.test
index aacf3affe10..c796f8b569e 100644
--- a/mysql-test/t/mysqld--help.test
+++ b/mysql-test/t/mysqld--help.test
@@ -54,7 +54,7 @@ perl;
     s/\b2146435072\b/9223372036853727232/;
     s/\b196608\b/262144/;
     s/\b4294963200\b/18446744073709547520/;
-    foreach $var (@env) { s/$ENV{$var}/$var/ }
+    foreach $var (@env) { s/\Q$ENV{$var}\E/$var/ }
     next if /use --skip-(use-)?symbolic-links to disable/; # for valgrind, again
     next if $skip;
     print;
diff --git a/mysql-test/t/ps_max_subselect-5113.test b/mysql-test/t/ps_max_subselect-5113.test
new file mode 100644
index 00000000000..255e81c1549
--- /dev/null
+++ b/mysql-test/t/ps_max_subselect-5113.test
@@ -0,0 +1,20 @@
+#
+# MDEV-5113 Wrong result (extra row) and valgrind warnings in Item_maxmin_subselect::any_value on 2nd execution of PS with SELECT subquery
+#
+
+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;
+EXECUTE stmt;
+
+DROP TABLE t1;
+
diff --git a/mysql-test/t/subselect_debug.test b/mysql-test/t/subselect_debug.test
index 101311beb5c..f27867dc9d0 100644
--- a/mysql-test/t/subselect_debug.test
+++ b/mysql-test/t/subselect_debug.test
@@ -15,3 +15,13 @@ SELECT SUM(EXISTS(SELECT RAND() FROM t1)) FROM t1;
 SELECT REVERSE(EXISTS(SELECT RAND() FROM t1));
 SET GLOBAL debug_dbug=@orig_debug;
 DROP TABLE t1;
+
+#
+# MDEV-5284 Assertion `!(*expr)->fixed' fails in replace_where_subcondition with IN suquery
+#
+
+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);
+drop table t1;
+
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index e9226b8884f..58831c4ffb3 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -1726,3 +1726,29 @@ SELECT * FROM t1 WHERE 8 IN (SELECT MIN(pk) FROM t1) AND (pk = a OR pk = b);
 DROP TABLE t1;
 
 --echo # End of 5.3 tests
+
+
+--echo #
+--echo # MDEV-5056: Wrong result (extra rows) with materialization+semijoin, IN subqueries
+--echo #
+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 );
+
+DROP TABLE t1,t2;
+set join_cache_level=@tmp_mdev5056;
+
+--echo # End of 5.5 tests
+
diff --git a/mysql-test/t/userstat-badlogin-4824.test b/mysql-test/t/userstat-badlogin-4824.test
new file mode 100644
index 00000000000..97370c1d081
--- /dev/null
+++ b/mysql-test/t/userstat-badlogin-4824.test
@@ -0,0 +1,33 @@
+#
+# MDEV-4824 userstats - wrong user statistics
+#
+--source include/not_embedded.inc
+
+create user foo@localhost identified by 'foo';
+flush user_statistics;
+set global userstat=1;
+
+connect(foo, localhost, foo, foo);
+select 1;
+disconnect foo;
+connection default;
+
+# wait for user_statistics changes to become visible
+let $wait_condition= select count(*) = 1 from information_schema.processlist;
+--source include/wait_condition.inc
+
+# 41 is for ps-procotol
+--replace_result 41 18
+select user, bytes_received from information_schema.user_statistics where user = 'foo';
+
+--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
+--error ER_ACCESS_DENIED_ERROR
+connect(foo, localhost, foo, bar);
+
+connection default;
+
+--replace_result 41 18
+select user, bytes_received from information_schema.user_statistics where user = 'foo';
+
+drop user foo@localhost;
+set global userstat=0;