Merge 10.4 into 10.5

6 files changed, 130 insertions, 2 deletions

diff --git a/mysql-test/main/delete_use_source.result b/mysql-test/main/delete_use_source.result
index 22972d0aa4a..0990a551db6 100644
--- a/mysql-test/main/delete_use_source.result
+++ b/mysql-test/main/delete_use_source.result
@@ -65,8 +65,8 @@ rollback;
 start transaction;
 explain delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	c1	NULL	NULL	NULL	670	Using where
-2	DEPENDENT SUBQUERY	b	ref	c1	c1	4	test.t1.c1	167	Using index
+1	PRIMARY	t1	ALL	c1	NULL	NULL	NULL	#	Using where
+2	DEPENDENT SUBQUERY	b	ref	c1	c1	4	test.t1.c1	#	Using index
 delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 ;
 affected rows: 500
 select count(*) from v1 where c1=0;
diff --git a/mysql-test/main/delete_use_source.test b/mysql-test/main/delete_use_source.test
index ed4c62b090c..4aed00da375 100644
--- a/mysql-test/main/delete_use_source.test
+++ b/mysql-test/main/delete_use_source.test
@@ -64,6 +64,7 @@ rollback;
 --echo #
 
 start transaction;
+--replace_column 9 #
 explain delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500;
 --enable_info ONCE
 delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 ;
