bzr commit into mysql-5.1-bugteam branch (davi:2994) Bug#45261

[Davi Arnaut]

# At a local mysql-5.1-bugteam repository of davi

 2994 Davi Arnaut	2009-07-04
      Bug#45261: Crash, stored procedure + decimal
      
      The problem was that creating a DECIMAL column from decimal
      value could lead to a assertion as decimal values can have
      a higher precision than those attached to a table. The assert
      could be triggered by creating a table from a decimal with
      a large (> 30) scale.
      
      The solution is to ensure that truncation procedure is executed
      when deducing a DECIMAL column from a decimal value of higher
      precision. If the integer part is equal to or bigger than the
      maximum precision for the DECIMAL type (65), the integer part
      is truncated to fit and the fractional becomes zero. Otherwise,
      the fractional part is truncated to fit into the space left
      after the integer part is copied.
      
      This patch borrows code and ideas from Martin Hansson's patch.
     @ mysql-test/r/type_newdecimal.result
        Add test case result for Bug#45261
     @ mysql-test/t/type_newdecimal.test
        Add test case for Bug#45261
     @ sql/field.cc
        Added DBUG_ASSERT to ensure object's invariant is maintained.
     @ sql/field.h
        Explain member variable.
     @ sql/sql_select.cc
        Implement new truncation procedure.

    modified:
      mysql-test/r/type_newdecimal.result
      mysql-test/t/type_newdecimal.test
      sql/field.cc
      sql/field.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-04 04:05:37 +0000
@@ -1521,13 +1521,13 @@ f1
 DROP TABLE t1;
 CREATE TABLE t1 SELECT
123451234512345123451234512345123451234512345.678906789067890678906789067890678906789067890
AS f1;
 Warnings:
-Warning	1264	Out of range value for column 'f1' at row 1
+Note	1265	Data truncated for column 'f1' at row 1
 DESC t1;
 Field	Type	Null	Key	Default	Extra
-f1	decimal(59,30)	NO		0.000000000000000000000000000000	
+f1	decimal(65,20)	NO		0.00000000000000000000	
 SELECT f1 FROM t1;
 f1
-99999999999999999999999999999.999999999999999999999999999999
+123451234512345123451234512345123451234512345.67890678906789067891
 DROP TABLE t1;
 select (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 *
 1.01500000 * 1.01500000 * 0.99500000);
