Fixed LP bug #887496.

This bug in the function Loose_scan_opt::check_ref_access_part1 could lead
to choosing an invalid execution plan employing a loose scan access to a
semi-join table even in the cases when such access could not be used at all.
This could result in wrong answers for some queries with IN subqueries.

1 files changed, 33 insertions, 0 deletions

diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 6c4f5d30ade..73dcdaf37d0 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -2061,6 +2061,39 @@ a
 3
 set optimizer_switch= @tmp_otimizer_switch;
 drop table t1,t2;
+#
+# Bug #887496: semijoin with IN equality for the second part of an index
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (9), (0), (8), (5);
+CREATE TABLE t2 (a int, b varchar(1), INDEX idx (b,a));
+INSERT INTO t2 VALUES (5,'r'), (5,'z');
+CREATE TABLE t3 (a int, b varchar(1), INDEX idx (b,a));
+INSERT INTO t3 VALUES (5,'r'), (5,'z');
+set @tmp_otimizer_switch= @@optimizer_switch;
+SET SESSION optimizer_switch='semijoin=on,firstmatch=on';
+SET SESSION optimizer_switch='loosescan=off';
+EXPLAIN
+SELECT * FROM t1 WHERE a IN (SELECT t2.a FROM t2,t3 WHERE t2.b = t3.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	idx	idx	9	NULL	2	Using where; Using index; Start temporary
+1	PRIMARY	t3	ref	idx	idx	4	test.t2.b	1	Using index
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; End temporary; Using join buffer (flat, BNL join)
+SELECT * FROM t1 WHERE a IN (SELECT t2.a FROM t2,t3 WHERE t2.b = t3.b);
+a
+5
+SET SESSION optimizer_switch='loosescan=on';
+EXPLAIN
+SELECT * FROM t1 WHERE a IN (SELECT t2.a FROM t2,t3 WHERE t2.b = t3.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	idx	idx	9	NULL	2	Using where; Using index; Start temporary
+1	PRIMARY	t3	ref	idx	idx	4	test.t2.b	1	Using index
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; End temporary; Using join buffer (flat, BNL join)
+SELECT * FROM t1 WHERE a IN (SELECT t2.a FROM t2,t3 WHERE t2.b = t3.b);
+a
+5
+set optimizer_switch= @tmp_otimizer_switch;
+DROP TABLE t1,t2,t3;
 set optimizer_switch=@subselect_sj_tmp;
 #
 # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off