From: Date: June 21 2005 7:47pm Subject: bk commit into 4.1 tree (timour:1.2304) BUG#11185 List-Archive: http://lists.mysql.com/internals/26259 X-Bug: 11185 Message-Id: <20050621174736.C458A16E62E@zmei.home> Below is the list of changes that have just been committed into a local 4.1 repository of timka. When timka 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 1.2304 05/06/21 20:47:30 timour@stripped +3 -0 Fix for BUG#11185. The source of the problem is in Field_longlong::cmp. If 'this' is an unsigned number, the method casts both the current value, and the constant that we compare with to an unsigned number. As a result if the constant we compare with is a negative number, it wraps to some unsigned number, and the comparison is incorrect. When the optimizer chooses the "range" access method, this problem causes handler::read_range_next to reject the current key when the upper bound key is a negative number because handler::compare_key incorrectly considers the positive and negative keys to be equal. The current patch does not correct the source of the problem in Field_longlong::cmp because it is not easy to propagate sign information about the constant at query execution time. Instead the patch changes the range optimizer so that it never compares unsiged fields with negative constants. As an added benefit, queries that do such comparisons will execute faster because the range optimizer replaces conditions like: (a) (unsigned_int [< | <=] negative_constant) == FALSE (b) (unsigned_int [> | >=] negative_constant) == TRUE with the corresponding constants. In some cases this may even result in constant time execution. The patch also corrects an additional problem with all other integer types, where negative bounds are replaced with '0'. This resulted in incorrect results when comparing '0' values with negative integers. sql/opt_range.cc 1.139 05/06/21 20:47:27 timour@stripped +41 -2 Added a new optimization to the range optimizer where we detect that an UNSIGNED field is compared with a negative constant. Depending on the comparison operator, we know directly that the result of the comparison is either TRUE or FALSE for all input values, and we need not check each value. mysql-test/t/range.test 1.29 05/06/21 20:47:27 timour@stripped +44 -3 - Added new tests for BUG#11185 - Deleted an old comment because now the problem is fixed mysql-test/r/range.result 1.34 05/06/21 20:47:27 timour@stripped +79 -1 - Changed incorrect result of an old test - Added new results for BUG#11185 # 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: timour # Host: zmei.home # Root: /home/timka/mysql/src/4.1-dbg --- 1.138/sql/opt_range.cc 2005-06-05 20:38:41 +03:00 +++ 1.139/sql/opt_range.cc 2005-06-21 20:47:27 +03:00 @@ -55,7 +55,7 @@ SEL_ARG *left,*right,*next,*prev,*parent,*next_key_part; enum leaf_color { BLACK,RED } color; - enum Type { IMPOSSIBLE, MAYBE, MAYBE_KEY, KEY_RANGE } type; + enum Type { IMPOSSIBLE, ALWAYS, MAYBE, MAYBE_KEY, KEY_RANGE } type; SEL_ARG() {} SEL_ARG(SEL_ARG &); @@ -960,8 +960,17 @@ if (sel_arg->type == SEL_ARG::IMPOSSIBLE) { tree->type=SEL_TREE::IMPOSSIBLE; - DBUG_RETURN(tree); + /* If this is an NE_FUNC, we still need to check GT_FUNC. */ + if (!ne_func) + DBUG_RETURN(tree); } + if (sel_arg->type == SEL_ARG::ALWAYS) + { + tree->type=SEL_TREE::ALWAYS; + /* If this is an NE_FUNC, we still need to check GT_FUNC. */ + if (!ne_func) + DBUG_RETURN(tree); + } } else { @@ -1158,6 +1167,36 @@ } if (!(tree=new SEL_ARG(field,str,str2))) DBUG_RETURN(0); // out of memory + + /* + Check if we are comparing an UNSIGNED integer with a negative constant. + In this case we know that: + (a) (unsigned_int [< | <=] negative_constant) == FALSE + (b) (unsigned_int [> | >=] negative_constant) == TRUE + In case (a) the condition is false for all values, and in case (b) it + is true for all values, so we can avoid unnecessary retrieval and condition + testing. + */ + Item_result field_result_type= field->result_type(); + Item_result value_result_type= value->result_type(); + if (field_result_type == INT_RESULT && value_result_type == INT_RESULT && + ((Field_num*)field)->unsigned_flag && !((Item_int*)value)->unsigned_flag) + { + longlong item_val= value->val_int(); + if (item_val < 0) + { + if (type == Item_func::LT_FUNC || type == Item_func::LE_FUNC) + { + tree->type= SEL_ARG::IMPOSSIBLE; + DBUG_RETURN(tree); + } + if (type == Item_func::GT_FUNC || type == Item_func::GE_FUNC) + { + tree->type= SEL_ARG::ALWAYS; + DBUG_RETURN(tree); + } + } + } switch (type) { case Item_func::LT_FUNC: --- 1.33/mysql-test/r/range.result 2005-04-17 01:05:06 +03:00 +++ 1.34/mysql-test/r/range.result 2005-06-21 20:47:27 +03:00 @@ -556,11 +556,89 @@ 0 select count(*) from t1 where x > -16; count(*) -1 +2 select count(*) from t1 where x = 18446744073709551601; count(*) 1 drop table t1; +drop table if exists t1, t2, t3; +Warnings: +Note 1051 Unknown table 't1' +Note 1051 Unknown table 't3' +create table t1 (a bigint unsigned); +create index t1i on t1(a); +insert into t1 select 18446744073709551615; +insert into t1 select 18446744073709551614; +explain select * from t1 where a <> -1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index t1i t1i 9 NULL 2 Using where; Using index +select * from t1 where a <> -1; +a +18446744073709551614 +18446744073709551615 +explain select * from t1 where a > -1 or a < -1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index t1i t1i 9 NULL 2 Using where; Using index +select * from t1 where a > -1 or a < -1; +a +18446744073709551614 +18446744073709551615 +explain select * from t1 where a > -1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index t1i t1i 9 NULL 2 Using where; Using index +select * from t1 where a > -1; +a +18446744073709551614 +18446744073709551615 +explain select * from t1 where a < -1; +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 a < -1; +a +create table t2 (a tinyint unsigned); +create index t2i on t2(a); +insert into t2 values (0), (254), (255); +explain select * from t2 where a <> -1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index t2i t2i 2 NULL 3 Using where; Using index +select * from t2 where a <> -1; +a +0 +254 +255 +explain select * from t2 where a > -1 or a < -1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index t2i t2i 2 NULL 3 Using where; Using index +select * from t2 where a > -1 or a < -1; +a +0 +254 +255 +explain select * from t2 where a > -1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index t2i t2i 2 NULL 3 Using where; Using index +select * from t2 where a > -1; +a +0 +254 +255 +explain select * from t2 where a < -1; +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 t2 where a < -1; +a +drop table if exists t3; +Warnings: +Note 1051 Unknown table 't3' +create table t3 (a int unsigned); +create index t3i on t3(a); +insert into t3 values (0), (4294967294), (4294967295); +select * from t3 where a <> -1; +a +0 +4294967294 +4294967295 +drop table t1, t2, t3; set names latin1; create table t1 (a char(10), b text, key (a)) character set latin1; INSERT INTO t1 (a) VALUES --- 1.28/mysql-test/t/range.test 2005-05-18 12:02:52 +03:00 +++ 1.29/mysql-test/t/range.test 2005-06-21 20:47:27 +03:00 @@ -423,13 +423,54 @@ select count(*) from t1 where x<0; select count(*) from t1 where x < -16; select count(*) from t1 where x = -16; -# The following query returns wrong value because the range optimizer can't -# handle search on a signed value for an unsigned parameter. This will be fixed in -# 5.0 select count(*) from t1 where x > -16; select count(*) from t1 where x = 18446744073709551601; drop table t1; + +# +# Bug #11185 incorrect comparison of unsigned int to signed constant +# +drop table if exists t1, t2, t3; + +create table t1 (a bigint unsigned); +create index t1i on t1(a); +insert into t1 select 18446744073709551615; +insert into t1 select 18446744073709551614; + +explain select * from t1 where a <> -1; +select * from t1 where a <> -1; +explain select * from t1 where a > -1 or a < -1; +select * from t1 where a > -1 or a < -1; +explain select * from t1 where a > -1; +select * from t1 where a > -1; +explain select * from t1 where a < -1; +select * from t1 where a < -1; + +# For TINYINT and INT negative values are replaced with '0', +# thus comparison between a '0' values and a negative constant +# considered (0 == any_negative_number). + +create table t2 (a tinyint unsigned); +create index t2i on t2(a); +insert into t2 values (0), (254), (255); + +explain select * from t2 where a <> -1; +select * from t2 where a <> -1; +explain select * from t2 where a > -1 or a < -1; +select * from t2 where a > -1 or a < -1; +explain select * from t2 where a > -1; +select * from t2 where a > -1; +explain select * from t2 where a < -1; +select * from t2 where a < -1; + +drop table if exists t3; +create table t3 (a int unsigned); +create index t3i on t3(a); +insert into t3 values (0), (4294967294), (4294967295); +select * from t3 where a <> -1; + +drop table t1, t2, t3; # # Bug #6045: Binary Comparison regression in MySQL 4.1