7 files changed, 191 insertions, 10 deletions
diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test
index 6c51f23c51e..d1853cdfcc0 100644
--- a/mysql-test/t/derived.test
+++ b/mysql-test/t/derived.test
@@ -1,7 +1,4 @@
 # Initialize
---disable_warnings
-drop table if exists t1,t2,t3;
---enable_warnings
 
 set @save_derived_optimizer_switch=@@optimizer_switch;
 set optimizer_switch='derived_merge=off,derived_with_keys=off';
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index 849ada947f3..9f5cee15ade 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -1954,7 +1954,7 @@ set optimizer_switch=@exit_optimizer_switch;
 set join_cache_level=@exit_join_cache_level;
 
 --echo #
---echo # Bug mdev-12812: EXPLAIN for query with many expensive derived
+--echo # Bug mdev-18479: EXPLAIN for query with many expensive derived
 --echo #
 
 CREATE TABLE t1
diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test
index e60b7827f75..cfb24a63304 100644
--- a/mysql-test/t/join_nested.test
+++ b/mysql-test/t/join_nested.test
@@ -1380,3 +1380,37 @@ DROP VIEW v1;
 DROP TABLE t1,t2;
 
 set optimizer_search_depth= @tmp_mdev621;
+
+--echo #
+--echo # MDEV-19588: Nested left joins using optimized join cache
+--echo #
+
+set optimizer_switch='optimize_join_buffer_size=on';
+
+set @save_join_cache_level= @@join_cache_level;
+set join_cache_level=2;
+
+CREATE TABLE t1 (i1 int, c1 varchar(20), pk int) engine=myisam;
+
+CREATE TABLE t2 (pk int, c1 varchar(20), i1 int) engine=myisam;
+INSERT INTO t2 VALUES (7,'a',-912),(8,'a',5);
+
+CREATE TABLE t3 (pk int, c1 varchar(20), i1 int) engine=myisam;
+INSERT INTO t3 VALUES
+(1,'a',-145),(2,'a',6),(3,'a',1),(7,'a',NULL),(8,'a',889),(9,'a',146),
+(10,'a',177),(16,'a',-433),(17,'a',NULL),(18,'a',2),(19,'a',3),(20,'a',5),
+(21,'a',-484),(22,'a',369),(23,'a',-192),(24,'a',-163),(25,'a',5),(26,'a',NULL);
+
+let $q=
+SELECT t3.*
+FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1
+WHERE t2.pk < 13 OR t3.i1 IS NULL;
+
+eval $q;
+eval explain extended $q;
+
+DROP TABLE t1,t2,t3;
+
+set join_cache_level= @save_join_cache_level;
+
+set optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index 900d0bd4283..b5328f74e70 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -360,7 +360,6 @@ drop table t1, t2;
 #
 
 connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
-connection root;
 create database mysqltest;
 create table mysqltest.t1 (a int, b int, primary key (a));
 create table mysqltest.t2 (a int, b int, primary key (a));
@@ -369,7 +368,6 @@ create user mysqltest_1@localhost;
 grant select on mysqltest.* to mysqltest_1@localhost;
 grant update on mysqltest.t1 to mysqltest_1@localhost;
 connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK);
-connection user1;
 update t1, t2 set t1.b=1 where t1.a=t2.a;
 update t1, t2 set t1.b=(select t3.b from t3 where t1.a=t3.a) where t1.a=t2.a;
 connection root;
@@ -425,15 +423,12 @@ insert into t2 values (10), (20), (30);
 create view v1 as select a as b, a/10 as a from t2;
 
 connect (locker,localhost,root,,test);
-connection locker;
 lock table t1 write;
 
 connect (changer,localhost,root,,test);
-connection changer;
 send alter table t1 add column c int default 100 after a;
 
 connect (updater,localhost,root,,test);
-connection updater;
 # Wait till "alter table t1 ..." of session changer is in work.
 # = There is one session waiting.
 let $wait_condition= select count(*)= 1 from information_schema.processlist
@@ -1014,4 +1009,35 @@ select * from t2;
 drop table t1,t2, t3;
 drop user foo;
 
