Merge

5 files changed, 87 insertions, 4 deletions

diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 7d13bce1f85..8c55df90f62 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -1274,11 +1274,11 @@ insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B;
 create table t2 as select * from t1;
 explain select * from t2 where a in (select b from t1 where a=3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	a	a	5	NULL	8	Using where; Using index; LooseScan
+1	PRIMARY	t1	ref	a	a	5	const	8	Using index; LooseScan
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
 explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	a	a	5	NULL	8	Using where; Using index; LooseScan
+1	PRIMARY	t1	ref	a	a	5	const	8	Using index; LooseScan
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
 drop table t1,t2;
 set @@optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result
index 5ad5878623d..66b305bcf14 100644
--- a/mysql-test/r/subselect3_jcl6.result
+++ b/mysql-test/r/subselect3_jcl6.result
@@ -1283,11 +1283,11 @@ insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B;
 create table t2 as select * from t1;
 explain select * from t2 where a in (select b from t1 where a=3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	a	a	5	NULL	8	Using where; Using index; LooseScan
+1	PRIMARY	t1	ref	a	a	5	const	8	Using index; LooseScan
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
 explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	a	a	5	NULL	8	Using where; Using index; LooseScan
+1	PRIMARY	t1	ref	a	a	5	const	8	Using index; LooseScan
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
 drop table t1,t2;
 set @@optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index a7b3cffb8d2..82804681eef 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -2149,4 +2149,31 @@ a	a
 SET optimizer_prune_level=DEFAULT;
 SET optimizer_switch=@tmp_optimizer_switch;
 DROP TABLE t1,t2,t3,t4;
+#
+# BUG#901399: Wrong result (extra row) with semijoin=ON, materialization=OFF, optimizer_prune_level=0
+#
+set @opl_901399= @@optimizer_prune_level;
+set @os_091399= @@optimizer_switch;
+SET optimizer_prune_level=0;
+SET optimizer_switch = 'materialization=off';
+CREATE TABLE t1 ( c INT ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(0),(1),(2),(3),(4),(5),
+(6),(7),(8),(9),(10),(11),(12);
+CREATE TABLE t2 ( a INT, b INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3,20),(2,21),(3,22);
+SELECT *
+FROM t1 AS alias1, t1 AS alias2
+WHERE ( alias1.c, alias2.c )
+IN (
+SELECT alias3.a, alias3.a
+FROM t2 AS alias3, t2 alias4
+WHERE alias3.b = alias4.b
+);
+c	c
+2	2
+3	3
+set optimizer_prune_level= @opl_901399;
+set optimizer_switch= @os_091399;
+DROP TABLE t1,t2;
 set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 8de5af4cf56..7a3fe20a4e5 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -2155,6 +2155,33 @@ a	a
 SET optimizer_prune_level=DEFAULT;
 SET optimizer_switch=@tmp_optimizer_switch;
 DROP TABLE t1,t2,t3,t4;
+#
+# BUG#901399: Wrong result (extra row) with semijoin=ON, materialization=OFF, optimizer_prune_level=0
+#
+set @opl_901399= @@optimizer_prune_level;
+set @os_091399= @@optimizer_switch;
+SET optimizer_prune_level=0;
+SET optimizer_switch = 'materialization=off';
+CREATE TABLE t1 ( c INT ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(0),(1),(2),(3),(4),(5),
+(6),(7),(8),(9),(10),(11),(12);
+CREATE TABLE t2 ( a INT, b INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3,20),(2,21),(3,22);
+SELECT *
+FROM t1 AS alias1, t1 AS alias2
+WHERE ( alias1.c, alias2.c )
+IN (
+SELECT alias3.a, alias3.a
+FROM t2 AS alias3, t2 alias4
+WHERE alias3.b = alias4.b
+);
+c	c
+2	2
+3	3
+set optimizer_prune_level= @opl_901399;
+set optimizer_switch= @os_091399;
+DROP TABLE t1,t2;
 set optimizer_switch=@subselect_sj_tmp;
 #
 # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index 4f8ed7f1643..55074787700 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -1967,5 +1967,34 @@ SET optimizer_switch=@tmp_optimizer_switch;
 
 DROP TABLE t1,t2,t3,t4;
 
+--echo #
+--echo # BUG#901399: Wrong result (extra row) with semijoin=ON, materialization=OFF, optimizer_prune_level=0
+--echo #
+set @opl_901399= @@optimizer_prune_level;
+set @os_091399= @@optimizer_switch;
+SET optimizer_prune_level=0;
+SET optimizer_switch = 'materialization=off';
+
+CREATE TABLE t1 ( c INT ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+  (0),(1),(2),(3),(4),(5),
+  (6),(7),(8),(9),(10),(11),(12);
+CREATE TABLE t2 ( a INT, b INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3,20),(2,21),(3,22);
+
+SELECT *
+FROM t1 AS alias1, t1 AS alias2
+WHERE ( alias1.c, alias2.c )
+  IN (
+      SELECT alias3.a, alias3.a
+        FROM t2 AS alias3, t2 alias4
+        WHERE alias3.b = alias4.b
+     );
+set optimizer_prune_level= @opl_901399;
+set optimizer_switch= @os_091399;
+
+DROP TABLE t1,t2;
+
+
 # The following command must be the last one the file 
 set optimizer_switch=@subselect_sj_tmp;