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-11-16 14:28:07+02:00, gkodinov@stripped +6 -0
Bug #30788: Inconsistent retrieval of char/varchar
Index lookup does not always guarantee that we can
simply remove the relevant conditions from the WHERE
clause. Reasons can be e.g. conversion errors,
partial indexes etc.
The optimizer was removing these parts of the WHERE
condition without any further checking.
This leads to "false positives" when using indexes.
Fixed by checking the index reference conditions
(using WHERE) when using indexes with sub-queries.
mysql-test/r/key.result@stripped, 2007-11-16 14:28:06+02:00, gkodinov@stripped +31 -0
Bug #30788: test case
mysql-test/r/ndb_subquery.result@stripped, 2007-11-16 14:28:06+02:00, gkodinov@stripped +3 -3
Bug #30788: using where
mysql-test/r/subselect.result@stripped, 2007-11-16 14:28:06+02:00, gkodinov@stripped +8 -8
Bug #30788: using where
mysql-test/r/subselect3.result@stripped, 2007-11-16 14:28:06+02:00, gkodinov@stripped +1 -1
Bug #30788: using where
mysql-test/t/key.test@stripped, 2007-11-16 14:28:06+02:00, gkodinov@stripped +25 -0
Bug #30788: test case
sql/sql_select.cc@stripped, 2007-11-16 14:28:06+02:00, gkodinov@stripped +11 -11
Bug #30788: Don't remove the WHERE when using index lookup
with subqueries.
diff -Nrup a/mysql-test/r/key.result b/mysql-test/r/key.result
--- a/mysql-test/r/key.result 2007-09-20 11:54:44 +03:00
+++ b/mysql-test/r/key.result 2007-11-16 14:28:06 +02:00
@@ -462,4 +462,35 @@ EXPLAIN SELECT MAX(a) FROM t1 FORCE INDE
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1
DROP TABLE t1;
+CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
+INSERT INTO t1 VALUES ('a', 'aa');
+INSERT INTO t1 VALUES ('a', 'aaa');
+SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
+a b
+CREATE INDEX I1 ON t1 (a);
+CREATE INDEX I2 ON t1 (b);
+EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 2 Using index; Using where
+SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
+a b
+CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
+INSERT INTO t2 SELECT * FROM t1;
+CREATE INDEX I1 ON t2 (a);
+CREATE INDEX I2 ON t2 (b);
+EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY t2 index_subquery I1 I1 4 func 2 Using index; Using where
+SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
+a b
+EXPLAIN
+SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 2 Using index; Using where
+SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
+a b
+DROP TABLE t1,t2;
End of 5.0 tests.
diff -Nrup a/mysql-test/r/ndb_subquery.result b/mysql-test/r/ndb_subquery.result
--- a/mysql-test/r/ndb_subquery.result 2007-01-12 22:22:36 +02:00
+++ b/mysql-test/r/ndb_subquery.result 2007-11-16 14:28:06 +02:00
@@ -9,7 +9,7 @@ insert into t2 values (1,1,1),(2,2,2),(3
explain select * from t2 where p NOT IN (select p from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where
-2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1
+2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using where
select * from t2 where p NOT IN (select p from t1) order by p;
p u o
4 4 4
@@ -17,7 +17,7 @@ p u o
explain select * from t2 where p NOT IN (select u from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where
-2 DEPENDENT SUBQUERY t1 unique_subquery u u 4 func 1
+2 DEPENDENT SUBQUERY t1 unique_subquery u u 4 func 1 Using where
select * from t2 where p NOT IN (select u from t1) order by p;
p u o
4 4 4
@@ -25,7 +25,7 @@ p u o
explain select * from t2 where p NOT IN (select o from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where
-2 DEPENDENT SUBQUERY t1 index_subquery o o 4 func 1
+2 DEPENDENT SUBQUERY t1 index_subquery o o 4 func 1 Using where
select * from t2 where p NOT IN (select o from t1) order by p;
p u o
4 4 4
diff -Nrup a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
--- a/mysql-test/r/subselect.result 2007-06-29 10:39:15 +03:00
+++ b/mysql-test/r/subselect.result 2007-11-16 14:28:06 +02:00
@@ -1295,9 +1295,9 @@ a
explain extended select * from t2 where t2.a in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index
+2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY)))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
2
@@ -1307,7 +1307,7 @@ id select_type table type possible_keys
1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 Using where; Using index
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using where
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where (`test`.`t1`.`b` <> 30))))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
a
2
@@ -1334,9 +1334,9 @@ a
explain extended select * from t2 where t2.a in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 Using index
+2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 Using index; Using where
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a)))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
2
@@ -1346,7 +1346,7 @@ id select_type table type possible_keys
1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 Using index; Using where
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where (`test`.`t1`.`b` <> 30))))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
a
2
@@ -1373,7 +1373,7 @@ id select_type table type possible_keys
1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 Using index; Using where
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where (`test`.`t1`.`b` <> 30))))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))
drop table t1, t2, t3;
create table t1 (a int, b int);
create table t2 (a int, b int);
@@ -1737,7 +1737,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where
Warnings:
-Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where (`test`.`t1`.`id` < 8))))))
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`)))))))
explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY tt ALL NULL NULL NULL NULL 12 Using where
diff -Nrup a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
--- a/mysql-test/r/subselect3.result 2007-05-21 10:21:54 +03:00
+++ b/mysql-test/r/subselect3.result 2007-11-16 14:28:06 +02:00
@@ -757,6 +757,6 @@ a
EXPLAIN SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where
-2 DEPENDENT SUBQUERY t2 unique_subquery PRIMARY PRIMARY 4 func 1 Using index
+2 DEPENDENT SUBQUERY t2 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where
DROP TABLE t1;
End of 5.0 tests
diff -Nrup a/mysql-test/t/key.test b/mysql-test/t/key.test
--- a/mysql-test/t/key.test 2007-05-22 15:58:29 +03:00
+++ b/mysql-test/t/key.test 2007-11-16 14:28:06 +02:00
@@ -443,4 +443,29 @@ ALTER TABLE t1 DISABLE KEYS;
EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a);
DROP TABLE t1;
+#
+# Bug #30788: Inconsistent retrieval of char/varchar
+#
+
+CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
+INSERT INTO t1 VALUES ('a', 'aa');
+INSERT INTO t1 VALUES ('a', 'aaa');
+SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
+CREATE INDEX I1 ON t1 (a);
+CREATE INDEX I2 ON t1 (b);
+EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
+SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
+
+CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
+INSERT INTO t2 SELECT * FROM t1;
+CREATE INDEX I1 ON t2 (a);
+CREATE INDEX I2 ON t2 (b);
+EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
+SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
+EXPLAIN
+SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
+SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
+
+DROP TABLE t1,t2;
+
--echo End of 5.0 tests.
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc 2007-10-23 16:48:56 +03:00
+++ b/sql/sql_select.cc 2007-11-16 14:28:06 +02:00
@@ -217,6 +217,7 @@ static void select_describe(JOIN *join,
bool distinct, const char *message=NullS);
static Item *remove_additional_cond(Item* conds);
static void add_group_and_distinct_keys(JOIN *join, JOIN_TAB *join_tab);
+static bool test_if_ref(Item_field *left_item,Item *right_item);
/*
@@ -673,9 +674,6 @@ err:
without "checking NULL", remove the predicates that were pushed down
into the subquery.
- We can remove the equalities that will be guaranteed to be true by the
- fact that subquery engine will be using index lookup.
-
If the subquery compares scalar values, we can remove the condition that
was wrapped into trig_cond (it will be checked when needed by the subquery
engine)
@@ -685,6 +683,12 @@ err:
and non-NULL values, we'll do a full table scan and will rely on the
equalities corresponding to non-NULL parts of left tuple to filter out
non-matching records.
+
+ TODO: We can remove the equalities that will be guaranteed to be true by the
+ fact that subquery engine will be using index lookup. This must be done only
+ for cases where there are no conversion errors of significance, e.g. 257
+ that is searched in a byte. But this requires homogenization of the return
+ codes of all Field*::store() methods.
*/
void JOIN::remove_subq_pushed_predicates(Item **where)
@@ -692,17 +696,13 @@ void JOIN::remove_subq_pushed_predicates
if (conds->type() == Item::FUNC_ITEM &&
((Item_func *)this->conds)->functype() == Item_func::EQ_FUNC &&
((Item_func *)conds)->arguments()[0]->type() == Item::REF_ITEM &&
- ((Item_func *)conds)->arguments()[1]->type() == Item::FIELD_ITEM)
+ ((Item_func *)conds)->arguments()[1]->type() == Item::FIELD_ITEM &&
+ test_if_ref ((Item_field *)((Item_func *)conds)->arguments()[1],
+ ((Item_func *)conds)->arguments()[0]))
{
*where= 0;
return;
}
- if (conds->type() == Item::COND_ITEM &&
- ((class Item_func *)this->conds)->functype() ==
- Item_func::COND_AND_FUNC)
- {
- *where= remove_additional_cond(conds);
- }
}
@@ -1219,7 +1219,7 @@ JOIN::optimize()
{
if (!having)
{
- Item *where= 0;
+ Item *where= conds;
if (join_tab[0].type == JT_EQ_REF &&
join_tab[0].ref.items[0]->name == in_left_expr_name)
{
| Thread |
|---|
| • bk commit into 5.0 tree (gkodinov:1.2549) BUG#30788 | kgeorge | 16 Nov |
