bzr commit into mysql-5.1-bugteam branch (Alexey.Kopytov:2973)

[Alexey Kopytov]

#At file:///data/src/bzr/bugteam/bug45262/my51-bug45262/ based on
revid:aelkin@stripped

 2973 Alexey Kopytov	2009-07-03 [merge]
      Manual merge.

    modified:
      mysql-test/r/type_newdecimal.result
      mysql-test/t/type_newdecimal.test
      sql/item.cc
      sql/item_cmpfunc.cc
      sql/item_func.cc
      sql/item_func.h
      sql/item_sum.cc
      sql/my_decimal.h
      sql/sql_select.cc
=== modified file 'mysql-test/r/type_newdecimal.result'
--- a/mysql-test/r/type_newdecimal.result	2008-11-18 09:52:03 +0000
+++ b/mysql-test/r/type_newdecimal.result	2009-07-03 10:36:04 +0000
@@ -1524,10 +1524,10 @@ Warnings:
 Warning	1264	Out of range value for column 'f1' at row 1
 DESC t1;
 Field	Type	Null	Key	Default	Extra
-f1	decimal(59,30)	NO		0.000000000000000000000000000000	
+f1	decimal(65,30)	NO		0.000000000000000000000000000000	
 SELECT f1 FROM t1;
 f1
-99999999999999999999999999999.999999999999999999999999999999
+99999999999999999999999999999999999.999999999999999999999999999999
 DROP TABLE t1;
 select (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 *
 1.01500000 * 1.01500000 * 0.99500000);
@@ -1577,3 +1577,56 @@ Error	1264	Out of range value for column
 select cast(98.6 as decimal(2,0));
 cast(98.6 as decimal(2,0))
 99
+#
+# Bug #45262: Bad effects with CREATE TABLE and DECIMAL
+#
+CREATE TABLE t1 SELECT
.123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS
my_col;
+Warnings:
+Note	1265	Data truncated for column 'my_col' at row 1
+DESCRIBE t1;
+Field	Type	Null	Key	Default	Extra
+my_col	decimal(30,30)	NO		0.000000000000000000000000000000	
+SELECT my_col FROM t1;
+my_col
+0.123456789123456789123456789123
+DROP TABLE t1;
+CREATE TABLE t1 SELECT 1 +
.123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS
my_col;
+Warnings:
+Note	1265	Data truncated for column 'my_col' at row 1
+DESCRIBE t1;
+Field	Type	Null	Key	Default	Extra
+my_col	decimal(65,30)	NO		0.000000000000000000000000000000	
+SELECT my_col FROM t1;
+my_col
+1.123456789123456789123456789123
+DROP TABLE t1;
+CREATE TABLE t1 SELECT 1 *
.123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS
my_col;
+Warnings:
+Note	1265	Data truncated for column 'my_col' at row 1
+DESCRIBE t1;
+Field	Type	Null	Key	Default	Extra
+my_col	decimal(65,30)	NO		0.000000000000000000000000000000	
+SELECT my_col FROM t1;
+my_col
+0.123456789123456789123456789123
+DROP TABLE t1;
+CREATE TABLE t1 SELECT 1 /
.123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS
my_col;
+Warnings:
+Note	1265	Data truncated for column 'my_col' at row 1
+DESCRIBE t1;
+Field	Type	Null	Key	Default	Extra
+my_col	decimal(65,4)	YES		NULL	
+SELECT my_col FROM t1;
+my_col
+8.1000
+DROP TABLE t1;
+CREATE TABLE t1 SELECT 1 %
.123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS
my_col;
+Warnings:
+Note	1265	Data truncated for column 'my_col' at row 1
+DESCRIBE t1;
+Field	Type	Null	Key	Default	Extra
+my_col	decimal(65,30)	YES		NULL	
+SELECT my_col FROM t1;
+my_col
+0.012345687012345687012345687012
+DROP TABLE t1;

=== modified file 'mysql-test/t/type_newdecimal.test'
--- a/mysql-test/t/type_newdecimal.test	2008-11-17 15:43:10 +0000
+++ b/mysql-test/t/type_newdecimal.test	2009-07-03 10:36:04 +0000
@@ -1257,3 +1257,32 @@ select cast(-3.4 as decimal(2,1));
 select cast(99.6 as decimal(2,0));
 select cast(-13.4 as decimal(2,1));
 select cast(98.6 as decimal(2,0));
