Merge

5 files changed, 134 insertions, 5 deletions

diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index e74e4f4b5c2..db0433ce48e 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -1749,7 +1749,7 @@ INSERT INTO t3 VALUES (5,0),(7,0);
 CREATE TABLE t4 (a int);
 INSERT INTO t4 VALUES (2),(8);
 
-set @@optimizer_switch='in_to_exists=on,materialization=off,subquery_cache=off';
+set @@optimizer_switch='semijoin=off,in_to_exists=on,materialization=off,subquery_cache=off';
 
 SELECT * FROM t2
 WHERE t2.b IN (SELECT b FROM t3 WHERE t3.a = t2.a AND a < SOME (SELECT * FROM t4))
diff --git a/mysql-test/t/subselect_extra.test b/mysql-test/t/subselect_extra.test
index 466f254e6ad..ed1c0ef7e4d 100644
--- a/mysql-test/t/subselect_extra.test
+++ b/mysql-test/t/subselect_extra.test
@@ -9,11 +9,9 @@
 
 --disable_warnings
 drop table if exists t1,t2,t3,t4;
+drop view if exists v1,v2,v3;
 --enable_warnings
 
-set @subselect_extra_tmp=@@optimizer_switch;
-set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
-
 
 --echo # From explain.test:
 --echo #
@@ -273,6 +271,123 @@ EXPLAIN SELECT 1 FROM t2 WHERE a IN
   (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
 DROP TABLE t1, t2;
 
+--echo # 
+--echo # From derived_view.test
+--echo #
+set @tmp_subselect_extra_derived=@@optimizer_switch;
+set optimizer_switch='derived_merge=on,derived_with_keys=on';
+
+--echo #
+--echo # LP bug #806504: right join over a view/derived table           
+--echo #
+
+CREATE TABLE t1 (a int, b int) ;
+INSERT INTO t1 VALUES (0,0);
+
+CREATE TABLE t2 (a int) ;
+INSERT INTO t2 VALUES (0), (0);
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+SELECT * FROM t2 RIGHT JOIN (SELECT * FROM t1) AS t ON t.a != 0
+  WHERE t.a IN (SELECT b FROM t1);
+EXPLAIN EXTENDED
+SELECT * FROM t2 RIGHT JOIN (SELECT * FROM t1) AS t ON t.a != 0
+  WHERE t.a IN (SELECT b FROM t1);
+
+SELECT * FROM t2 RIGHT JOIN v1 AS t ON t.a != 0
+  WHERE t.a IN (SELECT b FROM t1);
+EXPLAIN EXTENDED
+SELECT * FROM t2 RIGHT JOIN v1 AS t ON t.a != 0
+  WHERE t.a IN (SELECT b FROM t1);
+
+DROP VIEW v1;
+DROP TABLE t1,t2;
+
+--echo #
+--echo # LP bug #793448: materialized view accessed by two-component key           
+--echo #
+
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (9,3), (2,5);
+
+CREATE TABLE t2 (a int, b int);
+INSERT INTO t2 VALUES (9,3), (3,7), (9,1), (2,5), (2,4), (3,8);
+
+CREATE TABLE t3 (a int, b int);
+INSERT INTO t3 VALUES (10,3), (9,7), (9,1), (2,4);
+
+CREATE VIEW v1(a,b) AS SELECT a, MAX(b) FROM t2 GROUP BY a;
+CREATE VIEW v2(a,b) AS SELECT a,b FROM t2 UNION SELECT a,b FROM t3;
+
+SELECT * FROM v1;
+SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v1);
+EXPLAIN 
+SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v1);
+
+SELECT * FROM v2;
+SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2);
+EXPLAIN 
+SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2);
+
+DROP VIEW v1,v2;
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # LP bug #874006: materialized view used in IN subquery
+--echo #
+
+CREATE TABLE t3 (a int NOT NULL, b varchar(1), c varchar(1));
+INSERT INTO t3 VALUES (19,NULL,NULL), (20,'r','r');
+
+CREATE TABLE t1 (a int, b varchar(1) , c varchar(1));
+INSERT INTO t1 VALUES (1,NULL,NULL), (5,'r','r'), (7,'y','y');
+
+CREATE TABLE t2 (a int NOT NULL , b int, c varchar(1));
+INSERT INTO t2 VALUES (4,3,'r');
+
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+
+SET SESSION optimizer_switch='derived_with_keys=off';
+EXPLAIN
+SELECT * FROM t3 
+  WHERE t3.b IN (SELECT v1.b FROM  v1, t2 
+                   WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c);
+SELECT * FROM t3 
+  WHERE t3.b IN (SELECT v1.b FROM  v1, t2 
+                   WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c);
+
+SET SESSION optimizer_switch='derived_with_keys=on';
+EXPLAIN
+SELECT * FROM t3 
+  WHERE t3.b IN (SELECT v1.b FROM  v1, t2 
+                   WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c);
+SELECT * FROM t3 
+  WHERE t3.b IN (SELECT v1.b FROM  v1, t2 
+                   WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c);
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # LP bug #873263: materialized view used in correlated IN subquery
+--echo #
+
+CREATE TABLE t1 (a int, b int) ;
+INSERT INTO t1 VALUES (5,4), (9,8);
+
+CREATE TABLE t2 (a int, b int) ;
+INSERT INTO t2 VALUES (4,5), (5,1);
+
+CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2;
+
+SET SESSION optimizer_switch='derived_with_keys=on';
+EXPLAIN
+SELECT * FROM t1 WHERE t1.b IN (SELECT v2.a FROM v2 WHERE v2.b = t1.a);
+SELECT * FROM t1 WHERE t1.b IN (SELECT v2.a FROM v2 WHERE v2.b = t1.a);
+
+DROP VIEW v2;
+DROP TABLE t1,t2;
 