@@ -1577,3 +1577,144 @@ 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#45261: Crash, stored procedure + decimal
+#
+CREATE TABLE t1 SELECT
+/* 81 */
100000000000000000000000000000000000000000000000000000000000000000000000000000001
+AS c1;
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+DESC t1;
+Field	Type	Null	Key	Default	Extra
+c1	decimal(65,0)	NO		0	
+SELECT * FROM t1;
+c1
+99999999999999999999999999999999999999999999999999999999999999999
+DROP TABLE t1;
+CREATE TABLE t1 SELECT
+/* 81 */
100000000000000000000000000000000000000000000000000000000000000000000000000000001.
+AS c1;
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+DESC t1;
+Field	Type	Null	Key	Default	Extra
+c1	decimal(65,0)	NO		0	
+SELECT * FROM t1;
+c1
+99999999999999999999999999999999999999999999999999999999999999999
+DROP TABLE t1;
+CREATE TABLE t1 SELECT
+/* 81 */
100000000000000000000000000000000000000000000000000000000000000000000000000000001.1 /* 1
*/
+AS c1;
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+DESC t1;
+Field	Type	Null	Key	Default	Extra
+c1	decimal(65,0)	NO		0	
+SELECT * FROM t1;
+c1
+99999999999999999999999999999999999999999999999999999999999999999
+DROP TABLE t1;
+CREATE TABLE t1 SELECT
+/* 82 */
1000000000000000000000000000000000000000000000000000000000000000000000000000000001
+AS c1;
+Warnings:
+Error	1292	Truncated incorrect DECIMAL value: ''
+DESC t1;
+Field	Type	Null	Key	Default	Extra
+c1	decimal(65,0)	NO		0	
+SELECT * FROM t1;
+c1
+99999999999999999999999999999999999999999999999999999999999999999
+DROP TABLE t1;
+CREATE TABLE t1 SELECT
+/* 40 */
1000000000000000000000000000000000000001.1000000000000000000000000000000000000001 /* 40
*/
+AS c1;
+DESC t1;
+Field	Type	Null	Key	Default	Extra
+c1	decimal(65,25)	NO		0.0000000000000000000000000	
+SELECT * FROM t1;
+c1
+1000000000000000000000000000000000000001.1000000000000000000000000
+DROP TABLE t1;
+CREATE TABLE t1 SELECT
+/* 1 */
1.10000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 80
*/
+AS c1;
+DESC t1;
+Field	Type	Null	Key	Default	Extra
+c1	decimal(31,30)	NO		0.000000000000000000000000000000	
+SELECT * FROM t1;
+c1
+1.100000000000000000000000000000
+DROP TABLE t1;
+CREATE TABLE t1 SELECT
+/* 1 */
1.100000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 81
*/
+AS c1;
+DESC t1;
+Field	Type	Null	Key	Default	Extra
+c1	decimal(31,30)	NO		0.000000000000000000000000000000	
+SELECT * FROM t1;
+c1
+1.100000000000000000000000000000
+DROP TABLE t1;
+CREATE TABLE t1 SELECT
+.100000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 81
*/
+AS c1;
+Warnings:
+Note	1265	Data truncated for column 'c1' at row 1
+DESC t1;
+Field	Type	Null	Key	Default	Extra
+c1	decimal(30,30)	NO		0.000000000000000000000000000000	
+SELECT * FROM t1;
+c1
+0.100000000000000000000000000000
+DROP TABLE t1;
+CREATE TABLE t1 SELECT
+/* 45 */
123456789012345678901234567890123456789012345.123456789012345678901234567890123456789012345
/* 45 */
+AS c1;
+Warnings:
+Note	1265	Data truncated for column 'c1' at row 1
+DESC t1;
+Field	Type	Null	Key	Default	Extra
+c1	decimal(65,20)	NO		0.00000000000000000000	
+SELECT * FROM t1;
+c1
+123456789012345678901234567890123456789012345.12345678901234567890
+DROP TABLE t1;
+CREATE TABLE t1 SELECT
+/* 65 */ 12345678901234567890123456789012345678901234567890123456789012345.1 /* 1 */
+AS c1;
+Warnings:
+Note	1265	Data truncated for column 'c1' at row 1
+DESC t1;
+Field	Type	Null	Key	Default	Extra
+c1	decimal(65,0)	NO		0	
+SELECT * FROM t1;
+c1
+12345678901234567890123456789012345678901234567890123456789012345
+DROP TABLE t1;
+CREATE TABLE t1 SELECT
+/* 66 */ 123456789012345678901234567890123456789012345678901234567890123456.1 /* 1 */
+AS c1;
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+DESC t1;
+Field	Type	Null	Key	Default	Extra
+c1	decimal(65,0)	NO		0	
+SELECT * FROM t1;
+c1
+99999999999999999999999999999999999999999999999999999999999999999
+DROP TABLE t1;
+CREATE TABLE t1 SELECT
+.123456789012345678901234567890123456789012345678901234567890123456 /* 66 */
+AS c1;
+Warnings:
+Note	1265	Data truncated for column 'c1' at row 1
+DESC t1;
+Field	Type	Null	Key	Default	Extra
+c1	decimal(30,30)	NO		0.000000000000000000000000000000	
+SELECT * FROM t1;
+c1
+0.123456789012345678901234567890
+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-04 04:05:37 +0000
@@ -1257,3 +1257,91 @@ 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#45261: Crash, stored procedure + decimal
+--echo #
+
+CREATE TABLE t1 SELECT
+  /* 81 */
100000000000000000000000000000000000000000000000000000000000000000000000000000001
+  AS c1;
+DESC t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 SELECT
+  /* 81 */
100000000000000000000000000000000000000000000000000000000000000000000000000000001.
+  AS c1;
+DESC t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 SELECT
+  /* 81 */
100000000000000000000000000000000000000000000000000000000000000000000000000000001.1 /* 1
*/
+  AS c1;
+DESC t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 SELECT
+  /* 82 */
1000000000000000000000000000000000000000000000000000000000000000000000000000000001
+  AS c1;
+DESC t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 SELECT
+  /* 40 */
1000000000000000000000000000000000000001.1000000000000000000000000000000000000001 /* 40
*/
+  AS c1;
+DESC t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 SELECT
+  /* 1 */
1.10000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 80
*/
+  AS c1;
+DESC t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 SELECT
+  /* 1 */
1.100000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 81
*/
+  AS c1;
+DESC t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 SELECT
+  .100000000000000000000000000000000000000000000000000000000000000000000000000000001 /*
81 */
+  AS c1;
+DESC t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 SELECT
+  /* 45 */
123456789012345678901234567890123456789012345.123456789012345678901234567890123456789012345
/* 45 */
+  AS c1;
+DESC t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 SELECT
+  /* 65 */ 12345678901234567890123456789012345678901234567890123456789012345.1 /* 1 */
+  AS c1;
+DESC t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 SELECT
+  /* 66 */ 123456789012345678901234567890123456789012345678901234567890123456.1 /* 1 */
+  AS c1;
+DESC t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 SELECT
+  .123456789012345678901234567890123456789012345678901234567890123456 /* 66 */
+  AS c1;
+DESC t1;
+SELECT * FROM t1;
+DROP TABLE t1;