+
+--echo #
+--echo # Bug #45262: Bad effects with CREATE TABLE and DECIMAL
+--echo #
+
+CREATE TABLE t1 SELECT
.123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS
my_col;
+DESCRIBE t1;
+SELECT my_col FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 SELECT 1 +
.123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS
my_col;
+DESCRIBE t1;
+SELECT my_col FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 SELECT 1 *
.123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS
my_col;
+DESCRIBE t1;
+SELECT my_col FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 SELECT 1 /
.123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS
my_col;
+DESCRIBE t1;
+SELECT my_col FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 SELECT 1 %
.123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS
my_col;
+DESCRIBE t1;
+SELECT my_col FROM t1;
+DROP TABLE t1;

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2009-06-17 14:56:44 +0000
+++ b/sql/item.cc	2009-07-03 10:36:04 +0000
@@ -2260,8 +2260,10 @@ Item_decimal::Item_decimal(const char *s
   name= (char*) str_arg;
   decimals= (uint8) decimal_value.frac;
   fixed= 1;
-  max_length= my_decimal_precision_to_length(decimal_value.intg + decimals,
-                                             decimals, unsigned_flag);
+  max_length= my_decimal_precision_to_length_no_truncation(decimal_value.intg +
+                                                           decimals,
+                                                           decimals,
+                                                           unsigned_flag);
 }
 
 Item_decimal::Item_decimal(longlong val, bool unsig)
@@ -2269,8 +2271,10 @@ Item_decimal::Item_decimal(longlong val,
   int2my_decimal(E_DEC_FATAL_ERROR, val, unsig, &decimal_value);
   decimals= (uint8) decimal_value.frac;
   fixed= 1;
-  max_length= my_decimal_precision_to_length(decimal_value.intg + decimals,
-                                             decimals, unsigned_flag);
+  max_length= my_decimal_precision_to_length_no_truncation(decimal_value.intg +
+                                                           decimals,
+                                                           decimals,
+                                                           unsigned_flag);
 }
 
 
@@ -2279,8 +2283,10 @@ Item_decimal::Item_decimal(double val, i
   double2my_decimal(E_DEC_FATAL_ERROR, val, &decimal_value);
   decimals= (uint8) decimal_value.frac;
   fixed= 1;
-  max_length= my_decimal_precision_to_length(decimal_value.intg + decimals,
-                                             decimals, unsigned_flag);
+  max_length= my_decimal_precision_to_length_no_truncation(decimal_value.intg +
+                                                           decimals,
+                                                           decimals,
+                                                           unsigned_flag);
 }
 
 
@@ -2300,8 +2306,10 @@ Item_decimal::Item_decimal(my_decimal *v
   my_decimal2decimal(value_par, &decimal_value);
   decimals= (uint8) decimal_value.frac;
   fixed= 1;
-  max_length= my_decimal_precision_to_length(decimal_value.intg + decimals,
-                                             decimals, unsigned_flag);
+  max_length= my_decimal_precision_to_length_no_truncation(decimal_value.intg +
+                                                           decimals,
+                                                           decimals,
+                                                           unsigned_flag);
 }
 
 
@@ -2311,8 +2319,8 @@ Item_decimal::Item_decimal(const uchar *
                     &decimal_value, precision, scale);
   decimals= (uint8) decimal_value.frac;
   fixed= 1;
-  max_length= my_decimal_precision_to_length(precision, decimals,
-                                             unsigned_flag);
+  max_length= my_decimal_precision_to_length_no_truncation(precision, decimals,
+                                                           unsigned_flag);
 }
 
 
@@ -2367,8 +2375,10 @@ void Item_decimal::set_decimal_value(my_
   my_decimal2decimal(value_par, &decimal_value);
   decimals= (uint8) decimal_value.frac;
   unsigned_flag= !decimal_value.sign();
-  max_length= my_decimal_precision_to_length(decimal_value.intg + decimals,
-                                             decimals, unsigned_flag);
+  max_length= my_decimal_precision_to_length_no_truncation(decimal_value.intg +
+                                                           decimals,
+                                                           decimals,
+                                                           unsigned_flag);
 }
 
 