-set optimizer_switch= @subselect_extra_tmp;
+set optimizer_switch= @tmp_subselect_extra_derived;
 
diff --git a/mysql-test/t/subselect_extra_no_semijoin.test b/mysql-test/t/subselect_extra_no_semijoin.test
new file mode 100644
index 00000000000..53cc9c083d9
--- /dev/null
+++ b/mysql-test/t/subselect_extra_no_semijoin.test
@@ -0,0 +1,7 @@
+set @subselect_extra_no_sj_tmp=@@optimizer_switch;
+set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
+
+--source t/subselect_extra.test
+
+set optimizer_switch= @subselect_extra_no_sj_tmp;
+
diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test
index af0e4f58e6b..4b511b45e03 100644
--- a/mysql-test/t/subselect_innodb.test
+++ b/mysql-test/t/subselect_innodb.test
@@ -1,5 +1,7 @@
 -- source include/have_innodb.inc
 
+# Note: the tests uses only non-semijoin subqueries so semi-join switch
+#       settings are not relevant.
 set @subselect_innodb_tmp=@@optimizer_switch;
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 --disable_warnings
diff --git a/mysql-test/t/subselect_nulls.test b/mysql-test/t/subselect_nulls.test
index 6de7820872c..4b08e773b17 100644
--- a/mysql-test/t/subselect_nulls.test
+++ b/mysql-test/t/subselect_nulls.test
@@ -5,6 +5,9 @@ drop table if exists x1;
 drop table if exists x2;
 --enable_warnings
 
+set @tmp_subselect_nulls=@@optimizer_switch;
+set optimizer_switch='semijoin=off';
+
 create table x1(k int primary key, d1 int, d2 int);
 create table x2(k int primary key, d1 int, d2 int);
 
@@ -90,5 +93,7 @@ where exists (select *
               from x2
               where x1.d1=x2.d1 and x1.d2=x2.d2);
 
+set optimizer_switch= @tmp_subselect_nulls;
+
 drop table x1;
 drop table x2;