5.3-merge

12 files changed, 232 insertions, 18 deletions

diff --git a/mysql-test/include/have_unix_socket.inc b/mysql-test/include/have_unix_socket.inc
new file mode 100644
index 00000000000..4246b138e9d
--- /dev/null
+++ b/mysql-test/include/have_unix_socket.inc
@@ -0,0 +1,13 @@
+--source include/not_embedded.inc
+
+if (!$AUTH_SOCKET_SO) {
+  skip No unix_socket plugin;
+}
+
+if (!$USER) {
+  skip USER variable is undefined;
+}
+
+if (`SELECT count(*) <> 0 FROM mysql.user WHERE user = '$USER'`) {
+  skip \$USER=$USER which exists in mysql.user;
+}
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 9cd86fbe8e6..9b88cd214b3 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -2366,6 +2366,46 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 3	DEPENDENT SUBQUERY	pi	ref	gallery_id	gallery_id	4	test.gal.id	4	Using temporary; Using filesort
 drop table galleries, pictures;
 #
+# MDEV-5740: Assertion 
+#`!derived->first_select()->exclude_from_table_unique_test ||
+#derived->outer_select()-> exclude_from_table_unique_test'
+#failed on 2nd execution of PS with derived_merge
+#
+set @save_optimizer_switch5740=@@optimizer_switch;
+SET optimizer_switch = 'derived_merge=on';
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (3),(4);
+PREPARE stmt FROM '
+  INSERT INTO t1 SELECT * FROM t2 UNION SELECT * FROM (SELECT * FROM t1) AS sq  
+';
+EXECUTE stmt;
+select * from t1;
+a
+1
+2
+3
+4
+1
+2
+EXECUTE stmt;
+select * from t1;
+a
+1
+2
+3
+4
+1
+2
+3
+4
+1
+2
+deallocate prepare stmt;
+drop table t1,t2;
+set optimizer_switch=@save_optimizer_switch5740;
+#
 # end of 5.3 tests
 #
 set optimizer_switch=@exit_optimizer_switch;
diff --git a/mysql-test/r/failed_auth_unixsocket.result b/mysql-test/r/failed_auth_unixsocket.result
new file mode 100644
index 00000000000..3cf58ca5bec
--- /dev/null
+++ b/mysql-test/r/failed_auth_unixsocket.result
@@ -0,0 +1,12 @@
+update mysql.user set plugin='unix_socket';
+flush privileges;
+connect(localhost,USER,,test,MASTER_PORT,MASTER_SOCKET);
+ERROR HY000: Plugin 'unix_socket' is not loaded
+ERROR HY000: Plugin 'unix_socket' is not loaded
+install plugin unix_socket soname 'auth_socket.so';
+connect(localhost,USER,,test,MASTER_PORT,MASTER_SOCKET);
+ERROR 28000: Access denied for user 'USER'@'localhost'
+ERROR 28000: Access denied for user 'USER'@'localhost'
+update mysql.user set plugin='';
+flush privileges;
+uninstall plugin unix_socket;
diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result
index 34bab225276..d5db28f04b8 100644
--- a/mysql-test/r/func_misc.result
+++ b/mysql-test/r/func_misc.result
@@ -272,6 +272,22 @@ SELECT NAME_CONST('a', -(1)) OR 1;
 NAME_CONST('a', -(1)) OR 1
 1
 #
+#MDEV-5446: Assertion `!table || (!table->read_set ||
+#bitmap_is_set(table->read_set, field_index))' fails on
+#EXPLAIN EXTENDED with VALUES function
+#
+CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,10);
+CREATE VIEW v1 AS SELECT * FROM t1;
+EXPLAIN EXTENDED SELECT VALUES(b) FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+Warnings:
+Note	1003	select values(10) AS `VALUES(b)` from dual
+drop view v1;
+drop table t1;
+End of 5.3 tests
+# 
 # Bug #52165: Assertion failed: file .\dtoa.c, line 465
 # 
 CREATE TABLE t1 (a SET('a'), b INT);
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 582b4e97f2e..c7f22cf05e5 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2344,6 +2344,28 @@ id	a2	a3	id	a2	a3
 DROP VIEW v2;
 DROP TABLE t1,t2;
 #
