Merge branch '10.0' into 10.1

7 files changed, 128 insertions, 3 deletions

diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 78cdfe6ecb1..312b36c16ca 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -2440,11 +2440,27 @@ CREATE TABLE t1 (b1 BIT NOT NULL);
 INSERT INTO t1 VALUES (0),(1);
 CREATE TABLE t2 (b2 BIT NOT NULL);
 INSERT INTO t2 VALUES (0),(1);
-SET SESSION JOIN_CACHE_LEVEL = 3;
+set @save_join_cache_level= @@join_cache_level;
+SET  @@join_cache_level = 3;
 SELECT t1.b1+'0' , t2.b2 + '0' FROM t1 LEFT JOIN t2 ON b1 = b2;
 t1.b1+'0'	t2.b2 + '0'
 0	0
 1	1
 DROP TABLE t1, t2;
+set @join_cache_level= @save_join_cache_level;
+#
+# MDEV-14779: using left join causes incorrect results with materialization and derived tables
+#
+create table t1(id  int);
+insert into t1 values (1),(2);
+create table t2(sid int, id int);
+insert into t2 values (1,1),(2,2);
+select * from t1 t
+left join (select * from t2 where sid in (select max(sid) from t2 where 0=1 group by id)) r
+on t.id=r.id ;
+id	sid	id
+1	NULL	NULL
+2	NULL	NULL
+drop table t1, t2;
 # end of 5.5 tests
 SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index 861b224043c..a006bafcfc0 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -2451,12 +2451,28 @@ CREATE TABLE t1 (b1 BIT NOT NULL);
 INSERT INTO t1 VALUES (0),(1);
 CREATE TABLE t2 (b2 BIT NOT NULL);
 INSERT INTO t2 VALUES (0),(1);
-SET SESSION JOIN_CACHE_LEVEL = 3;
+set @save_join_cache_level= @@join_cache_level;
+SET  @@join_cache_level = 3;
 SELECT t1.b1+'0' , t2.b2 + '0' FROM t1 LEFT JOIN t2 ON b1 = b2;
 t1.b1+'0'	t2.b2 + '0'
 0	0
 1	1
 DROP TABLE t1, t2;
+set @join_cache_level= @save_join_cache_level;
+#
+# MDEV-14779: using left join causes incorrect results with materialization and derived tables
+#
+create table t1(id  int);
+insert into t1 values (1),(2);
+create table t2(sid int, id int);
+insert into t2 values (1,1),(2,2);
+select * from t1 t
+left join (select * from t2 where sid in (select max(sid) from t2 where 0=1 group by id)) r
+on t.id=r.id ;
+id	sid	id
+1	NULL	NULL
+2	NULL	NULL
+drop table t1, t2;
 # end of 5.5 tests
 SET optimizer_switch=@save_optimizer_switch;
 set join_cache_level=default;
diff --git a/mysql-test/r/ps_qc_innodb.result b/mysql-test/r/ps_qc_innodb.result
new file mode 100644
index 00000000000..775055e858f
--- /dev/null
+++ b/mysql-test/r/ps_qc_innodb.result
@@ -0,0 +1,23 @@
+#
+# MDEV-15492: Subquery crash similar to MDEV-10050
+#
+SET @qcs.save= @@global.query_cache_size, @qct.save= @@global.query_cache_type;
+SET GLOBAL query_cache_size= 512*1024*1024, query_cache_type= ON;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+CREATE TABLE t2 (b INT) ENGINE=InnoDB;
+CREATE VIEW v AS select a from t1 join t2;
+PREPARE stmt1 FROM "SELECT * FROM t1 WHERE a in (SELECT a FROM v)";
+PREPARE stmt2 FROM "SELECT * FROM t1 WHERE a in (SELECT a FROM v)";
+EXECUTE stmt2;
+a
+EXECUTE stmt1;
+a
+INSERT INTO t2 VALUES (0);
+EXECUTE stmt1;
+a
+START TRANSACTION;
+EXECUTE stmt1;
+a
+DROP VIEW v;
+DROP TABLE t1, t2;
+SET GLOBAL query_cache_size= @qcs.save, query_cache_type= @qct.save;
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 4e70615120f..2ae8ff3ba1b 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2489,6 +2489,17 @@ FROM t2 WHERE b <= 'quux' GROUP BY field;
 field	COUNT(DISTINCT c)
 0	1
 drop table t1,t2;