---echo end of 5.5 tests
+#
+# Another test on not-opening tables unnecessary
+#
+create table t1 (a int, b int);
+create table t2 (c int, d int);
+insert t1 values (1,2),(3,4);
+insert t2 values (5,6),(7,8);
+create table t0 (x int);
+insert t0 values (11), (22);
+create trigger tr1 before update on t2 for each row insert t0 values (new.c);
+connect con1, localhost, root;
+lock table t0 write;
+connection default;
+update t1 join t2 on (a=c+4) set b=d;
+disconnect con1;
+drop table t1, t2, t0;
+
+#
+# MDEV-19521 Update Table Fails with Trigger and Stored Function
+#
+create table t1 (a int, b varchar(50), c varchar(50));
+insert t1 (a,b) values (1,'1'), (2,'2'), (3,'3');
+create function f1() returns varchar(50) return 'result';
+create trigger tr before update on t1 for each row set new.c = (select f1());
+create table t2 select a, b from t1;
+update t1 join t2 using (a) set t1.b = t2.b;
+drop table t1, t2;
+drop function f1;
+
+--echo #
+--echo # end of 5.5 tests
+--echo #
diff --git a/mysql-test/t/multi_update_debug.test b/mysql-test/t/multi_update_debug.test
new file mode 100644
index 00000000000..2da376e1b87
--- /dev/null
+++ b/mysql-test/t/multi_update_debug.test
@@ -0,0 +1,27 @@
+#
+# test MDL backoff-and-retry during multi-update
+#
+source include/have_debug_sync.inc;
+create table t1 (a int, b int);
+create table t2 (c int, d int);
+insert t1 values (1,2),(3,4);
+insert t2 values (5,6),(7,8);
+create table t0 (x int);
+insert t0 values (11), (22);
+create trigger tr1 before update on t1 for each row insert t0 values (new.b);
+
+set debug_sync='open_tables_after_open_and_process_table WAIT_FOR cont';
+send update t1 join t2 on (a=c+4) set b=d;
+
+connect con1, localhost, root;
+let $wait_condition= select count(*) from information_schema.processlist where state = ' debug sync point: open_tables_after_open_and_process_table'
+source include/wait_condition.inc;
+set debug_sync='mdl_acquire_lock_wait SIGNAL cont';
+lock table t1 write, t0 write;
+let $wait_condition= select count(*) from information_schema.processlist where state = 'Waiting for table metadata lock'
+source include/wait_condition.inc;
+disconnect con1;
+connection default;
+reap;
+drop table t1, t2, t0;
+set debug_sync='reset';
diff --git a/mysql-test/t/multi_update_innodb.test b/mysql-test/t/multi_update_innodb.test
index 2e46ee06d4d..04736482011 100644
--- a/mysql-test/t/multi_update_innodb.test
+++ b/mysql-test/t/multi_update_innodb.test
@@ -76,6 +76,28 @@ SELECT * FROM t2;
 
 DROP TABLE t1,t2;
 
+#
+# MDEV-19491 update query stopped working after mariadb upgrade 10.2.23 -> 10.2.24
+#
+create table t1 (id serial, size int(11)) engine=innodb;
+create table t2 (id serial, size int, account_id int) engine=innodb;
+create table t3 (id serial, size int, article_id int) engine=innodb;
+create table t4 (id serial, file_id int, article_id int) engine=innodb;
+insert t1 values(null, 400);
+insert t2 values(null, 0, 1), (null, 1, 1);
+insert t3 values(null, 100, 1);
+insert t4 values(null, 1, 2);
+create trigger file_update_article before update on t3 for each row
+  update t2 set t2.size = new.size where t2.id = new.article_id;
+create trigger article_update_account before update on t2 for each row
+  update t1 set t1.size = t1.size + new.size where t1.id = new.account_id;
+update t3 join t4 on t4.file_id =t3.id and t4.article_id=2 set t3.size=t3.size + 2;
+drop table t1, t2, t3, t4;
+
+--echo #
+--echo # end of 5.5 tests
+--echo #
+
 --echo
 --echo # Bug mdev-5970
 --echo # Bug#13256831 - ERROR 1032 (HY000): CAN'T FIND RECORD
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index 4bd8dfdf058..12c32b53aa6 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -2238,6 +2238,81 @@ explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
 SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
 drop table t1,t2;
 drop view v1;
+
+
+--echo #
+--echo # MDEV-19580: function invocation in the left part of IN subquery
+--echo #
+
+create table t1 (id int, a varchar(50), b int);
+insert into t1 values
+(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1);
+
+create table t2 (id int, a varchar(50), x int);
+insert into t2 values
+(1,'grand',1),(2,'average',1),(3,'serf',0);
+
+create table t3 (d1 date, d2 date, t1_id int, t2_id int );
+insert into t3 values
+('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1),
+('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3);
+
+create table t4 ( id int, a varchar(50) );
+insert into t4 values
+(1,'songwriter'),(2,'song character');
+
+delimiter $$;
+
+create function f1(who int, dt date) returns int
+deterministic
+begin
+    declare result int;
+    select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+    return result;
+end$$
+
+create function f2(who int, dt date) returns int
+begin
+    declare result int;
+    select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who;
+    return result;
+end$$
+
+delimiter ;$$
+
+--echo # Deterministic function in left part of IN subquery: semi-join is OK
+
+let $q1=
+select * from t1
+    left join t4 on t1.b = t4.id
+    where f1(t1.id, '1980-01-01') in (select id from t2 where x=1);
+
+eval $q1;
+eval explain extended $q1;
+
+--echo # Non-deterministic function in left part of IN subq: semi-join is OK
+
+let $q2=
+select * from t1
+    left join t4 on t1.b = t4.id
+    where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+
+eval $q2;
+eval explain extended $q2;
+
+let $q3=
+select t1.*, t4.*,
+       (select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s
+    from t1 left join t4 on t1.b = t4.id
+    where f2(t1.id, '1980-01-01') in (select id from t2 where x=1);
+
+eval $q3;
+eval explain extended $q3;
+
+drop function f1;
+drop function f2;
+drop table t1,t2,t3,t4;
+
 --echo # End of 5.5 tests
 --echo #
 --echo # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT