bk commit into 5.0 tree (igreenhoe:1.2272) BUG#19342

[Ian Greenhoe]

Below is the list of changes that have just been committed into a local
5.0 repository of greenman. When greenman 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@stripped, 2006-09-12 23:17:58-07:00, igreenhoe@stripped +4 -0
  Fix for bug #19342 (IN works incorrectly for BIGINT UNSIGNED values)
  
  Problem: the in function extracts and stores the integer portion of
  an integer-valued Item prior to comparison for speed.  However, this
  extraction did not keep track if the value was unsigned in certian
  circumstances, and assumed that the stored value was signed.
  
  Solution: add the extra information so that we can make the
  determination at comparison time if the origional values were
  signed or not.  Note that this may appear to be an inefficent packing
  of the data; however, it ensures that the long longs are
  word-aligned, and also keeps the signedness bit near the value so
  that we can efficently sort.

  mysql-test/r/func_in.result@stripped, 2006-09-12 23:17:53-07:00,
igreenhoe@stripped +32 -0
    New tests for the "in" function

  mysql-test/t/func_in.test@stripped, 2006-09-12 23:17:53-07:00,
igreenhoe@stripped +18 -0
    New tests for the "in" function

  sql/item_cmpfunc.cc@stripped, 2006-09-12 23:17:53-07:00,
igreenhoe@stripped +14 -4
    Added unsigned_flag to the information that we keep (and check) when
    comparing integers in the "in" function.

  sql/item_cmpfunc.h@stripped, 2006-09-12 23:17:53-07:00,
igreenhoe@stripped +12 -3
    Added unsigned_flag to the information that we keep (and check) when
    comparing integers in the "in" function.

# 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:	igreenhoe
# Host:	anubis.greendragongames.com
# Root:	/home/greenman/workspace-mysql/mysql/bug-5.0-19342

--- 1.217/sql/item_cmpfunc.cc	2006-09-12 23:18:06 -07:00
+++ 1.218/sql/item_cmpfunc.cc	2006-09-12 23:18:06 -07:00
@@ -2058,6 +2058,10 @@
 
 static int cmp_longlong(void *cmp_arg, longlong *a,longlong *b)
 {
+  /* extra bit (unsigned_flag) is stored immediately after a and b */
+  if (*(a+1) != *(b+1))
+    return *(a+1) ? 1 : -1;
+
   return *a < *b ? -1 : *a == *b ? 0 : 1;
 }
 
@@ -2183,20 +2187,26 @@
 }
 
 in_longlong::in_longlong(uint elements)
-  :in_vector(elements,sizeof(longlong),(qsort2_cmp) cmp_longlong, 0)
+  :in_vector(elements,sizeof(longlong) * 2,(qsort2_cmp) cmp_longlong, 0)
 {}
 
 void in_longlong::set(uint pos,Item *item)
 {
-  ((longlong*) base)[pos]=item->val_int();
+  longlong *temp= (longlong *) base;
+  temp[pos * 2]= item->val_int();
+
+  /* record unsigned flag only if val_int would read as < 0 */
+  temp[pos * 2 + 1]= item->unsigned_flag && temp[pos * 2] < 0;
 }
 
 byte *in_longlong::get_value(Item *item)
 {
-  tmp= item->val_int();
+  tmp[0]= item->val_int();
+  /* record unsigned flag only if val_int would read as < 0 */
+  tmp[1]= item->unsigned_flag && tmp[0] < 0;
   if (item->null_value)
     return 0;
-  return (byte*) &tmp;
+  return (byte*) tmp;
 }
 
 in_double::in_double(uint elements)

--- 1.129/sql/item_cmpfunc.h	2006-09-12 23:18:06 -07:00
+++ 1.130/sql/item_cmpfunc.h	2006-09-12 23:18:06 -07:00
@@ -703,7 +703,8 @@
 
 class in_longlong :public in_vector
 {
-  longlong tmp;
+  /* used by get_value, tmp[0] is val_int, tmp[1] is unsigned_flag */
+  longlong tmp[2];
 public:
   in_longlong(uint elements);
   void set(uint pos,Item *item);
@@ -719,7 +720,8 @@
   }
   void value_to_item(uint pos, Item *item)
   {
-    ((Item_int*)item)->value= ((longlong*)base)[pos];
+    ((Item_int*)item)->value= ((longlong*)base)[pos * 2];
+    ((Item_int*)item)->unsigned_flag= ((longlong*)base)[pos * 2 + 1];
   }
 };
 
@@ -825,19 +827,26 @@
 class cmp_item_int :public cmp_item
 {
   longlong value;
+  my_bool unsigned_flag;
 public:
   cmp_item_int() {}                           /* Remove gcc warning */
   void store_value(Item *item)
   {
     value= item->val_int();
+    /* record unsigned flag only if val_int would read as < 0 */
+    unsigned_flag= item->unsigned_flag && value < 0;
   }
   int cmp(Item *arg)
   {
-    return value != arg->val_int();
+    return (value != arg->val_int()) || (unsigned_flag != arg->unsigned_flag);
   }
   int compare(cmp_item *c)
   {
     cmp_item_int *cmp= (cmp_item_int *)c;
+
+    if (unsigned_flag != cmp->unsigned_flag)
+      return unsigned_flag ? 1 : -1;
+
     return (value < cmp->value) ? -1 : ((value == cmp->value) ? 0 : 1);
   }
   cmp_item *make_same();

--- 1.28/mysql-test/r/func_in.result	2006-09-12 23:18:06 -07:00
+++ 1.29/mysql-test/r/func_in.result	2006-09-12 23:18:06 -07:00
@@ -343,3 +343,35 @@
 1
 2
 drop table t1;
+create table t1 (c1 bigint unsigned);
+insert into t1 values (0xFFFFFFFFFFFFFFFF);
+select * from t1 where c1=-1 or c1=-2;
+c1
+select * from t1 where c1 in (-1, -2);
+c1
+drop table t1;
+create table t1 (c1 smallint(5));
+insert into t1 values (1),(2),(3),(4),(5),(-1),(-2);
+select * from t1 where c1 in (18446744073709551615,18446744073709551612);
+c1
+select * from t1 where c1 not in (18446744073709551615,18446744073709551612);
+c1
+1
+2
+3
+4
+5
+-1
+-2
+select c1 from t1 where c1 in (1,3);
+c1
+1
+3
+select c1 from t1 where c1 not in (1,3);
+c1
+2
+4
+5
+-1
+-2
+drop table t1;

--- 1.22/mysql-test/t/func_in.test	2006-09-12 23:18:06 -07:00
+++ 1.23/mysql-test/t/func_in.test	2006-09-12 23:18:06 -07:00
@@ -232,3 +232,21 @@
 select some_id from t1 where some_id not in(-4,-1,-4);
 select some_id from t1 where some_id not in(-4,-1,3423534,2342342);
 drop table t1;
+
+# bug 19342: IN works incorrectly for bigint unsigned values
+create table t1 (c1 bigint unsigned);
+insert into t1 values (0xFFFFFFFFFFFFFFFF);
+select * from t1 where c1=-1 or c1=-2;
+select * from t1 where c1 in (-1, -2);
+drop table t1;
+
+create table t1 (c1 smallint(5));
+insert into t1 values (1),(2),(3),(4),(5),(-1),(-2);
+
+select * from t1 where c1 in (18446744073709551615,18446744073709551612);
+select * from t1 where c1 not in (18446744073709551615,18446744073709551612);
+select c1 from t1 where c1 in (1,3);
+select c1 from t1 where c1 not in (1,3);
+
+drop table t1;
+