Merge enabling of materialization=on by default with main tree.

4 files changed, 33 insertions, 5 deletions

diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index c1a9435ef6c..e35aca9b718 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -394,7 +394,7 @@ DROP TABLE t1;
 --echo # LP bug #803851: materialized view + IN->EXISTS                       
 --echo #                 
 
-SET SESSION optimizer_switch='semijoin=off,derived_with_keys=on';
+SET SESSION optimizer_switch='semijoin=off,derived_with_keys=on,materialization=off';
 
 CREATE TABLE t1 (a int, b int);
 INSERT INTO t1 VALUES (2,2), (3,3), (1,1);
@@ -895,7 +895,7 @@ INSERT INTO t3 VALUES
   (14,4,'a'), (15,7,'b'), (16,4,'c'), (17,1,'d'), (18,9,'e'),
   (19,4,'f'), (20,8,'g');
 
-SET SESSION optimizer_switch='derived_merge=on,subquery_cache=off';
+SET SESSION optimizer_switch='derived_merge=on,subquery_cache=off,materialization=off';
 
 --echo # The following two EXPLAINs must return the same execution plan
 EXPLAIN
diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test
index 4691ef37f40..7e4943d85ba 100644
--- a/mysql-test/t/subselect3.test
+++ b/mysql-test/t/subselect3.test
@@ -872,12 +872,17 @@ create table t12 select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) orde
 create table t21 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 asc;
 create table t22 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 desc;
 
+set @@optimizer_switch=@save_optimizer_switch;
+set @@optimizer_switch='materialization=off';
+
 update t22 set c = '2005-12-08 15:58:27' where a = 255;
 explain select t21.* from t21,t22 where t21.a = t22.a and 
 t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
 select t21.* from t21,t22 where t21.a = t22.a and 
 t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
 
+set @@optimizer_switch=@save_optimizer_switch;
+
 drop table t1, t11, t12, t21, t22;
 
 #
@@ -888,7 +893,7 @@ drop table t1, t11, t12, t21, t22;
 create table t1(a int);
 insert into t1 values (0),(1);
 
-set @@optimizer_switch='firstmatch=off';
+set @@optimizer_switch='firstmatch=off,materialization=off';
 explain 
 select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X;
 select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X;
@@ -914,6 +919,9 @@ select * from t1 where 2 in (select a from t0);
 set @@optimizer_switch=@save_optimizer_switch;
 
 
+set @@optimizer_switch=@save_optimizer_switch;
+set @@optimizer_switch='materialization=off';
+
 # 
 # FirstMatch referring to a derived table
 #
@@ -943,6 +951,8 @@ and t4.pk=t1.c);
 
 drop table t1, t3, t4;
 
+set @@optimizer_switch=@save_optimizer_switch;
+
 #
 # Test if we handle duplicate elimination temptable overflowing to disk
 #
@@ -997,6 +1007,9 @@ explain select * from t1 where a in (select a from t1);
 drop table t1;
 set @@optimizer_switch=@save_optimizer_switch;
 
+set @@optimizer_switch=@save_optimizer_switch;
+set @@optimizer_switch='materialization=off';
+
 #
 # SJ-Materialization-scan for non-first table
 #
@@ -1040,6 +1053,8 @@ explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
 
 drop table t1,t2;
 
+set @@optimizer_switch=@save_optimizer_switch;
+
 #
 # Multi-column sj-materialization with lookups
 #
@@ -1048,7 +1063,7 @@ insert into t1 select a,a from t0;
 create table t2 (a int, b int); 
 insert into t2 select A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B;
 
-set @@optimizer_switch='firstmatch=off';
+set @@optimizer_switch='firstmatch=off,materialization=off';
 explain select * from t1 where (a,b) in (select a,b from t2);
 
 # A smallish test if find_best() still works for semi-join optimization:
@@ -1060,6 +1075,7 @@ set @@optimizer_switch=@save_optimizer_switch;
 
 drop table t0, t1, t2;
 
+set @@optimizer_switch='materialization=off';
 
 #
 # Primitive SJ-Materialization tests for DECIMAL and DATE
@@ -1094,6 +1110,8 @@ explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y,
 
 drop table t0,t1,t2;
 
+set @@optimizer_switch=@save_optimizer_switch;
+
 --echo
 --echo BUG#37842: Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307
 --echo
@@ -1150,12 +1168,15 @@ create table t3 (
 insert into t3 values (1,1, 'foo'), (2,2,'bar');
 
 --echo The following must be converted to a semi-join:
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch='materialization=off';
 explain extended SELECT a.idIndividual FROM t1 a 
 WHERE a.idIndividual IN 
 	(	SELECT c.idObj FROM t3 cona 
 		INNER JOIN t2 c ON c.idContact=cona.idContact 
 		WHERE cona.postalStripped='T2H3B2'
 	);
+set @@optimizer_switch=@save_optimizer_switch;
 drop table t1,t2,t3;
 
 --echo #
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index 98d47e7f3ea..0fd89bee42b 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -125,6 +125,9 @@ INSERT INTO t4 VALUES (1), (2), (3);
 CREATE TABLE t5 ( a INT );
 INSERT INTO t5 VALUES (NULL), (2);
 
+SET @old_optimizer_switch = @@session.optimizer_switch;
+SET SESSION optimizer_switch = 'materialization=off,in_to_exists=on,semijoin=off';
+
 --replace_column 1 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x
 EXPLAIN
 SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 );
@@ -166,6 +169,8 @@ SELECT * FROM t1 WHERE b NOT IN ( SELECT c FROM t2 WHERE c = 1 );
 
 SELECT * FROM t1 WHERE NULL NOT IN ( SELECT c FROM t2 WHERE c = 1 AND c <> 1 );
 
+SET SESSION optimizer_switch = @old_optimizer_switch;
+
 DROP TABLE t1, t2, t3, t4, t5;
 
 --echo #
diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test
index 2e5dd71f9af..4ce19012e0c 100644
--- a/mysql-test/t/subselect_mat_cost_bugs.test
+++ b/mysql-test/t/subselect_mat_cost_bugs.test
@@ -323,7 +323,7 @@ create index key1 on t2 (kp1, kp2);
 create index key2 on t2 (kp1);
 create index key3 on t2 (kp2);
 
-set session optimizer_switch='default';
+SET @@optimizer_switch='materialization=off,semijoin=off,in_to_exists=on';
 
 analyze table t2;
 
@@ -331,6 +331,8 @@ explain
 select c1 from t1 where c1 in (select kp1 from t2 where kp2 = 10 and c2 = 4) or c1 > 7;
 select c1 from t1 where c1 in (select kp1 from t2 where kp2 = 10 and c2 = 4) or c1 > 7;
 
+set @@optimizer_switch='default';
+
 drop table t1, t2;
 
 --echo #