From: Ole John Aske Date: January 14 2011 3:08pm Subject: bzr push into mysql-5.1-telco-7.0 branch (ole.john.aske:4114 to 4115) List-Archive: http://lists.mysql.com/commits/128805 Message-Id: <20110114150850.F04A8223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 4115 Ole John Aske 2011-01-14 Backport to mysql-5.1-telco-7.0: fix for bug#57030: ('BETWEEN' evaluation is incorrect') Root cause for this bug is that the optimizer try to detect& optimize the special case: ' BETWEEN c1 AND c1' and handle this as the condition ' = c1' This was implemented inside add_key_field(.. *field, *value[]...) which assumed field to refer key Field, and value[] to refer a [low...high] constant pair. value[0] and value[1] was then compared for equality. In a 'normal' BETWEEN condition of the form ' BETWEEN val1 and val2' the BETWEEN operation is represented with an argementlist containing the values [, val1, val2] - add_key_field() is then called with parameters field=, *value=val1. However, if the BETWEEN predicate specified: 1) ' BETWEEN AND the 'field' and 'value' arguments to add_key_field() had to be swapped. This was implemented by trying to cheat add_key_field() to handle it like: 2) ' GE AND LE' As we didn't really replace the BETWEEN operation with 'ge' and 'le', add_key_field() still handled it as a 'BETWEEN' and compared the (swapped) arguments and for equality. If they was equal, the condition 1) was incorrectly 'optimized' to: 3) ' EQ' This fix moves this optimization of ' BETWEEN c1 AND c1' into add_key_fields() which then calls add_key_equal_fields() to collect key equality / comparison for the key fields in the BETWEEN condition. @ mysql-test/suite/ndb/r/ndb_condition_pushdown.result Updated result file as BETWEEN optimization now correctly recognice 1) ('1901-01-01 01:01:01' between date_time and date_time) as 2) ('1901-01-01 01:01:01' = date_time) And thus is able to create a REF operation on the table instead of a 'range' modified: mysql-test/r/range.result mysql-test/suite/ndb/r/ndb_condition_pushdown.result mysql-test/t/range.test sql/sql_select.cc 4114 Ole John Aske 2011-01-14 Backport to mysql-5.1-telco-7.0: Fix for bug#58626, Incorrect result for WHERE IN () IS UNKNOWN. NOTE: This fix is a backport from 5.6.x which already seems to have fixed this problem. The fix ensures that if 'Full scan on NULL key' access method may be used for a table, we can't assume that any part of the predicate is covered by the REF-key. (The join_tab is known to have 'Full scan on NULL key' if any cond_guards[] has been defined for 'join_tab->ref') part_of_refkey() will therefore return '0' if a potential'Full scan on NULL key' is detected - Which will force make_cond_for_table() to include all part of a predicate covering the specified 'tables' and 'used_table' mask. modified: mysql-test/r/join_outer.result mysql-test/t/join_outer.test sql/sql_select.cc === modified file 'mysql-test/r/range.result' --- a/mysql-test/r/range.result 2010-08-24 15:51:32 +0000 +++ b/mysql-test/r/range.result 2011-01-14 15:08:09 +0000 @@ -1666,4 +1666,105 @@ c_key c_notkey 1 1 3 3 DROP TABLE t1; +# +# Bug #57030: 'BETWEEN' evaluation is incorrect +# +CREATE TABLE t1(pk INT PRIMARY KEY, i4 INT); +CREATE UNIQUE INDEX i4_uq ON t1(i4); +INSERT INTO t1 VALUES (1,10), (2,20), (3,30); +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const i4_uq i4_uq 5 const 1 +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10; +pk i4 +1 10 +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 const i4_uq i4_uq 5 const 1 +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4; +pk i4 +1 10 +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 10 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 3 Using where +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; +pk i4 +1 10 +2 20 +3 30 +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; +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 +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; +pk i4 +1 10 +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10; +pk i4 +1 10 +2 20 +3 30 +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11; +pk i4 +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0; +pk i4 +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0; +pk i4 +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range i4_uq i4_uq 5 NULL 2 Using where +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; +pk i4 +1 10 +2 20 +3 30 +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30; +pk i4 +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; +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 +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; +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/suite/ndb/r/ndb_condition_pushdown.result' --- a/mysql-test/suite/ndb/r/ndb_condition_pushdown.result 2011-01-14 10:42:53 +0000 +++ b/mysql-test/suite/ndb/r/ndb_condition_pushdown.result 2011-01-14 15:08:09 +0000 @@ -1307,7 +1307,7 @@ select auto from t1 where ('1901-01-01 01:01:01' between date_time and date_time) order by auto; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range medium_index medium_index 3 NULL # Using where with pushed condition; Using filesort +1 SIMPLE t1 ref medium_index medium_index 3 const # Using where with pushed condition; Using filesort select auto from t1 where ("aaaa" between string and string) and ("aaaa" between vstring and vstring) and === modified file 'mysql-test/t/range.test' --- a/mysql-test/t/range.test 2010-08-24 15:51:32 +0000 +++ b/mysql-test/t/range.test 2011-01-14 15:08:09 +0000 @@ -1325,4 +1325,71 @@ SELECT * FROM t1 WHERE 2 NOT BETWEEN c_n DROP TABLE t1; +--echo # +--echo # Bug #57030: 'BETWEEN' evaluation is incorrect +--echo # + +# Test some BETWEEN predicates which does *not* follow the +# 'normal' pattern of BETWEEN AND + +CREATE TABLE t1(pk INT PRIMARY KEY, i4 INT); +CREATE UNIQUE INDEX i4_uq ON t1(i4); + +INSERT INTO t1 VALUES (1,10), (2,20), (3,30); + +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10; +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10; + +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4; +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4; + +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; + +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; + +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10; +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10; + +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11; +SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11; + +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0; +SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0; + +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0; +SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0; + +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; + +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30; +SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30; + +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; + --echo End of 5.1 tests === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2011-01-14 14:26:14 +0000 +++ b/sql/sql_select.cc 2011-01-14 15:08:09 +0000 @@ -3330,6 +3330,9 @@ add_key_field(KEY_FIELD **key_fields,uin eq_func is NEVER true when num_values > 1 */ if (!eq_func) +#ifndef MCP_BUG57030 + return; +#else { /* Additional optimization: if we're processing @@ -3350,6 +3353,7 @@ add_key_field(KEY_FIELD **key_fields,uin eq_func= TRUE; } +#endif if (field->result_type() == STRING_RESULT) { if ((*value)->result_type() != STRING_RESULT) @@ -3545,9 +3549,71 @@ add_key_fields(JOIN *join, KEY_FIELD **k case Item_func::OPTIMIZE_KEY: { Item **values; +#ifndef MCP_BUG57030 + /* + 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, + 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)) +#else // BETWEEN, IN, NE if (is_local_field (cond_func->key_item()) && !(cond_func->used_tables() & OUTER_REF_TABLE_BIT)) +#endif /* MCP_BUG57030 */ { values= cond_func->arguments()+1; if (cond_func->functype() == Item_func::NE_FUNC && @@ -3561,6 +3627,7 @@ add_key_fields(JOIN *join, KEY_FIELD **k cond_func->argument_count()-1, usable_tables, sargables); } +#ifdef MCP_BUG57030 if (cond_func->functype() == Item_func::BETWEEN) { values= cond_func->arguments(); @@ -3576,6 +3643,7 @@ add_key_fields(JOIN *join, KEY_FIELD **k } } } +#endif break; } case Item_func::OPTIMIZE_OP: No bundle (reason: useless for push emails).