bk commit into 5.0 tree (gkodinov:1.2367) BUG#20420

[kgeorge]

Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-01-10 12:22:53+02:00, gkodinov@stripped +3 -0
  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
   based on the whole-query-constants only.

  mysql-test/r/func_in.result@stripped, 2007-01-10 12:22:47+02:00, gkodinov@stripped +16 -0
    BUG#20420: optimizer reports wrong keys on left join with IN
     - test case

  mysql-test/t/func_in.test@stripped, 2007-01-10 12:22:47+02:00, gkodinov@stripped +19 -0
    BUG#20420: optimizer reports wrong keys on left join with IN
     - test case

  sql/item_cmpfunc.h@stripped, 2007-01-10 12:22:48+02:00, gkodinov@stripped +1 -1
    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

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	gkodinov
# Host:	macbook.gmz
# Root:	/Users/kgeorge/mysql/work/B20420-5.0-opt

--- 1.135/sql/item_cmpfunc.h	2006-12-23 21:04:24 +02:00
+++ 1.136/sql/item_cmpfunc.h	2007-01-10 12:22:48 +02:00
@@ -990,7 +990,7 @@ public:
     DBUG_VOID_RETURN;
   }
   optimize_type select_optimize() const
-    { return array ? OPTIMIZE_KEY : OPTIMIZE_NONE; }
+    { return OPTIMIZE_KEY; }
   void print(String *str);
   enum Functype functype() const { return IN_FUNC; }
   const char *func_name() const { return " IN "; }

--- 1.29/mysql-test/r/func_in.result	2006-11-16 12:25:53 +02:00
+++ 1.30/mysql-test/r/func_in.result	2007-01-10 12:22:47 +02:00
@@ -351,4 +351,20 @@ some_id
 1
 2
 drop table t1;
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
+INSERT INTO t1 VALUES (1,1),(2,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 * FROM t3 LEFT JOIN t1 ON t3.a=t1.a LEFT JOIN t2 ON t3.a=t2.a
+JOIN t4 WHERE t4.a IN (t1.b, t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	index	NULL	PRIMARY	4	NULL	4	Using index
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	2	Range checked for each record (index map: 0x1)
+DROP TABLE t1,t2,t3,t4;
 End of 5.0 tests

--- 1.23/mysql-test/t/func_in.test	2006-11-16 12:25:53 +02:00
+++ 1.24/mysql-test/t/func_in.test	2007-01-10 12:22:47 +02:00
@@ -254,5 +254,24 @@ select some_id from t1 where some_id not
 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);
+
+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 * FROM t3 LEFT JOIN t1 ON t3.a=t1.a LEFT JOIN t2 ON t3.a=t2.a
+  JOIN t4 WHERE t4.a IN (t1.b, t2.b);
+
+DROP TABLE t1,t2,t3,t4;
 
 --echo End of 5.0 tests