bk commit into 5.0 tree (gkodinov:1.2408) BUG#19342

[kgeorge]

Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge 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, 2007-02-19 15:26:37+02:00, gkodinov@stripped +6 -0
  Bug #19342:
  Several problems here :
   1. The conversion to double of an hex string const item
   was not taking into account the unsigned flag.
   
   2. IN was not behaving in the same was way as comparisons
   when performed over an INT/DATE/DATETIME/TIMESTAMP column
   and a constant. The ordinary comparisons in that case 
   convert the constant to an INTEGER value and do int 
   comparisons. Fixed the IN to do the same.
   
   3. IN is not taking into account the unsigned flag when 
   calculating <expr> IN (<int_const1>, <int_const2>, ...).
   Extended the implementation of IN to store and process
   the unsigned flag for its arguments.

  mysql-test/r/func_in.result@stripped, 2007-02-19 15:26:28+02:00, gkodinov@stripped +66 -0
    Bug #19342: test case

  mysql-test/t/func_in.test@stripped, 2007-02-19 15:26:29+02:00, gkodinov@stripped +58 -0
    Bug #19342: test case

  sql/item.cc@stripped, 2007-02-19 15:26:30+02:00, gkodinov@stripped +8 -0
    Bug #19342: correct handling of the unsigned flag
     for hex strings.

  sql/item.h@stripped, 2007-02-19 15:26:30+02:00, gkodinov@stripped +1 -2
    Bug #19342: correct handling of the unsigned flag
     for hex strings.

  sql/item_cmpfunc.cc@stripped, 2007-02-19 15:26:31+02:00, gkodinov@stripped +60 -5
    Bug #19342: correct handling of the unsigned flag
     for IN.

  sql/item_cmpfunc.h@stripped, 2007-02-19 15:26:32+02:00, gkodinov@stripped +10 -2
    Bug #19342: correct handling of the unsigned flag
     for IN.

# 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:	gkodinov
# Host:	macbook.gmz
# Root:	/Users/kgeorge/mysql/work/B19342-5.0-opt

--- 1.254/sql/item.cc	2007-02-09 11:05:17 +02:00
+++ 1.255/sql/item.cc	2007-02-19 15:26:30 +02:00
@@ -4574,6 +4574,14 @@ my_decimal *Item_hex_string::val_decimal
 }
 
 
+double Item_hex_string::val_real()
+{ 
+  DBUG_ASSERT(fixed == 1);
+  return (double) unsigned_flag ? 
+    (ulonglong) Item_hex_string::val_int() : Item_hex_string::val_int();
+}
+
+
 int Item_hex_string::save_in_field(Field *field, bool no_conversions)
 {
   field->set_notnull();

--- 1.219/sql/item.h	2007-01-26 19:33:16 +02:00
+++ 1.220/sql/item.h	2007-02-19 15:26:30 +02:00
@@ -1765,8 +1765,7 @@ public:
   Item_hex_string(): Item() {}
   Item_hex_string(const char *str,uint str_length);
   enum Type type() const { return VARBIN_ITEM; }
-  double val_real()
-    { DBUG_ASSERT(fixed == 1); return (double) Item_hex_string::val_int(); }
+  double val_real();
   longlong val_int();
   bool basic_const_item() const { return 1; }
   String *val_str(String*) { DBUG_ASSERT(fixed == 1); return &str_value; }

--- 1.232/sql/item_cmpfunc.cc	2007-02-07 10:18:34 +02:00
+++ 1.233/sql/item_cmpfunc.cc	2007-02-19 15:26:31 +02:00
@@ -2034,9 +2034,42 @@ void Item_func_coalesce::fix_length_and_
  Classes and function for the IN operator
 ****************************************************************************/
 
-static int cmp_longlong(void *cmp_arg, longlong *a,longlong *b)
+static inline int cmp_longs (longlong a_val, longlong b_val)
 {
-  return *a < *b ? -1 : *a == *b ? 0 : 1;
+  return a_val < b_val ? -1 : a_val == b_val ? 0 : 1;
+}
+
+static inline int cmp_ulongs (ulonglong a_val, ulonglong b_val)
+{
+  return a_val < b_val ? -1 : a_val == b_val ? 0 : 1;
+}
+
+int cmp_longlong(void *cmp_arg, 
+                        in_longlong::packed_longlong *a,
+                        in_longlong::packed_longlong *b)
+{
+  if (a->unsigned_flag != b->unsigned_flag)
+  { 
+    longlong a_val, b_val;
+    /* 
+      One of the args is unsigned and is too big to fit into the 
+      positive signed range. Report no match.
+    */  
+    if (a->unsigned_flag && ((ulonglong) a->val) > LONGLONG_MAX ||
+        b->unsigned_flag && ((ulonglong) b->val) > LONGLONG_MAX)
+      return a->unsigned_flag ? 1 : -1;
+    /*
+      Althrough the signedness differs both args can fit into the signed 
+      positive range. Make them signed and compare as usual.
+    */  
+    a_val= a->unsigned_flag ? (ulonglong) a->val : (longlong) a->val;
+    b_val= b->unsigned_flag ? (ulonglong) b->val : (longlong) b->val;
+    return cmp_longs (a_val, b_val);
+  }
+  if (a->unsigned_flag)
+    return cmp_ulongs ((ulonglong) a->val, (ulonglong) b->val);
+  else
+    return cmp_longs (a->val, b->val);
 }
 
 static int cmp_double(void *cmp_arg, double *a,double *b)
@@ -2161,19 +2194,23 @@ void in_row::set(uint pos, Item *item)
 }
 
 in_longlong::in_longlong(uint elements)
