BUG#20420: optimizer reports wrong keys on left join with IN

 The optimizer needs to evaluate whether predicates are better
 evaluated using an index. IN is one such predicate.
 To qualify an IN predicate must involve a field of the index
 on the left and constant arguments on the right.
 However whether an expression is a constant can be determined only
 by knowing the preceding tables in the join order. 
 Assuming that only IN predicates with expressions on the right that
 are constant for the whole query qualify limits the scope of 
 possible optimizations of the IN predicate (more specifically it
 doesn't allow the "Range checked for each record" optimization for
 such an IN predicate.
 Fixed by not pre-determining the optimizability of the IN predicate
 in the case when all right IN operands are not SQL constant expressions


mysql-test/r/func_in.result:
  BUG#20420: optimizer reports wrong keys on left join with IN
   - test case
mysql-test/t/func_in.test:
  BUG#20420: optimizer reports wrong keys on left join with IN
   - test case
sql/item_cmpfunc.h:
  BUG#20420: optimizer reports wrong keys on left join with IN
   - cannot decide on the optimizability of a function without
     knowing the tables before it in the join order

1 files changed, 36 insertions, 0 deletions

diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test
index d48606ac6e6..8c89aa1e729 100644
--- a/mysql-test/t/func_in.test
+++ b/mysql-test/t/func_in.test
@@ -254,5 +254,41 @@ select some_id from t1 where some_id not in(-4,-1,-4);
 select some_id from t1 where some_id not in(-4,-1,3423534,2342342);
 drop table t1;
 
+#
+# BUG#20420: optimizer reports wrong keys on left join with IN
+#
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
+INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1);
+
+CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));
+INSERT INTO t2 VALUES (3,2),(4,2);
+
+CREATE TABLE t3 (a int PRIMARY KEY);
+INSERT INTO t3 VALUES (1),(2),(3),(4);
+
+CREATE TABLE t4 (a int PRIMARY KEY);
+INSERT INTO t4 VALUES (1),(2);
+
+EXPLAIN SELECT STRAIGHT_JOIN * FROM t3 
+  JOIN t1 ON t3.a=t1.a 
+  JOIN t2 ON t3.a=t2.a
+  JOIN t4 WHERE t4.a IN (t1.b, t2.b);
+
+SELECT STRAIGHT_JOIN * FROM t3 
+  JOIN t1 ON t3.a=t1.a 
+  JOIN t2 ON t3.a=t2.a
+  JOIN t4 WHERE t4.a IN (t1.b, t2.b);
+
+EXPLAIN SELECT STRAIGHT_JOIN 
+   (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 
+  FROM t3, t1, t2
+  WHERE t3.a=t1.a AND t3.a=t2.a;
+
+SELECT STRAIGHT_JOIN 
+   (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 
+  FROM t3, t1, t2
+  WHERE t3.a=t1.a AND t3.a=t2.a;
+
+DROP TABLE t1,t2,t3,t4;  
 
 --echo End of 5.0 tests