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-15 19:15:52+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
in the case when all right IN operands are not SQL constant expressions
mysql-test/r/func_in.result@stripped, 2007-01-15 19:15:46+02:00, gkodinov@stripped +43 -0
BUG#20420: optimizer reports wrong keys on left join with IN
- test case
mysql-test/t/func_in.test@stripped, 2007-01-15 19:15:47+02:00, gkodinov@stripped +36 -0
BUG#20420: optimizer reports wrong keys on left join with IN
- test case
sql/item_cmpfunc.h@stripped, 2007-01-15 19:15:48+02:00, gkodinov@stripped +5 -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-15 19:15:48 +02:00
@@ -965,6 +965,10 @@ class Item_func_in :public Item_func_opt
{
public:
Item_result cmp_type;
+ /*
+ an array of values when the right hand arguments of IN
+ are all SQL constant and there are no nulls
+ */
in_vector *array;
cmp_item *in_item;
bool have_null;
@@ -990,7 +994,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-15 19:15:46 +02:00
@@ -351,4 +351,47 @@ 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),(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);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t3 index PRIMARY 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)
+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);
+a a b a b a
+3 3 1 3 2 1
+3 3 1 3 2 2
+4 4 1 4 2 1
+4 4 1 4 2 2
+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;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index
+1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
+1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
+2 DEPENDENT SUBQUERY t4 index NULL PRIMARY 4 NULL 2 Using where; Using index
+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 SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
+3
+3
+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-15 19:15:47 +02:00
@@ -254,5 +254,41 @@ 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),(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
| Thread |
|---|
| • bk commit into 5.0 tree (gkodinov:1.2367) BUG#20420 | kgeorge | 15 Jan |
