MySQL Lists are EOL. Please join:

List:Internals« Previous MessageNext Message »
From:timour Date:June 23 2005 9:09am
Subject:bk commit into 5.0 tree (timour:1.2000) BUG#11185
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 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.2000 05/06/23 12:08:56 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.

  sql/opt_range.cc
    1.172 05/06/23 12:08:50 timour@stripped +1 -6
    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.
        
    This optimization is also necessary so that the index range access
    method produces correct results when comparing unsigned fields with
    negative constants.

  mysql-test/t/range.test
    1.29 05/06/23 12:08:50 timour@stripped +16 -0
    - Added test for BUG#11185
    - Added missing test from 4.1. This test also tests the fix for BUG#11185.

  mysql-test/r/range.result
    1.36 05/06/23 12:08:50 timour@stripped +29 -1
    - Added test for BUG#11185
    - Added missing test from 4.1. This test also tests the fix 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/5.0-dbg/RESYNC

--- 1.171/sql/opt_range.cc	2005-06-22 12:08:21 +03:00
+++ 1.172/sql/opt_range.cc	2005-06-23 12:08:50 +03:00
@@ -3840,6 +3840,35 @@
   if (!(tree=new SEL_ARG(field,str,str)))
     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, and we also get correct comparison of unsinged integers with
+    negative integers (which otherwise fails because at query execution time
+    negative integers are cast to unsigned if compared with unsigned).
+   */
+  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)
+        DBUG_RETURN(0);
+    }
+  }
+
   switch (type) {
   case Item_func::LT_FUNC:
     if (field_is_equal_to_item(field,value))

--- 1.35/mysql-test/r/range.result	2005-05-21 16:08:03 +03:00
+++ 1.36/mysql-test/r/range.result	2005-06-23 12:08:50 +03:00
@@ -544,11 +544,71 @@
 1
 select count(*) from t2 where x > -16;
 count(*)
-1
+2
 select count(*) from t2 where x = 18446744073709551601;
 count(*)
 0
 drop table t1,t2;
+create table t1 (x bigint unsigned not null primary key) engine=innodb;
+insert into t1(x) values (0xfffffffffffffff0);
+insert into t1(x) values (0xfffffffffffffff1);
+select * from t1;
+x
+18446744073709551600
+18446744073709551601
+select count(*) from t1 where x>0;
+count(*)
+2
+select count(*) from t1 where x=0;
+count(*)
+0
+select count(*) from t1 where x<0;
+count(*)
+0
+select count(*) from t1 where x < -16;
+count(*)
+0
+select count(*) from t1 where x = -16;
+count(*)
+0
+select count(*) from t1 where x > -16;
+count(*)
+2
+select count(*) from t1 where x = 18446744073709551601;
+count(*)
+1
+drop table t1;
+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
+drop table t1;
 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-21 16:07:37 +03:00
+++ 1.29/mysql-test/t/range.test	2005-06-23 12:08:50 +03:00
@@ -418,6 +418,41 @@
 select count(*) from t2 where x = 18446744073709551601;
 drop table t1,t2;
 
+--disable_warnings
+create table t1 (x bigint unsigned not null primary key) engine=innodb;
+--enable_warnings
+insert into t1(x) values (0xfffffffffffffff0);
+insert into t1(x) values (0xfffffffffffffff1);
+select * from t1;
+select count(*) from t1 where x>0;
+select count(*) from t1 where x=0;
+select count(*) from t1 where x<0;
+select count(*) from t1 where x < -16;
+select count(*) from t1 where x = -16;
+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
+#
+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;
+
+drop table t1;
+
 #
 # Bug #6045: Binary Comparison regression in MySQL 4.1
 # Binary searches didn't use a case insensitive index.
Thread
bk commit into 5.0 tree (timour:1.2000) BUG#11185timour23 Jun