bzr push into mysql-6.0-opt-subqueries branch (sergefp:2721 to 2722) WL#3985

[Sergey Petrunia]

 2722 Sergey Petrunia	2008-11-16
      WL#3985: Subqueries: smart choice between semi-join and materialization
      - More test coverage
modified:
  mysql-test/r/subselect3.result
  mysql-test/t/subselect3.test

 2721 Sergey Petrunia	2008-11-16
      More subquery tests
modified:
  mysql-test/r/subselect3.result
  mysql-test/t/subselect3.test

=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result	2008-11-16 15:43:20 +0000
+++ b/mysql-test/r/subselect3.result	2008-11-16 19:47:47 +0000
@@ -862,3 +862,18 @@ t22.a in (select t12.a from t11, t12 whe
 a	b	c
 256	67	NULL
 drop table t1, t11, t12, t21, t22;
+create table t1(a int);
+insert into t1 values (0),(1);
+set @@optimizer_switch='no_firstmatch';
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	X	ALL	NULL	NULL	NULL	NULL	2	
+2	DEPENDENT SUBQUERY	Y	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	Z	ALL	NULL	NULL	NULL	NULL	2	Materialize
+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;
+subq
+NULL
+0
+set @@optimizer_switch='';
+drop table t1;

=== modified file 'mysql-test/t/subselect3.test'
--- a/mysql-test/t/subselect3.test	2008-11-16 15:43:20 +0000
+++ b/mysql-test/t/subselect3.test	2008-11-16 19:47:47 +0000
@@ -724,3 +724,18 @@ t22.a in (select t12.a from t11, t12 whe
 
 drop table t1, t11, t12, t21, t22;
 
+#
+# Test sj-materialization re-execution. The test isn't meaningful (materialized
+# table stays the same across all executions) because it's hard to create a
+# dataset that would verify correct re-execution without hitting BUG#31480
+# 
+create table t1(a int);
+insert into t1 values (0),(1);
+
+set @@optimizer_switch='no_firstmatch';
+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;
+set @@optimizer_switch='';
+
+drop table t1;