-  :in_vector(elements,sizeof(longlong),(qsort2_cmp) cmp_longlong, 0)
+  :in_vector(elements,sizeof(packed_longlong),(qsort2_cmp) cmp_longlong, 0)
 {}
 
 void in_longlong::set(uint pos,Item *item)
 {
-  ((longlong*) base)[pos]=item->val_int();
+  struct packed_longlong *buff= &((packed_longlong*) base)[pos];
+  
+  buff->val= item->val_int();
+  buff->unsigned_flag= item->unsigned_flag;
 }
 
 byte *in_longlong::get_value(Item *item)
 {
-  tmp= item->val_int();
+  tmp.val= item->val_int();
   if (item->null_value)
     return 0;
+  tmp.unsigned_flag= item->unsigned_flag;
   return (byte*) &tmp;
 }
 
@@ -2494,6 +2531,24 @@ void Item_func_in::fix_length_and_dec()
   */
   if (const_itm && !nulls_in_row())
   {
+    if (args[0]->real_item()->type() == FIELD_ITEM &&
+        thd->lex->sql_command != SQLCOM_CREATE_VIEW &&
+        thd->lex->sql_command != SQLCOM_SHOW_CREATE &&
+        cmp_type != INT_RESULT)
+    {
+      Field *field= ((Item_field*) (args[0]->real_item()))->field;
+      if (field->can_be_compared_as_longlong())
+      {
+        bool all_converted= TRUE;
+        for (arg=args+1, arg_end=args+arg_count; arg != arg_end ; arg++)
+        {
+          if (!convert_constant_item (thd, field, &arg[0]))
+            all_converted= FALSE;
+        }
+        if (all_converted)
+          cmp_type= INT_RESULT;
+      }
+    }
     switch (cmp_type) {
     case STRING_RESULT:
       array=new in_string(arg_count-1,(qsort2_cmp) srtcmp_in, 

--- 1.139/sql/item_cmpfunc.h	2007-01-26 07:01:25 +02:00
+++ 1.140/sql/item_cmpfunc.h	2007-02-19 15:26:32 +02:00
@@ -731,7 +731,11 @@ public:
 
 class in_longlong :public in_vector
 {
-  longlong tmp;
+  struct packed_longlong 
+  {
+    longlong val;
+    longlong unsigned_flag;  // Use longlong, not bool, to preserve alignment
+  } tmp;
 public:
   in_longlong(uint elements);
   void set(uint pos,Item *item);
@@ -747,8 +751,12 @@ public:
   }
   void value_to_item(uint pos, Item *item)
   {
-    ((Item_int*)item)->value= ((longlong*)base)[pos];
+    ((Item_int*)item)->value= ((packed_longlong*)base)[pos].val;
+    ((Item_int*)item)->unsigned_flag= 
+      ((packed_longlong*)base)[pos].unsigned_flag;
   }
+
+  friend int cmp_longlong(void *cmp_arg, packed_longlong *a,packed_longlong *b);
 };
 
 class in_double :public in_vector

--- 1.31/mysql-test/r/func_in.result	2007-01-23 12:04:14 +02:00
+++ 1.32/mysql-test/r/func_in.result	2007-02-19 15:26:28 +02:00
@@ -398,4 +398,70 @@ WHERE t3.a=t1.a AND t3.a=t2.a;
 3
 3
 DROP TABLE t1,t2,t3,t4;
+CREATE TABLE t1(a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF);
+SELECT * FROM t1 WHERE a=-1 OR a=-2 ;
+a
+SELECT * FROM t1 WHERE a IN (-1, -2);
+a
+CREATE TABLE t2 (a BIGINT UNSIGNED);
+insert into t2 values(13491727406643098568),
+(0x7fffffefffffffff),
+(0x7ffffffeffffffff),
+(0x7fffffffefffffff),
+(0x7ffffffffeffffff),
+(0x7fffffffffefffff),
+(0x7ffffffffffeffff),
+(0x7fffffffffffefff),
+(0x7ffffffffffffeff),
+(0x7fffffffffffffef),
+(0x7ffffffffffffffe),
+(0x7fffffffffffffff),
+(0x8000000000000000),
+(0x8000000000000001),
+(0x8000000000000002),
+(0x8000000000000300),
+(0x8000000000000400),
+(0x8000000000000401),
+(0x8000000000004001),
+(0x8000000000040001),
+(0x8000000000400001),
+(0x8000000004000001),
+(0x8000000040000001),
+(0x8000000400000001),
+(0x8000004000000001),
+(0x8000040000000001);
+SELECT HEX(a) FROM t2 WHERE a IN (0xBB3C3E98175D33C8, 42);
+HEX(a)
+BB3C3E98175D33C8
+SELECT HEX(a) FROM t2 WHERE a IN
+(0xBB3C3E98175D33C8,
+0x7fffffffffffffff,
+0x8000000000000000,
+0x8000000000000400,
+0x8000000000000401,
+42);
+HEX(a)
+BB3C3E98175D33C8
+7FFFFFFFFFFFFFFF
+8000000000000000
+8000000000000400
+8000000000000401
+SELECT HEX(a) FROM t2 WHERE a IN (0x7fffffffffffffff,0x8000000000000001);
+HEX(a)
+7FFFFFFFFFFFFFFF
+8000000000000001
+SELECT HEX(a) FROM t2 WHERE a IN (0x7ffffffffffffffe,0x7fffffffffffffff);
+HEX(a)
+7FFFFFFFFFFFFFFE
+7FFFFFFFFFFFFFFF
+SELECT HEX(a) FROM t2 WHERE a IN (0x7ffffffffffffffe,0x7fffffffffffffff,'abc');
+HEX(a)
+7FFFFFFFFFFFFFFE
+7FFFFFFFFFFFFFFF
+CREATE TABLE t3 (a BIGINT UNSIGNED);
+INSERT INTO t3 VALUES (9223372036854775551);
+SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42);
+HEX(a)
+DROP TABLE t1,t2,t3;
 End of 5.0 tests

--- 1.25/mysql-test/t/func_in.test	2007-01-23 12:04:15 +02:00
+++ 1.26/mysql-test/t/func_in.test	2007-02-19 15:26:29 +02:00
@@ -298,4 +298,62 @@ SELECT STRAIGHT_JOIN 
 
 DROP TABLE t1,t2,t3,t4;  
 
+#
+# BUG#19342: IN works incorrectly for BIGINT UNSIGNED values
+#
+CREATE TABLE t1(a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF);
+
+SELECT * FROM t1 WHERE a=-1 OR a=-2 ;
+SELECT * FROM t1 WHERE a IN (-1, -2);
+
+CREATE TABLE t2 (a BIGINT UNSIGNED);
+insert into t2 values(13491727406643098568),
+       (0x7fffffefffffffff),
+       (0x7ffffffeffffffff),
+       (0x7fffffffefffffff),
+       (0x7ffffffffeffffff),
+       (0x7fffffffffefffff),
+       (0x7ffffffffffeffff),
+       (0x7fffffffffffefff),
+       (0x7ffffffffffffeff),
+       (0x7fffffffffffffef),
+       (0x7ffffffffffffffe),
+       (0x7fffffffffffffff),
+       (0x8000000000000000),
+       (0x8000000000000001),
+       (0x8000000000000002),
+       (0x8000000000000300),
+       (0x8000000000000400),
+       (0x8000000000000401),
+       (0x8000000000004001),
+       (0x8000000000040001),
+       (0x8000000000400001),
+       (0x8000000004000001),
+       (0x8000000040000001),
+       (0x8000000400000001),
+       (0x8000004000000001),
+       (0x8000040000000001);
+
+SELECT HEX(a) FROM t2 WHERE a IN (0xBB3C3E98175D33C8, 42);
+
+SELECT HEX(a) FROM t2 WHERE a IN
+(0xBB3C3E98175D33C8,
+ 0x7fffffffffffffff,
+ 0x8000000000000000,
+ 0x8000000000000400,
+ 0x8000000000000401,
+ 42);
+
+SELECT HEX(a) FROM t2 WHERE a IN (0x7fffffffffffffff,0x8000000000000001);
+SELECT HEX(a) FROM t2 WHERE a IN (0x7ffffffffffffffe,0x7fffffffffffffff);
+SELECT HEX(a) FROM t2 WHERE a IN (0x7ffffffffffffffe,0x7fffffffffffffff,'abc');
+
+CREATE TABLE t3 (a BIGINT UNSIGNED);
+INSERT INTO t3 VALUES (9223372036854775551);
+
+SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42);
+
+DROP TABLE t1,t2,t3;
+
 --echo End of 5.0 tests