+# MDEV-5686: degenerate disjunct in NOT IN subquery
+#
+CREATE TABLE t1 (a int, b int, c varchar(3)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,1,'CAN'),(2,2,'AUS');
+CREATE TABLE t2 (f int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3);
+EXPLAIN EXTENDED
+SELECT * FROM t2 
+WHERE f NOT IN (SELECT b FROM t1
+WHERE 0 OR (c IN ('USA') OR c NOT IN ('USA')) AND a = b);
+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	
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select 3 AS `f` from dual where (not(<expr_cache><3>(<in_optimizer>(3,<exists>(select `test`.`t1`.`b` from `test`.`t1` where (((`test`.`t1`.`c` = 'USA') or (`test`.`t1`.`c` <> 'USA')) and trigcond(((<cache>(3) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`))) and (`test`.`t1`.`b` = `test`.`t1`.`a`)) having trigcond(<is_not_null_test>(`test`.`t1`.`b`)))))))
+SELECT * FROM t2 
+WHERE f NOT IN (SELECT b FROM t1
+WHERE 0 OR (c IN ('USA') OR c NOT IN ('USA')) AND a = b);
+f
+3
+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/view.result b/mysql-test/r/view.result
index 33355db0105..7a7dbf92ee3 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -5002,6 +5002,22 @@ v1_field1
 deallocate prepare my_stmt;
 DROP VIEW v1,v2;
 DROP TABLE t1,t2,t3,t4;
+#
+#MDEV-5717: Server crash with insert statement containing DEFAULT into
+#view
+#
+CREATE TABLE t1 (
+`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+`test` tinyint(3) unsigned NOT NULL DEFAULT '0',
+PRIMARY KEY (`id`)
+);
+CREATE VIEW v1 AS (select  t1.id AS id,  t1.test AS test from t1);
+INSERT INTO v1 SET test = DEFAULT;
+select * from v1;
+id	test
+1	0
+drop view v1;
+drop table t1;
 # -----------------------------------------------------------------
 # -- End of 5.3 tests.
 # -----------------------------------------------------------------
diff --git a/mysql-test/suite/plugins/t/unix_socket.test b/mysql-test/suite/plugins/t/unix_socket.test
index 5869e2717c9..2c1af9fb1da 100644
--- a/mysql-test/suite/plugins/t/unix_socket.test
+++ b/mysql-test/suite/plugins/t/unix_socket.test
@@ -1,20 +1,4 @@
---source include/not_embedded.inc
-# If we run this as root, $USER gets authenticated as the `root' user, and we
-# get .result differences from CURRENT_USER().
---source include/not_as_root.inc
-
-# The previous check verifies that the user does not have root permissions. 
-# However in some cases tests are run under a user named 'root',
-# even although this user does not have real root permissions. 
-# This test should be skipped in this case, since it does not expect
-# that there are records in mysql.user where user=<username>
-if ($USER=="root") {
-  skip Cannot be run by user named 'root' even if it does not have all privileges;
-}
-
-if (!$AUTH_SOCKET_SO) {
-  skip No auth_socket plugin;
-}
+--source include/have_unix_socket.inc
 
 if (!$USER) {
   skip USER variable is undefined;
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index 61e11cebad4..de08b1c4d0d 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -1704,6 +1704,33 @@ ORDER BY gallery_name ASC
 drop table galleries, pictures;
 
 --echo #
+--echo # MDEV-5740: Assertion 
+--echo #`!derived->first_select()->exclude_from_table_unique_test ||
+--echo #derived->outer_select()-> exclude_from_table_unique_test'
+--echo #failed on 2nd execution of PS with derived_merge
+--echo #
+
+set @save_optimizer_switch5740=@@optimizer_switch;
+SET optimizer_switch = 'derived_merge=on';
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (3),(4);
+
+PREPARE stmt FROM '
+  INSERT INTO t1 SELECT * FROM t2 UNION SELECT * FROM (SELECT * FROM t1) AS sq  
+';
+EXECUTE stmt;
+select * from t1;
+EXECUTE stmt;
+select * from t1;
+deallocate prepare stmt;
+
+drop table t1,t2;
+set optimizer_switch=@save_optimizer_switch5740;
+
+--echo #
 --echo # end of 5.3 tests
 --echo #
 
diff --git a/mysql-test/t/failed_auth_unixsocket.test b/mysql-test/t/failed_auth_unixsocket.test
new file mode 100644
index 00000000000..fdde7399fc3
--- /dev/null
+++ b/mysql-test/t/failed_auth_unixsocket.test
@@ -0,0 +1,29 @@
+--source include/have_unix_socket.inc
+
+#
+# MDEV-3909 remote user enumeration
+# unix_socket tests
+#
+update mysql.user set plugin='unix_socket';
+flush privileges;
+
+--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT $USER USER
+--error ER_PLUGIN_IS_NOT_LOADED
+connect (fail,localhost,$USER);
+
+--error ER_PLUGIN_IS_NOT_LOADED
+change_user $USER;
+
+eval install plugin unix_socket soname '$AUTH_SOCKET_SO';
+
+--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT $USER USER
+--error ER_ACCESS_DENIED_NO_PASSWORD_ERROR
+connect (fail,localhost,$USER);
+
+--error ER_ACCESS_DENIED_NO_PASSWORD_ERROR
+change_user $USER;
+
+update mysql.user set plugin='';
+flush privileges;
+uninstall plugin unix_socket;
+
diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test
index 424722a8990..24fe1ef7984 100644
--- a/mysql-test/t/func_misc.test
+++ b/mysql-test/t/func_misc.test
@@ -308,6 +308,22 @@ SELECT NAME_CONST('a', -(1 AND 2)) AND 1;
 SELECT NAME_CONST('a', -(1)) OR 1;
 
 --echo #
+--echo #MDEV-5446: Assertion `!table || (!table->read_set ||
+--echo #bitmap_is_set(table->read_set, field_index))' fails on
+--echo #EXPLAIN EXTENDED with VALUES function
+--echo #
+CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,10);
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+EXPLAIN EXTENDED SELECT VALUES(b) FROM v1;
+
+drop view v1;
+drop table t1;
+
+--echo End of 5.3 tests
+
+--echo # 
 --echo # Bug #52165: Assertion failed: file .\dtoa.c, line 465
 --echo # 
 
@@ -379,4 +395,3 @@ drop table t1,tv;
 --echo #
 --echo # End of 5.5 tests
 --echo #
-
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index 1c058c5e6e1..b179ead39d7 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -1889,6 +1889,27 @@ DROP VIEW v2;
 DROP TABLE t1,t2;
 
 --echo #
+--echo # MDEV-5686: degenerate disjunct in NOT IN subquery
+--echo #
+
+CREATE TABLE t1 (a int, b int, c varchar(3)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,1,'CAN'),(2,2,'AUS');
+
+CREATE TABLE t2 (f int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3);
+
+EXPLAIN EXTENDED
+SELECT * FROM t2 
+  WHERE f NOT IN (SELECT b FROM t1
+                    WHERE 0 OR (c IN ('USA') OR c NOT IN ('USA')) AND a = b);
+
+SELECT * FROM t2 
+  WHERE f NOT IN (SELECT b FROM t1
+                    WHERE 0 OR (c IN ('USA') OR c NOT IN ('USA')) AND a = b);
+
+DROP TABLE t1,t2;
+
+--echo #
 --echo # MDEV-3899  Valgrind warnings (blocks are definitely lost) in filesort on IN subquery with SUM and DISTINCT
 --echo #
 
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index a01a8a4d815..017887223a3 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -4918,6 +4918,25 @@ deallocate prepare my_stmt;
 DROP VIEW v1,v2;
 DROP TABLE t1,t2,t3,t4;
 
+--echo #
+--echo #MDEV-5717: Server crash with insert statement containing DEFAULT into
+--echo #view
+--echo #
+CREATE TABLE t1 (
+  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+  `test` tinyint(3) unsigned NOT NULL DEFAULT '0',
+  PRIMARY KEY (`id`)
+);
+
+CREATE VIEW v1 AS (select  t1.id AS id,  t1.test AS test from t1);
+
+INSERT INTO v1 SET test = DEFAULT;
+
+select * from v1;
+
+drop view v1;
+drop table t1;
+
 --echo # -----------------------------------------------------------------
 --echo # -- End of 5.3 tests.
 --echo # -----------------------------------------------------------------