From: Ole John Aske Date: December 21 2010 10:38am Subject: bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3397 to 3398) List-Archive: http://lists.mysql.com/commits/127378 Message-Id: <20101221103840.AB399223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 value[]' if the + condition is of the form:: + ' 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] 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).