3398 Ole John Aske 2010-12-21
SPJ-scan-scan: Updated cherry picked fix for bug 57030: 'BETWEEN evaluation is incorrect'
after reviewers had some suggestions for improvements / rewrite
modified:
mysql-test/r/range.result
mysql-test/t/range.test
sql/sql_select.cc
3397 Ole John Aske 2010-12-09
SPJ-scan-scan: 'magic patch' to make ctype_cp932_binlog_stm.test pass
modified:
mysql-test/t/ctype_cp932_binlog_stm.test
=== modified file 'mysql-test/r/range.result'
--- a/mysql-test/r/range.result 2010-12-03 14:55:15 +0000
+++ b/mysql-test/r/range.result 2010-12-21 10:38:10 +0000
@@ -1682,7 +1682,7 @@ pk i4
EXPLAIN
SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range i4_uq i4_uq 5 NULL 1 Using where
+1 SIMPLE t1 const i4_uq i4_uq 5 const 1
SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4;
pk i4
1 10
@@ -1752,5 +1752,19 @@ SELECT * FROM t1 WHERE i4 BETWEEN 10 AND
pk i4
1 10
2 20
+EXPLAIN
+SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL i4_uq NULL NULL NULL 3
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 Using where
+SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4;
+pk i4 pk i4
+EXPLAIN
+SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL i4_uq NULL NULL NULL 3
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 Using where
+SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk;
+pk i4 pk i4
DROP TABLE t1;
End of 5.1 tests
=== modified file 'mysql-test/t/range.test'
--- a/mysql-test/t/range.test 2010-12-03 14:55:15 +0000
+++ b/mysql-test/t/range.test 2010-12-21 10:38:10 +0000
@@ -1381,6 +1381,14 @@ EXPLAIN
SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20';
SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20';
+#Should detect the EQ_REF 't2.pk=t1.i4'
+EXPLAIN
+SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4;
+SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4;
+
+EXPLAIN
+SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk;
+SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk;
DROP TABLE t1;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-12-07 15:06:29 +0000
+++ b/sql/sql_select.cc 2010-12-21 10:38:10 +0000
@@ -3424,27 +3424,7 @@ add_key_field(KEY_FIELD **key_fields,uin
eq_func is NEVER true when num_values > 1
*/
if (!eq_func)
- {
- /*
- Additional optimization: if we're processing
- "t.key BETWEEN c1 AND c1" then proceed as if we were processing
- "t.key = c1".
- TODO: This is a very limited fix. A more generic fix is possible.
- There are 2 options:
- A) Make equality propagation code be able to handle BETWEEN
- (including cases like t1.key BETWEEN t2.key AND t3.key)
- B) Make range optimizer to infer additional "t.key = c" equalities
- and use them in equality propagation process (see details in
- OptimizerKBAndTodo)
- */
- if ((cond->functype() != Item_func::BETWEEN) ||
- ((Item_func_between*) cond)->negated ||
- num_values < 2 ||
- !value[0]->eq(value[1], field->binary()))
- return;
- eq_func= TRUE;
- }
-
+ return;
if (field->result_type() == STRING_RESULT)
{
if ((*value)->result_type() != STRING_RESULT)
@@ -3640,9 +3620,65 @@ add_key_fields(JOIN *join, KEY_FIELD **k
case Item_func::OPTIMIZE_KEY:
{
Item **values;
- // BETWEEN, IN, NE
- if (is_local_field (cond_func->key_item()) &&
- !(cond_func->used_tables() & OUTER_REF_TABLE_BIT))
+ /*
+ Build list of possible keys for 'a BETWEEN low AND high'.
+ It is handled similar to the equivalent condition
+ 'a >= low AND a <= high':
+ */
+ if (cond_func->functype() == Item_func::BETWEEN)
+ {
+ Item_field *field_item;
+ bool equal_func= FALSE;
+ uint num_values= 2;
+ values= cond_func->arguments();
+
+ bool binary_cmp= (values[0]->real_item()->type() == Item::FIELD_ITEM)
+ ? ((Item_field*)values[0]->real_item())->field->binary()
+ : TRUE;
+
+ /*
+ Additional optimization: If 'low = high':
+ Handle as if the condition was "t.key = low".
+ */
+ if (!((Item_func_between*)cond_func)->negated &&
+ values[1]->eq(values[2], binary_cmp))
+ {
+ equal_func= TRUE;
+ num_values= 1;
+ }
+
+ /*
+ Append keys for 'field <cmp> value[]' if the
+ condition is of the form::
+ '<field> BETWEEN value[1] AND value[2]'
+ */
+ if (is_local_field (values[0]))
+ {
+ field_item= (Item_field *) (values[0]->real_item());
+ add_key_equal_fields(key_fields, *and_level, cond_func,
+ field_item, equal_func, &values[1],
+ num_values, usable_tables, sargables);
+ }
+ /*
+ Append keys for 'value[0] <cmp> field' if the
+ condition is of the form:
+ 'value[0] BETWEEN field1 AND field2'
+ */
+ for (uint i= 1; i <= num_values; i++)
+ {
+ if (is_local_field (values[i]))
+ {
+ field_item= (Item_field *) (values[i]->real_item());
+ add_key_equal_fields(key_fields, *and_level, cond_func,
+ field_item, equal_func, &values[0],
+ 1, usable_tables, sargables);
+ }
+ }
+ } // if ( ... Item_func::BETWEEN)
+
+ // IN, NE
+ else if (is_local_field (cond_func->key_item()) &&
+ !(cond_func->used_tables() & OUTER_REF_TABLE_BIT))
{
values= cond_func->arguments()+1;
if (cond_func->functype() == Item_func::NE_FUNC &&
@@ -3656,21 +3692,6 @@ add_key_fields(JOIN *join, KEY_FIELD **k
cond_func->argument_count()-1,
usable_tables, sargables);
}
- 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 (is_local_field (cond_func->arguments()[i]))
- {
- 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,
- sargables);
- }
- }
- }
break;
}
case Item_func::OPTIMIZE_OP:
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch(ole.john.aske:3397 to 3398) | Ole John Aske | 21 Dec |