@@ -2640,8 +2650,9 @@ void Item_param::set_decimal(const char 
   str2my_decimal(E_DEC_FATAL_ERROR, str, &decimal_value, &end);
   state= DECIMAL_VALUE;
   decimals= decimal_value.frac;
-  max_length= my_decimal_precision_to_length(decimal_value.precision(),
-                                             decimals, unsigned_flag);
+  max_length=
+    my_decimal_precision_to_length_no_truncation(decimal_value.precision(),
+                                                 decimals, unsigned_flag);
   maybe_null= 0;
   DBUG_VOID_RETURN;
 }
@@ -2797,8 +2808,9 @@ bool Item_param::set_from_user_var(THD *
       my_decimal2decimal(ent_value, &decimal_value);
       state= DECIMAL_VALUE;
       decimals= ent_value->frac;
-      max_length= my_decimal_precision_to_length(ent_value->precision(),
-                                                 decimals, unsigned_flag);
+      max_length=
+        my_decimal_precision_to_length_no_truncation(ent_value->precision(),
+                                                     decimals, unsigned_flag);
       item_type= Item::DECIMAL_ITEM;
       break;
     }
@@ -7290,8 +7302,9 @@ bool Item_type_holder::join_types(THD *t
     int item_prec = max(prev_decimal_int_part, item_int_part) + decimals;
     int precision= min(item_prec, DECIMAL_MAX_PRECISION);
     unsigned_flag&= item->unsigned_flag;
-    max_length= my_decimal_precision_to_length(precision, decimals,
-                                               unsigned_flag);
+    max_length= my_decimal_precision_to_length_no_truncation(precision,
+                                                             decimals,
+                                                             unsigned_flag);
   }
 
   switch (Field::result_merge_type(fld_type))

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2009-06-09 16:44:26 +0000
+++ b/sql/item_cmpfunc.cc	2009-07-03 10:36:04 +0000
@@ -2760,8 +2760,9 @@ void Item_func_case::fix_length_and_dec(
       agg_num_lengths(args[i + 1]);
     if (else_expr_num != -1) 
       agg_num_lengths(args[else_expr_num]);
-    max_length= my_decimal_precision_to_length(max_length + decimals, decimals,
-                                               unsigned_flag);
+    max_length= my_decimal_precision_to_length_no_truncation(max_length +
+                                                             decimals, decimals,
+                                                             unsigned_flag);
   }
 }
 

=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc	2009-06-09 16:44:26 +0000
+++ b/sql/item_func.cc	2009-07-03 10:36:04 +0000
@@ -452,11 +452,45 @@ Field *Item_func::tmp_table_field(TABLE 
     return make_string_field(table);
     break;
   case DECIMAL_RESULT:
-    field= new Field_new_decimal(my_decimal_precision_to_length(decimal_precision(),
-                                                                decimals,
-                                                                unsigned_flag),
-                                 maybe_null, name, decimals, unsigned_flag);
+  {
+    uint8 dec= decimals;
+    uint8 intg= decimal_precision() - dec;
+    uint32 len= max_length;
+
+    /*
+      Trying to put too many digits overall in a DECIMAL(prec,dec)
+      will always throw a warning. We must limit dec to
+      DECIMAL_MAX_SCALE however to prevent an assert() later.
+    */
+
+    if (dec > 0)
+    {
+      int overflow;
+
+      dec= min(dec, DECIMAL_MAX_SCALE);
+
+      /*
+        If the value still overflows the field with the corrected dec,
+        we'll throw out decimals rather than integers. This is still
+        bad and of course throws a truncation warning.
+      */
+
+      const int required_length=
+        my_decimal_precision_to_length(intg + dec, dec,
+                                                     unsigned_flag);
+
+      overflow= required_length - len;
+
+      if (overflow > 0)
+        dec= max(0, dec - overflow);            // too long, discard fract
+      else
+        /* Corrected value fits. */
+        len= required_length;
+    }
+
+    field= new Field_new_decimal(len, maybe_null, name, dec, unsigned_flag);
     break;
+  }
   case ROW_RESULT:
   default:
     // This case should never be chosen
@@ -545,8 +579,8 @@ void Item_func::count_decimal_length()
     set_if_smaller(unsigned_flag, args[i]->unsigned_flag);
   }
   int precision= min(max_int_part + decimals, DECIMAL_MAX_PRECISION);