+#
+# MDEV-15555: select from DUAL where false yielding wrong result when in a IN
+#
+explain 
+SELECT 2 IN (SELECT 2 from DUAL WHERE 1 != 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+SELECT 2 IN (SELECT 2 from DUAL WHERE 1 != 1);
+2 IN (SELECT 2 from DUAL WHERE 1 != 1)
+0
 SET optimizer_switch= @@global.optimizer_switch;
 set @@tmp_table_size= @@global.tmp_table_size;
 #
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index acbe19b5e87..34b0551a32e 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -1972,9 +1972,25 @@ INSERT INTO t1 VALUES (0),(1);
 CREATE TABLE t2 (b2 BIT NOT NULL);
 INSERT INTO t2 VALUES (0),(1);
 
-SET SESSION JOIN_CACHE_LEVEL = 3;
+set @save_join_cache_level= @@join_cache_level;
+SET  @@join_cache_level = 3;
 SELECT t1.b1+'0' , t2.b2 + '0' FROM t1 LEFT JOIN t2 ON b1 = b2;
 DROP TABLE t1, t2;
+set @join_cache_level= @save_join_cache_level;
+
+--echo #
+--echo # MDEV-14779: using left join causes incorrect results with materialization and derived tables
+--echo #
+
+create table t1(id  int);
+insert into t1 values (1),(2);
+create table t2(sid int, id int);
+insert into t2 values (1,1),(2,2);
+
+select * from t1 t
+ left join (select * from t2 where sid in (select max(sid) from t2 where 0=1 group by id)) r
+ on t.id=r.id ;
+drop table t1, t2;
 
 --echo # end of 5.5 tests
 
diff --git a/mysql-test/t/ps_qc_innodb.test b/mysql-test/t/ps_qc_innodb.test
new file mode 100644
index 00000000000..e09a2bf4070
--- /dev/null
+++ b/mysql-test/t/ps_qc_innodb.test
@@ -0,0 +1,35 @@
+--source include/have_query_cache.inc
+--source include/have_innodb.inc
+
+--echo #
+--echo # MDEV-15492: Subquery crash similar to MDEV-10050
+--echo #
+
+SET @qcs.save= @@global.query_cache_size, @qct.save= @@global.query_cache_type;
+SET GLOBAL query_cache_size= 512*1024*1024, query_cache_type= ON;
+
+--connect (con1,localhost,root,,test)
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+CREATE TABLE t2 (b INT) ENGINE=InnoDB;
+CREATE VIEW v AS select a from t1 join t2;
+
+PREPARE stmt1 FROM "SELECT * FROM t1 WHERE a in (SELECT a FROM v)";
+
+--connect (con2,localhost,root,,test)
+PREPARE stmt2 FROM "SELECT * FROM t1 WHERE a in (SELECT a FROM v)";
+EXECUTE stmt2;
+
+--connection con1
+EXECUTE stmt1;
+INSERT INTO t2 VALUES (0);
+EXECUTE stmt1;
+START TRANSACTION;
+EXECUTE stmt1;
+
+# Cleanup
+--disconnect con1
+--disconnect con2
+--connection default
+DROP VIEW v;
+DROP TABLE t1, t2;
+SET GLOBAL query_cache_size= @qcs.save, query_cache_type= @qct.save;
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index d4ccbcf6f66..77ea117b15f 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -2032,6 +2032,14 @@ SELECT ( SELECT COUNT(*) FROM t1 WHERE a = c ) AS field, COUNT(DISTINCT c)
 FROM t2 WHERE b <= 'quux' GROUP BY field;
 drop table t1,t2;
 
+--echo #
+--echo # MDEV-15555: select from DUAL where false yielding wrong result when in a IN
+--echo #
+
+explain 
+SELECT 2 IN (SELECT 2 from DUAL WHERE 1 != 1);
+SELECT 2 IN (SELECT 2 from DUAL WHERE 1 != 1);
+
 SET optimizer_switch= @@global.optimizer_switch;
 set @@tmp_table_size= @@global.tmp_table_size;