diff --git a/mysql-test/main/lock_kill.result b/mysql-test/main/lock_kill.result
new file mode 100644
index 00000000000..727dd66da7f
--- /dev/null
+++ b/mysql-test/main/lock_kill.result
@@ -0,0 +1,36 @@
+#
+# MDEV-17749 Kill during LOCK TABLE ; ALTER TABLE causes assert
+#
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+connect  con1,localhost,root,,test;
+LOCK TABLE t1 WRITE;
+ALTER TABLE t1 ADD COLUMN b INT;
+connection default;
+Killing connection
+connection con1;
+connection default;
+disconnect con1;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+CREATE TABLE t2 (a INT) ENGINE=InnoDB;
+connect  con1,localhost,root,,test;
+LOCK TABLE t1 WRITE, t2 WRITE;
+ALTER TABLE t1 ADD COLUMN b INT;
+connection default;
+Killing connection
+connection con1;
+connection default;
+disconnect con1;
+DROP TABLE t1, t2;
+CREATE TABLE t1 (id int(11)) ENGINE=InnoDB;
+LOCK TABLES t1 WRITE;
+SET max_statement_time= 0.0001;
+CREATE TRIGGER tr16 AFTER UPDATE ON t1 FOR EACH ROW INSERT INTO t1 VALUES (1);
+SET max_statement_time= default;
+DROP TRIGGER IF EXISTS trg16;
+Warnings:
+Note	1360	Trigger does not exist
+DROP TABLE t1;
+#
+# End of 10.3 tests
+#
diff --git a/mysql-test/main/lock_kill.test b/mysql-test/main/lock_kill.test
new file mode 100644
index 00000000000..d0b83fe1413
--- /dev/null
+++ b/mysql-test/main/lock_kill.test
@@ -0,0 +1,59 @@
+--source include/have_innodb.inc
+
+# This test file is for testing killing of queries that are under LOCK TABLES
+
+--echo #
+--echo # MDEV-17749 Kill during LOCK TABLE ; ALTER TABLE causes assert
+--echo #
+
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+--connect (con1,localhost,root,,test)
+LOCK TABLE t1 WRITE;
+--let $conid= `SELECT CONNECTION_ID()`
+--send ALTER TABLE t1 ADD COLUMN b INT
+--connection default
+--disable_query_log
+--echo Killing connection
+eval KILL $conid;
+--enable_query_log
+--connection con1
+--error 0,2013
+reap;
+--connection default
+--disconnect con1
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+CREATE TABLE t2 (a INT) ENGINE=InnoDB;
+--connect (con1,localhost,root,,test)
+LOCK TABLE t1 WRITE, t2 WRITE;
+--let $conid= `SELECT CONNECTION_ID()`
+--send ALTER TABLE t1 ADD COLUMN b INT
+--connection default
+--disable_query_log
+--echo Killing connection
+eval KILL $conid;
+--enable_query_log
+--connection con1
+--error 0,2013
+reap;
+--connection default
+--disconnect con1
+DROP TABLE t1, t2;
+
+# Similar test for CREATE TRIGGER, which also failed
+
+CREATE TABLE t1 (id int(11)) ENGINE=InnoDB;
+LOCK TABLES t1 WRITE;
+SET max_statement_time= 0.0001;
+--error 0,1969
+--disable_warnings
+CREATE TRIGGER tr16 AFTER UPDATE ON t1 FOR EACH ROW INSERT INTO t1 VALUES (1);
+--enable_warnings
+SET max_statement_time= default;
+DROP TRIGGER IF EXISTS trg16;
+DROP TABLE t1;
+ 
+--echo #
+--echo # End of 10.3 tests
+--echo #
diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result
index 6b2586e9be6..d904585d4d4 100644
--- a/mysql-test/main/ps.result
+++ b/mysql-test/main/ps.result
@@ -5551,6 +5551,24 @@ Note	1003	/* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1, t2;
 #
+# MDEV-25576: The statement EXPLAIN running as regular statement and
+#             as prepared statement produces different results for
+#             UPDATE with subquery
+#
+CREATE TABLE t1 (c1 INT KEY) ENGINE=MyISAM;
+CREATE TABLE t2 (c2 INT) ENGINE=MyISAM;
+CREATE TABLE t3 (c3 INT) ENGINE=MyISAM;
+EXPLAIN EXTENDED UPDATE t3 SET c3 = ( SELECT COUNT(d1.c1) FROM ( SELECT a11.c1 FROM t1 AS a11 STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1 JOIN t1 AS a12 ON a12.c1 = a11.c1 ) d1 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	100.00	
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+PREPARE stmt FROM "EXPLAIN EXTENDED UPDATE t3 SET c3 = ( SELECT COUNT(d1.c1) FROM ( SELECT a11.c1 FROM t1 AS a11 STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1 JOIN t1 AS a12 ON a12.c1 = a11.c1 ) d1 )";
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	100.00	
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1, t2, t3;
 # End of 10.2 tests
 #
 #
diff --git a/mysql-test/main/ps.test b/mysql-test/main/ps.test
index 2a468d33ace..837fa6f2b6e 100644
--- a/mysql-test/main/ps.test
+++ b/mysql-test/main/ps.test
@@ -4975,7 +4975,21 @@ SHOW WARNINGS;
 
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1, t2;
+
+--echo #
+--echo # MDEV-25576: The statement EXPLAIN running as regular statement and
+--echo #             as prepared statement produces different results for
+--echo #             UPDATE with subquery
 --echo #
+CREATE TABLE t1 (c1 INT KEY) ENGINE=MyISAM;
+CREATE TABLE t2 (c2 INT) ENGINE=MyISAM;
+CREATE TABLE t3 (c3 INT) ENGINE=MyISAM;
+EXPLAIN EXTENDED UPDATE t3 SET c3 = ( SELECT COUNT(d1.c1) FROM ( SELECT a11.c1 FROM t1 AS a11 STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1 JOIN t1 AS a12 ON a12.c1 = a11.c1 ) d1 );
+PREPARE stmt FROM "EXPLAIN EXTENDED UPDATE t3 SET c3 = ( SELECT COUNT(d1.c1) FROM ( SELECT a11.c1 FROM t1 AS a11 STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1 JOIN t1 AS a12 ON a12.c1 = a11.c1 ) d1 )";
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1, t2, t3;
+
 --echo # End of 10.2 tests
 --echo #