-  max_length= my_decimal_precision_to_length(precision, decimals,
-                                             unsigned_flag);
+  max_length= my_decimal_precision_to_length_no_truncation(precision, decimals,
+                                                           unsigned_flag);
 }
 
 
@@ -1141,16 +1175,15 @@ void Item_func_additive_op::result_preci
   decimals= max(args[0]->decimals, args[1]->decimals);
   int arg1_int= args[0]->decimal_precision() - args[0]->decimals;
   int arg2_int= args[1]->decimal_precision() - args[1]->decimals;
-  int est_prec= max(arg1_int, arg2_int) + 1 + decimals;
-  int precision= min(est_prec, DECIMAL_MAX_PRECISION);
+  int precision= max(arg1_int, arg2_int) + 1 + decimals;
 
   /* Integer operations keep unsigned_flag if one of arguments is unsigned */
   if (result_type() == INT_RESULT)
     unsigned_flag= args[0]->unsigned_flag | args[1]->unsigned_flag;
   else
     unsigned_flag= args[0]->unsigned_flag & args[1]->unsigned_flag;
-  max_length= my_decimal_precision_to_length(precision, decimals,
-                                             unsigned_flag);
+  max_length= my_decimal_precision_to_length_no_truncation(precision, decimals,
+                                                           unsigned_flag);
 }
 
 
@@ -1255,7 +1288,8 @@ void Item_func_mul::result_precision()
   decimals= min(args[0]->decimals + args[1]->decimals, DECIMAL_MAX_SCALE);
   uint est_prec = args[0]->decimal_precision() + args[1]->decimal_precision();
   uint precision= min(est_prec, DECIMAL_MAX_PRECISION);
-  max_length= my_decimal_precision_to_length(precision, decimals,unsigned_flag);
+  max_length= my_decimal_precision_to_length_no_truncation(precision, decimals,
+                                                           unsigned_flag);
 }
 
 
@@ -1311,8 +1345,8 @@ void Item_func_div::result_precision()
   else
     unsigned_flag= args[0]->unsigned_flag & args[1]->unsigned_flag;
   decimals= min(args[0]->decimals + prec_increment, DECIMAL_MAX_SCALE);
-  max_length= my_decimal_precision_to_length(precision, decimals,
-                                             unsigned_flag);
+  max_length= my_decimal_precision_to_length_no_truncation(precision, decimals,
+                                                           unsigned_flag);
 }
 
 
@@ -1999,8 +2033,9 @@ void Item_func_round::fix_length_and_dec
 
     precision-= decimals_delta - length_increase;
     decimals= min(decimals_to_set, DECIMAL_MAX_SCALE);
-    max_length= my_decimal_precision_to_length(precision, decimals,
-                                               unsigned_flag);
+    max_length= my_decimal_precision_to_length_no_truncation(precision,
+                                                             decimals,
+                                                             unsigned_flag);
     break;
   }
   default:
@@ -2243,8 +2278,9 @@ void Item_func_min_max::fix_length_and_d
     }
   }
   else if ((cmp_type == DECIMAL_RESULT) || (cmp_type == INT_RESULT))
-    max_length= my_decimal_precision_to_length(max_int_part+decimals, decimals,
-                                            unsigned_flag);
+    max_length= my_decimal_precision_to_length_no_truncation(max_int_part +
+                                                             decimals, decimals,
+                                                             unsigned_flag);
   cached_field_type= agg_field_type(args, arg_count);
 }
 

=== modified file 'sql/item_func.h'
--- a/sql/item_func.h	2009-05-21 20:22:46 +0000
+++ b/sql/item_func.h	2009-07-03 10:36:04 +0000
@@ -378,7 +378,8 @@ public:
   Item_decimal_typecast(Item *a, int len, int dec) :Item_func(a)
   {
     decimals= dec;
-    max_length= my_decimal_precision_to_length(len, dec, unsigned_flag);
+    max_length= my_decimal_precision_to_length_no_truncation(len, dec,
+                                                             unsigned_flag);
   }
   String *val_str(String *str);
   double val_real();

