5 files changed, 19 insertions, 9 deletions
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index d1ca98962d6..2dd8249c150 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2078,7 +2078,7 @@ SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);
 --error ER_SUBQUERY_NO_1_ROW
 SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);
 
-
+--sorted_result
 SELECT a FROM t1 GROUP BY a
   HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),
                 (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
@@ -2086,7 +2086,7 @@ SELECT a FROM t1 GROUP BY a
 SELECT a FROM t1 GROUP BY a
   HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),
                 (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
-
+--sorted_result
 SELECT a FROM t1 GROUP BY a
   HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
                 (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
@@ -2094,7 +2094,7 @@ SELECT a FROM t1 GROUP BY a
 SELECT a FROM t1 GROUP BY a
   HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
                 (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3;
-
+--sorted_result
 SELECT a FROM t1
   ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),
                   (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
@@ -2102,7 +2102,7 @@ SELECT a FROM t1
 SELECT a FROM t1
   ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),
                   (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
-
+--sorted_result
 SELECT a FROM t1
   ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
                   (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
diff --git a/mysql-test/t/subselect_mat_cost.test b/mysql-test/t/subselect_mat_cost.test
index dcbaec791c0..4de5461481b 100644
--- a/mysql-test/t/subselect_mat_cost.test
+++ b/mysql-test/t/subselect_mat_cost.test
@@ -18,7 +18,6 @@ drop procedure if exists set_all_columns_nullable;
 create table t1 (a1 char(8), a2 char(8), a3 char(8), a4 int);
 insert into t1 values ('1 - 00', '2 - 00', '3 - 00', 0);
 insert into t1 values ('1 - 01', '2 - 01', '3 - 01', 1);
-insert into t1 values ('1 - 02', '2 - 02', '3 - 02', 2);
 
 create table t2 (b1 char(8), b2 char(8), b3 char(8), b4 int);
 insert into t2 values ('1 - 01', '2 - 01', '3 - 01', 1);
@@ -151,6 +150,8 @@ call set_all_columns_not_null();
 call set_all_columns_nullable();
 
 
+insert into t1 values ('1 - 02', '2 - 02', '3 - 02', 2);
+
 -- echo /******************************************************************************
 -- echo 2. Materialization is OFF, in-to-exists is ON, materialization is cheaper.
 -- echo ******************************************************************************/
diff --git a/mysql-test/t/subselect_no_opts.test b/mysql-test/t/subselect_no_opts.test
index a26e8dd4c0d..d72deab45bf 100644
--- a/mysql-test/t/subselect_no_opts.test
+++ b/mysql-test/t/subselect_no_opts.test
@@ -1,6 +1,6 @@
 # 
-# Run subselect.test without semi-join optimization (test materialize)
-#
+# Run subselect.test without semi-join and materialization optimizations
+#  (test in-to-exists)
 set optimizer_switch='materialization=off,semijoin=off';
 
 --source t/subselect.test
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index 18ec4d00677..cb7f9515988 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -311,7 +311,7 @@ FROM t0
 WHERE varchar_nokey  IN (  
   SELECT t1 .varchar_key  from t1
 );
-
+--disable_parsing # wrong duplicate results - LP BUG#702374
 SELECT t0.int_key  
 FROM t0
 WHERE t0.varchar_nokey  IN (  
@@ -325,7 +325,7 @@ WHERE t0.varchar_nokey  IN (
   SELECT t1_1 .varchar_key  
   FROM t1 AS t1_1  JOIN t1 AS t1_2 ON t1_1 .int_key  
 );
-
+--enable_parsing
 DROP TABLE t0, t1, t2;
 
 --echo # End of bug#46550
diff --git a/mysql-test/t/subselect_sj2_mat.test b/mysql-test/t/subselect_sj2_mat.test
new file mode 100644
index 00000000000..643a287a897
--- /dev/null
+++ b/mysql-test/t/subselect_sj2_mat.test
@@ -0,0 +1,9 @@
+# 
+# Run subselect_sj2.test with subquery materialization.
+#
+set optimizer_switch='materialization=on';
+
+--source t/subselect_sj2.test
+
+set optimizer_switch=default;
+select @@optimizer_switch like '%materialization=on%';