Bug#13463415 63502: INCORRECT RESULTS OF BIGINT AND DECIMAL COMPARISON

Bug#11758543 50756: BIGINT '100' MATCHES 1.001E2

Expressions of the form
      BIGINT_COL <compare> <non-integer constant>

      should be done either as decimal, or float.

      Currently however, such comparisons are done as int,
      which means that the constant may be truncated,
      and yield false positives/negatives for all queries
      where compare is '>' '<' '>=' '<=' '=' '!='.

      BIGINT_COL IN <list of contstants>
      and
      BIGINT_COL BETWEEN <constant> AND <constant>
      are also affected.



mysql-test/r/bigint.result:
  New tests.
mysql-test/r/func_in.result:
  BIGINT <=> string comparison should be done as float,
  so a warning for the value 'abc' is appropriate.
mysql-test/t/bigint.test:
  New tests.
sql/item_cmpfunc.cc:
  In convert_constant_item() we verify that the constant item
  can be stored in the given field.
  For BIGINT columns (MYSQL_TYPE_LONGLONG) we must verify that the
  stored constant value is actually comparable as int,
  i.e. that the value was not truncated.
  
  For between: compare as int only if both arguments convert correctly to int.

1 files changed, 21 insertions, 10 deletions

diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index f30b6adcb93..fb9878ccb0a 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -435,11 +435,22 @@ static bool convert_constant_item(THD *thd, Item_field *field_item,
       orig_field_val= field->val_int();
     if (!(*item)->is_null() && !(*item)->save_in_field(field, 1))
     {
-      Item *tmp= new Item_int_with_ref(field->val_int(), *item,
-                                       test(field->flags & UNSIGNED_FLAG));
-      if (tmp)
-        thd->change_item_tree(item, tmp);
-      result= 1;					// Item was replaced
+      int field_cmp= 0;
+      // If item is a decimal value, we must reject it if it was truncated.
+      if (field->type() == MYSQL_TYPE_LONGLONG)
+      {
+        field_cmp= stored_field_cmp_to_item(thd, field, *item);
+        DBUG_PRINT("info", ("convert_constant_item %d", field_cmp));
+      }
+
+      if (0 == field_cmp)
+      {
+        Item *tmp= new Item_int_with_ref(field->val_int(), *item,
+                                         test(field->flags & UNSIGNED_FLAG));
+        if (tmp)
+          thd->change_item_tree(item, tmp);
+        result= 1;					// Item was replaced
+      }
     }
     /* Restore the original field value. */
     if (save_field_value)
@@ -2321,10 +2332,10 @@ void Item_func_between::fix_length_and_dec()
         The following can't be recoded with || as convert_constant_item
         changes the argument
       */
-      if (convert_constant_item(thd, field_item, &args[1]))
-        cmp_type=INT_RESULT;			// Works for all types.
-      if (convert_constant_item(thd, field_item, &args[2]))
-        cmp_type=INT_RESULT;			// Works for all types.
+      const bool cvt_arg1= convert_constant_item(thd, field_item, &args[1]);
+      const bool cvt_arg2= convert_constant_item(thd, field_item, &args[2]);
+      if (cvt_arg1 && cvt_arg2)
+        cmp_type=INT_RESULT;                    // Works for all types.
     }
   }
 }