Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor 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, 2006-08-16 09:37:19-07:00, igor@stripped +4 -0
Fixed bug #18165.
Made [NOT]BETWEEN predicates SARGable in respect to the second and
the third arguments.
mysql-test/r/range.result@stripped, 2006-08-16 09:37:11-07:00, igor@stripped +36 -0
Added a test case to bug #18165.
mysql-test/t/range.test@stripped, 2006-08-16 09:37:11-07:00, igor@stripped +30 -0
Added a test case to bug #18165.
sql/opt_range.cc@stripped, 2006-08-16 09:37:12-07:00, igor@stripped +171 -59
Fixed bug #18165.
Made [NOT]BETWEEN predicates SARGable in respect to the second and
the third arguments.
Put in a separate function called get_full_func_mm_tree the functionality
that builds a conjunction of all SEL_TREEs for a simple predicate of the
form (f op c), where f was a field and c was a constant, applying different
equalities f=f' with f' being another field.
sql/sql_select.cc@stripped, 2006-08-16 09:37:12-07:00, igor@stripped +18 -1
Fixed bug #18165.
Made [NOT]BETWEEN predicates SARGable in respect to the second and
the third arguments.
# 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: igor
# Host: rurik.mysql.com
# Root: /home/igor/mysql-5.0-opt
--- 1.218/sql/opt_range.cc 2006-08-16 09:37:28 -07:00
+++ 1.219/sql/opt_range.cc 2006-08-16 09:37:28 -07:00
@@ -3580,25 +3580,36 @@
break;
case Item_func::BETWEEN:
- if (inv)
- {
- tree= get_ne_mm_tree(param, cond_func, field, cond_func->arguments()[1],
- cond_func->arguments()[2], cmp_type);
- }
- else
+ {
+ int i= (int ) value;
+ if (! i)
{
- tree= get_mm_parts(param, cond_func, field, Item_func::GE_FUNC,
- cond_func->arguments()[1],cmp_type);
- if (tree)
+ if (inv)
{
- tree= tree_and(param, tree, get_mm_parts(param, cond_func, field,
- Item_func::LE_FUNC,
- cond_func->arguments()[2],
- cmp_type));
+ tree= get_ne_mm_tree(param, cond_func, field, cond_func->arguments()[1],
+ cond_func->arguments()[2], cmp_type);
+ }
+ else
+ {
+ tree= get_mm_parts(param, cond_func, field, Item_func::GE_FUNC,
+ cond_func->arguments()[1],cmp_type);
+ if (tree)
+ {
+ tree= tree_and(param, tree, get_mm_parts(param, cond_func, field,
+ Item_func::LE_FUNC,
+ cond_func->arguments()[2],
+ cmp_type));
+ }
}
}
+ else
+ tree= get_mm_parts(param, cond_func, field,
+ (inv ?
+ (i == 1 ? Item_func::GT_FUNC : Item_func::LT_FUNC) :
+ (i == 1 ? Item_func::LE_FUNC : Item_func::GE_FUNC)),
+ cond_func->arguments()[0], cmp_type);
break;
-
+ }
case Item_func::IN_FUNC:
{
Item_func_in *func=(Item_func_in*) cond_func;
@@ -3768,6 +3779,118 @@
DBUG_RETURN(tree);
}
+
+/*
+ Build conjunction of all SEL_TREEs for a simple predicate applying equalities
+
+ SYNOPSIS
+ get_full_func_mm_tree()
+ param PARAM from SQL_SELECT::test_quick_select
+ cond_func item for the predicate
+ field_item field in the predicate
+ value constant in the predicate
+ (for BETWEEN it contains the number of the field argument,
+ for IN it's always 0)
+ inv TRUE <> NOT cond_func is considered
+ (makes sense only when cond_func is BETWEEN or IN)
+
+ DESCRIPTION
+ For a simple SARGable predicate of the form (f op c), where f is a field and
+ c is a constant, the function builds a conjunction of all SEL_TREES that can
+ be obtained by the substitution of f for all different fields equal to f.
+
+ NOTES
+ If the WHERE condition contains a predicate (fi op c),
+ then not only SELL_TREE for this predicate is built, but
+ the trees for the results of substitution of fi for
+ each fj belonging to the same multiple equality as fi
+ are built as well.
+ E.g. for WHERE t1.a=t2.a AND t2.a > 10
+ a SEL_TREE for t2.a > 10 will be built for quick select from t2
+ and
+ a SEL_TREE for t1.a > 10 will be built for quick select from t1.
+
+ A BETWEEN predicate of the form (fi [NOT] BETWEEN c1 AND c2) is treated
+ in a similar way: we build a conjuction of trees for the results
+ of all substitutions of fi for equal fj.
+ Yet a predicate of the form (c BETWEEN f1i AND f2i) is processed
+ differently. It is considered as a conjuction of two SARGable
+ predicates (f1i <= c) and (f2i <=c) and the function get_full_func_mm_tree
+ is called for each of them separately producing trees for
+ AND j (f1j <=c ) and AND j (f2j <= c)
+ After this these two trees are united in one conjunctive tree.
+ It's easy to see that the same tree is obtained for
+ AND j,k (f1j <=c AND f2k<=c)
+ which is equivalent to
+ AND j,k (c BETWEEN f1j AND f2k).
+ The validity of the processing of the predicate (c NOT BETWEEN f1i AND f2i)
+ which equivalent to (f1i > c OR f2i < c) is not so obvious. Here the
+ function get_full_func_mm_tree is called for (f1i > c) and (f2i < c)
+ producing trees for AND j (f1j > c) and AND j (f2j < c). Then this two
+ trees are united in one OR-tree. The expression
+ (AND j (f1j > c) OR AND j (f2j < c)
+ is equivalent to the expression
+ AND j,k (f1j > c OR f2k < c)
+ which is just a translation of
+ AND j,k (c NOT BETWEEN f1j AND f2k)
+
+ In the cases when one of the items f1, f2 is a constant c1 we do not create
+ a tree for it at all. It works for BETWEEN predicates but does not
+ work for NOT BETWEEN predicates as we have to evaluate the expression
+ with it. If it is TRUE then the other tree can be completely ignored.
+ We do not do it now and no trees are built in these cases for
+ NOT BETWEEN predicates.
+
+ As to IN predicates only ones of the form (f IN (c1,...,cn)),
+ where f1 is a field and c1,...,cn are constant, are considered as
+ SARGable. We never try to narrow the index scan using predicates of
+ the form (c IN (c1,...,f,...,cn)).
+
+ RETURN
+ Pointer to the tree representing the built conjunction of SEL_TREEs
+*/
+
+static SEL_TREE *get_full_func_mm_tree(PARAM *param, Item_func *cond_func,
+ Item_field *field_item, Item *value,
+ bool inv)
+{
+ SEL_TREE *tree= 0;
+ SEL_TREE *ftree= 0;
+ table_map ref_tables= 0;
+ table_map param_comp= ~(param->prev_tables | param->read_tables |
+ param->current_table);
+ DBUG_ENTER("get_full_func_mm_tree");
+
+ for (uint i= 0; i < cond_func->arg_count; i++)
+ {
+ Item *arg= cond_func->arguments()[i]->real_item();
+ if (arg != field_item)
+ ref_tables|= arg->used_tables();
+ }
+ Field *field= field_item->field;
+ Item_result cmp_type= field->cmp_type();
+ if (!((ref_tables | field->table->map) & param_comp))
+ ftree= get_func_mm_tree(param, cond_func, field, value, cmp_type, inv);
+ Item_equal *item_equal= field_item->item_equal;
+ if (item_equal)
+ {
+ Item_equal_iterator it(*item_equal);
+ Item_field *item;
+ while ((item= it++))
+ {
+ Field *f= item->field;
+ if (field->eq(f))
+ continue;
+ if (!((ref_tables | f->table->map) & param_comp))
+ {
+ tree= get_func_mm_tree(param, cond_func, f, value, cmp_type, inv);
+ ftree= !ftree ? tree : tree_and(param, ftree, tree);
+ }
+ }
+ }
+ DBUG_RETURN(ftree);
+}
+
/* make a select tree of all keys in condition */
static SEL_TREE *get_mm_tree(PARAM *param,COND *cond)
@@ -3776,7 +3899,7 @@
SEL_TREE *ftree= 0;
Item_field *field_item= 0;
bool inv= FALSE;
- Item *value;
+ Item *value= 0;
DBUG_ENTER("get_mm_tree");
if (cond->type() == Item::COND_ITEM)
@@ -3856,10 +3979,37 @@
switch (cond_func->functype()) {
case Item_func::BETWEEN:
- if (cond_func->arguments()[0]->real_item()->type() != Item::FIELD_ITEM)
- DBUG_RETURN(0);
- field_item= (Item_field*) (cond_func->arguments()[0]->real_item());
- value= NULL;
+ if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM)
+ {
+ field_item= (Item_field*) (cond_func->arguments()[0]->real_item());
+ ftree= get_full_func_mm_tree(param, cond_func, field_item, NULL, inv);
+ }
+
+ /*
+ Concerning the code below see the NOTES section in
+ the comments for the function get_full_func_mm_tree()
+ */
+ for (uint i= 1 ; i < cond_func->arg_count ; i++)
+ {
+
+ if (cond_func->arguments()[i]->real_item()->type() == Item::FIELD_ITEM)
+ {
+ field_item= (Item_field*) (cond_func->arguments()[i]->real_item());
+ SEL_TREE *tmp= get_full_func_mm_tree(param, cond_func,
+ field_item, (Item*) i, inv);
+ if (inv)
+ tree= !tree ? tmp : tree_or(param, tree, tmp);
+ else
+ tree= tree_and(param, tree, tmp);
+ }
+ else if (inv)
+ {
+ tree= 0;
+ break;
+ }
+ }
+
+ ftree = tree_and(param, ftree, tree);
break;
case Item_func::IN_FUNC:
{
@@ -3867,7 +4017,7 @@
if (func->key_item()->real_item()->type() != Item::FIELD_ITEM)
DBUG_RETURN(0);
field_item= (Item_field*) (func->key_item()->real_item());
- value= NULL;
+ ftree= get_full_func_mm_tree(param, cond_func, field_item, NULL, inv);
break;
}
case Item_func::MULT_EQUAL_FUNC:
@@ -3906,47 +4056,9 @@
}
else
DBUG_RETURN(0);
+ ftree= get_full_func_mm_tree(param, cond_func, field_item, value, inv);
}
- /*
- If the where condition contains a predicate (ti.field op const),
- then not only SELL_TREE for this predicate is built, but
- the trees for the results of substitution of ti.field for
- each tj.field belonging to the same multiple equality as ti.field
- are built as well.
- E.g. for WHERE t1.a=t2.a AND t2.a > 10
- a SEL_TREE for t2.a > 10 will be built for quick select from t2
- and
- a SEL_TREE for t1.a > 10 will be built for quick select from t1.
- */
-
- for (uint i= 0; i < cond_func->arg_count; i++)
- {
- Item *arg= cond_func->arguments()[i]->real_item();
- if (arg != field_item)
- ref_tables|= arg->used_tables();
- }
- Field *field= field_item->field;
- Item_result cmp_type= field->cmp_type();
- if (!((ref_tables | field->table->map) & param_comp))
- ftree= get_func_mm_tree(param, cond_func, field, value, cmp_type, inv);
- Item_equal *item_equal= field_item->item_equal;
- if (item_equal)
- {
- Item_equal_iterator it(*item_equal);
- Item_field *item;
- while ((item= it++))
- {
- Field *f= item->field;
- if (field->eq(f))
- continue;
- if (!((ref_tables | f->table->map) & param_comp))
- {
- tree= get_func_mm_tree(param, cond_func, f, value, cmp_type, inv);
- ftree= !ftree ? tree : tree_and(param, ftree, tree);
- }
- }
- }
DBUG_RETURN(ftree);
}
--- 1.442/sql/sql_select.cc 2006-08-16 09:37:28 -07:00
+++ 1.443/sql/sql_select.cc 2006-08-16 09:37:29 -07:00
@@ -2796,11 +2796,12 @@
break;
case Item_func::OPTIMIZE_KEY:
{
+ Item **values;
// BETWEEN, IN, NE
if (cond_func->key_item()->real_item()->type() == Item::FIELD_ITEM &&
!(cond_func->used_tables() & OUTER_REF_TABLE_BIT))
{
- Item **values= cond_func->arguments()+1;
+ values= cond_func->arguments()+1;
if (cond_func->functype() == Item_func::NE_FUNC &&
cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM &&
!(cond_func->arguments()[0]->used_tables() & OUTER_REF_TABLE_BIT))
@@ -2812,6 +2813,22 @@
0, values,
cond_func->argument_count()-1,
usable_tables);
+ }
+ if (cond_func->functype() == Item_func::BETWEEN)
+ {
+ values= cond_func->arguments();
+ for (uint i= 1 ; i < cond_func->argument_count() ; i++)
+ {
+ Item_field *field_item;
+ if (cond_func->arguments()[i]->real_item()->type() == Item::FIELD_ITEM
+ &&
+ !(cond_func->arguments()[i]->used_tables() & OUTER_REF_TABLE_BIT))
+ {
+ field_item= (Item_field *) (cond_func->arguments()[i]->real_item());
+ add_key_equal_fields(key_fields, *and_level, cond_func,
+ field_item, 0, values, 1, usable_tables);
+ }
+ }
}
break;
}
--- 1.47/mysql-test/r/range.result 2006-08-16 09:37:29 -07:00
+++ 1.48/mysql-test/r/range.result 2006-08-16 09:37:29 -07:00
@@ -860,3 +860,39 @@
13
15
drop table t1, t2;
+CREATE TABLE t1 (
+id int NOT NULL DEFAULT '0',
+b int NOT NULL DEFAULT '0',
+c int NOT NULL DEFAULT '0',
+INDEX idx1(b,c), INDEX idx2(c));
+INSERT INTO t1(id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
+INSERT INTO t1(b,c) VALUES (3,4), (3,4);
+SELECT * FROM t1 WHERE b<=3 AND 3<=c;
+id b c
+0 3 4
+0 3 4
+SELECT * FROM t1 WHERE 3 BETWEEN b AND c;
+id b c
+0 3 4
+0 3 4
+EXPLAIN SELECT * FROM t1 WHERE b<=3 AND 3<=c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 3 Using where
+EXPLAIN SELECT * FROM t1 WHERE 3 BETWEEN b AND c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 3 Using where
+SELECT * FROM t1 WHERE 0 < b OR 0 > c;
+id b c
+0 3 4
+0 3 4
+SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c;
+id b c
+0 3 4
+0 3 4
+EXPLAIN SELECT * FROM t1 WHERE 0 < b OR 0 > c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge idx1,idx2 idx1,idx2 4,4 NULL 4 Using sort_union(idx1,idx2); Using where
+EXPLAIN SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge idx1,idx2 idx1,idx2 4,4 NULL 4 Using sort_union(idx1,idx2); Using where
+DROP TABLE t1;
--- 1.40/mysql-test/t/range.test 2006-08-16 09:37:29 -07:00
+++ 1.41/mysql-test/t/range.test 2006-08-16 09:37:29 -07:00
@@ -680,4 +680,34 @@
execute stmt1;
drop table t1, t2;
+
+#
+# Bug #18165: range access for BETWEEN with a constant for the first argument
+#
+
+CREATE TABLE t1 (
+ id int NOT NULL DEFAULT '0',
+ b int NOT NULL DEFAULT '0',
+ c int NOT NULL DEFAULT '0',
+ INDEX idx1(b,c), INDEX idx2(c));
+
+INSERT INTO t1(id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
+
+INSERT INTO t1(b,c) VALUES (3,4), (3,4);
+
+SELECT * FROM t1 WHERE b<=3 AND 3<=c;
+SELECT * FROM t1 WHERE 3 BETWEEN b AND c;
+
+EXPLAIN SELECT * FROM t1 WHERE b<=3 AND 3<=c;
+EXPLAIN SELECT * FROM t1 WHERE 3 BETWEEN b AND c;
+
+SELECT * FROM t1 WHERE 0 < b OR 0 > c;
+SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c;
+
+EXPLAIN SELECT * FROM t1 WHERE 0 < b OR 0 > c;
+EXPLAIN SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c;
+
+DROP TABLE t1;
+
+
# End of 5.0 tests
| Thread |
|---|
| • bk commit into 5.0 tree (igor:1.2259) BUG#18165 | igor | 16 Aug |