=== modified file 'sql/field.cc'
--- a/sql/field.cc	2009-06-09 16:44:26 +0000
+++ b/sql/field.cc	2009-07-04 04:05:37 +0000
@@ -2485,6 +2485,7 @@ Field_new_decimal::Field_new_decimal(uin
 {
   precision= my_decimal_length_to_precision(len_arg, dec_arg, unsigned_arg);
   set_if_smaller(precision, DECIMAL_MAX_PRECISION);
+  DBUG_ASSERT(precision >= dec);
   DBUG_ASSERT((precision <= DECIMAL_MAX_PRECISION) &&
               (dec <= DECIMAL_MAX_SCALE));
   bin_size= my_decimal_get_binary_size(precision, dec);

=== modified file 'sql/field.h'
--- a/sql/field.h	2009-06-09 16:44:26 +0000
+++ b/sql/field.h	2009-07-04 04:05:37 +0000
@@ -608,6 +608,10 @@ protected:
 
 class Field_num :public Field {
 public:
+  /**
+     The scale of the Field's value, i.e. the number of digits to the right
+     of the decimal point.
+  */
   const uint8 dec;
   bool zerofill,unsigned_flag;	// Purify cannot handle bit fields
   Field_num(uchar *ptr_arg,uint32 len_arg, uchar *null_ptr_arg,

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2009-06-26 19:57:42 +0000
+++ b/sql/sql_select.cc	2009-07-04 04:05:37 +0000
@@ -9368,40 +9368,79 @@ static Field *create_tmp_field_from_item
     break;
   case DECIMAL_RESULT:
   {
-    uint8 dec= item->decimals;
-    uint8 intg= ((Item_decimal *) item)->decimal_precision() - dec;
-    uint32 len= item->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.
-    */
+      The MySQL DECIMAL data type has a characteristic that needs to be
+      taken into account when deducing the type from a Item_decimal.
 
-    if (dec > 0)
-    {
-      signed int overflow;
+      But first, let's briefly recap what is the new MySQL DECIMAL type:
 
-      dec= min(dec, DECIMAL_MAX_SCALE);
+      The declaration syntax for a decimal is DECIMAL(M,D), where:
 
-      /*
-        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.
-        +1: for decimal point
-      */
+      * M is the maximum number of digits (the precision).
+        It has a range of 1 to 65.
+      * D is the number of digits to the right of the decimal separator (the scale).
+        It has a range of 0 to 30 and must be no larger than M.
 
-      overflow= my_decimal_precision_to_length(intg + dec, dec,
-                                               item->unsigned_flag) - len;
+      D and M are used to determine the storage requirements for the integer
+      and fractional parts of each value. The integer part is to the left of
+      the decimal separator and to the right is the fractional part. Hence:
 
-      if (overflow > 0)
-        dec= max(0, dec - overflow);            // too long, discard fract
-      else
-        len -= item->decimals - dec;            // corrected value fits
+      M is the number of digits for the integer and fractional part.
+      D is the number of digits for the fractional part.
+
+      Consequently, M - D is the number of digits for the integer part. For
+      example, a DECIMAL(20,10) column has ten digits on either side of the
+      decimal separator.
+
+      The characteristic that needs to be taken into account is that the
+      backing type for Item_decimal is a my_decimal that has a higher precision
+      (DECIMAL_MAX_POSSIBLE_PRECISION, see my_decimal.h) than DECIMAL.
+
+      Drawing a comparison between my_decimal and DECIMAL:
+
+      * M has a range of 1 to 81.
+      * D has a range of 0 to 72.
+
+      The difference in range is that the fractional part must always be on
+      on a group boundary, leaving one group for the integer part. Since each
+      group is 9 (DIG_PER_DEC1) digits and there are 9 (DECIMAL_BUFF_LENGTH)
+      groups, the fractional part is limited to 72 digits.
+
+      Although the backing type for a DECIMAL is also my_decimal, every time
+      a my_decimal is stored in a DECIMAL field, the precision and scale are
+      explicitly capped at 65 (DECIMAL_MAX_PRECISION) and 30 (DECIMAL_MAX_SCALE)
+      digits, following my_decimal truncation procedure (FIX_INTG_FRAC_ERROR).
+    */
+
+    uint8 scale= item->decimals;
+    uint8 precision= ((Item_decimal *) item)->decimal_precision();
+
+    /*
+      Employ a procedure along the lines of the my_decimal truncation process:
+      - If the integer part is equal to or bigger than the maximum precision:
+        Truncate integer part to fit and the fractional becomes zero.
+      - Otherwise:
+        Truncate fractional part to fit.
+    */
+
+    uint intg= precision - scale;
+
+    if (intg >= DECIMAL_MAX_PRECISION)
+    {
+      intg= DECIMAL_MAX_PRECISION;
+      scale= 0;
+    }
+    else
+    {
+      uint room= min(DECIMAL_MAX_PRECISION - intg, DECIMAL_MAX_SCALE);
+      if (scale > room)
+        scale= room;
     }
 
-    new_field= new Field_new_decimal(len, maybe_null, item->name,
-                                     dec, item->unsigned_flag);
+    uint len= my_decimal_precision_to_length(intg + scale, scale,
+                                             item->unsigned_flag);
+    new_field= new Field_new_decimal(len, maybe_null, item->name, scale,
+                                     item->unsigned_flag);
     break;
   }
   case ROW_RESULT:


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