=== modified file 'sql/item_sum.cc'
--- a/sql/item_sum.cc	2009-06-15 15:57:06 +0000
+++ b/sql/item_sum.cc	2009-07-03 10:36:04 +0000
@@ -798,8 +798,9 @@ void Item_sum_sum::fix_length_and_dec()
   {
     /* SUM result can't be longer than length(arg) + length(MAX_ROWS) */
     int precision= args[0]->decimal_precision() + DECIMAL_LONGLONG_DIGITS;
-    max_length= my_decimal_precision_to_length(precision, decimals,
-                                               unsigned_flag);
+    max_length= my_decimal_precision_to_length_no_truncation(precision,
+                                                             decimals,
+                                                             unsigned_flag);
     curr_dec_buff= 0;
     hybrid_type= DECIMAL_RESULT;
     my_decimal_set_zero(dec_buffs);
@@ -1233,8 +1234,9 @@ void Item_sum_avg::fix_length_and_dec()
   {
     int precision= args[0]->decimal_precision() + prec_increment;
     decimals= min(args[0]->decimals + prec_increment, DECIMAL_MAX_SCALE);
-    max_length= my_decimal_precision_to_length(precision, decimals,
-                                               unsigned_flag);
+    max_length= my_decimal_precision_to_length_no_truncation(precision,
+                                                             decimals,
+                                                             unsigned_flag);
     f_precision= min(precision+DECIMAL_LONGLONG_DIGITS, DECIMAL_MAX_PRECISION);
     f_scale=  args[0]->decimals;
     dec_bin_size= my_decimal_get_binary_size(f_precision, f_scale);
@@ -1439,8 +1441,9 @@ void Item_sum_variance::fix_length_and_d
   {
     int precision= args[0]->decimal_precision()*2 + prec_increment;
     decimals= min(args[0]->decimals + prec_increment, DECIMAL_MAX_SCALE);
-    max_length= my_decimal_precision_to_length(precision, decimals,
-                                               unsigned_flag);
+    max_length= my_decimal_precision_to_length_no_truncation(precision,
+                                                             decimals,
+                                                             unsigned_flag);
 
     break;
   }

=== modified file 'sql/my_decimal.h'
--- a/sql/my_decimal.h	2008-05-20 07:38:17 +0000
+++ b/sql/my_decimal.h	2009-07-03 10:36:04 +0000
@@ -183,6 +183,19 @@ inline uint my_decimal_length_to_precisi
                  (unsigned_flag || !length ? 0:1));
 }
 
+inline uint32 my_decimal_precision_to_length_no_truncation(uint precision,
+                                                           uint8 scale,
+                                                           bool unsigned_flag)
+{
+  /*
+    When precision is 0 it means that original length was also 0. Thus
+    unsigned_flag is ignored in this case.
+  */
+  DBUG_ASSERT(precision || !scale);
+  return (uint32)(precision + (scale > 0 ? 1 : 0) +
+                  (unsigned_flag || !precision ? 0 : 1));
+}
+
 inline uint32 my_decimal_precision_to_length(uint precision, uint8 scale,
                                              bool unsigned_flag)
 {
@@ -192,8 +205,8 @@ inline uint32 my_decimal_precision_to_le
   */
   DBUG_ASSERT(precision || !scale);
   set_if_smaller(precision, DECIMAL_MAX_PRECISION);
-  return (uint32)(precision + (scale>0 ? 1:0) +
-                  (unsigned_flag || !precision ? 0:1));
+  return my_decimal_precision_to_length_no_truncation(precision, scale,
+                                                      unsigned_flag);
 }
 
 inline

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2009-06-17 14:56:44 +0000
+++ b/sql/sql_select.cc	2009-07-03 10:36:04 +0000
@@ -9392,13 +9392,17 @@ static Field *create_tmp_field_from_item
         +1: for decimal point
       */
 
-      overflow= my_decimal_precision_to_length(intg + dec, dec,
-                                               item->unsigned_flag) - len;
+      const int required_length=
+        my_decimal_precision_to_length(intg + dec, dec,
+                                                     item->unsigned_flag);
+
+      overflow= required_length - len;
 
       if (overflow > 0)
         dec= max(0, dec - overflow);            // too long, discard fract
       else
-        len -= item->decimals - dec;            // corrected value fits
+        /* Corrected value fits. */
+        len= required_length;
     }
 
     new_field= new Field_new_decimal(len, maybe_null, item->name,


Attachment: [text/bzr-bundle] bzr/[e-mail]