5.3 merge

1 files changed, 40 insertions, 0 deletions

diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index ca834b21df5..f8ebe0d07a9 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -2671,4 +2671,44 @@ a
 DEALLOCATE PREPARE pstmt;
 DROP VIEW v1;
 DROP TABLE t1, t2;
+#
+# BUG#978479: Wrong result (extra rows) with derived_with_keys+loosescan+semijoin=ON, materialization=OFF
+#
+set @tmp_jcl_978479= @@join_cache_level;
+set join_cache_level=0;
+set @tmp_os_978479= @@optimizer_switch;
+set optimizer_switch = 'derived_with_keys=on,loosescan=on,semijoin=on,materialization=off';
+# Part#1: make sure EXPLAIN is using LooseScan:
+CREATE TABLE t1 ( a INT, b INT );
+INSERT INTO t1 VALUES
+(4,0),(6,8),(3,1),(5,8),(3,9),(2,4),
+(2,6),(9,1),(5,4),(7,7),(5,4);
+CREATE ALGORITHM=TEMPTABLE
+VIEW v1 AS SELECT * FROM t1;
+# This will use LooseScan:
+EXPLAIN
+SELECT * FROM t1 AS t1_1, t1 AS t1_2
+WHERE (t1_1.a, t1_2.a) IN ( SELECT a, b FROM v1 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1	ALL	NULL	NULL	NULL	NULL	11	Using where
+1	PRIMARY	<derived3>	ref	key0	key0	5	test.t1_1.a	2	Start temporary
+1	PRIMARY	t1_2	ALL	NULL	NULL	NULL	NULL	11	Using where; End temporary
+3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	
+SELECT * FROM t1 AS t1_1, t1 AS t1_2
+WHERE (t1_1.a, t1_2.a) IN ( SELECT a, b FROM v1 );
+a	b	a	b
+3	1	9	1
+5	8	4	0
+3	9	9	1
+2	4	6	8
+2	4	4	0
+2	6	6	8
+2	6	4	0
+5	4	4	0
+7	7	7	7
+5	4	4	0
+DROP VIEW v1;
+DROP TABLE t1;
+set @@join_cache_level= @tmp_jcl_978479;
+set @@optimizer_switch= @tmp_os_978479;
 set optimizer_switch=@subselect_sj_tmp;