MySQL Stored Procedures – Data Truncated for Column

MySQLstored-procedures

DECLARE float_one, float_two, my_result NUMERIC(7,2)    
my_result = CONVERT(float_one/float_two, DECIMAL(7,2));

In this mysql query, I do this type of operation in a stored procedure, but the Linux environment phpmyadmin throws the following warning:

Note: #1265 Data truncated for column 'float_one' at row 5

Does anyone have an idea how can I solve this problem?

CREATE TABLE IF NOT EXISTS `float_sample` (
      `id` int(11) NOT NULL auto_increment,
      `first_number` float(10,3) default NULL,
      `second_number` float(10,3) default NULL,
      PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `float_sample` (`id`, `first_number`, `second_number`) 
VALUES    (1, 2.900, 1.900),    (2, 3.100, 22.100);

and the procedure

DELIMITER $$
DROP PROCEDURE IF EXISTS float_test$$
CREATE PROCEDURE float_test(IN my_ID INT)  

BEGIN
DECLARE first_float, second_float DECIMAL(10,3);

DECLARE done INT DEFAULT 0;

DECLARE myCursor CURSOR FOR 
        SELECT `first_number`, `second_number`
        FROM `float_sample`
        WHERE `id` = my_ID;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN myCursor;
my_loop:LOOP
    FETCH myCursor INTO first_float, second_float;

    IF done = 1 THEN
        LEAVE my_loop;
    END IF;

END LOOP my_loop;
CLOSE myCursor;

-- SELECT first_float, second_float;
END;$$
DELIMITER ;

and the result

1 70 10:41:36    CALL mytests.float_test(1) 0 row(s) affected, 2 warning(s):
1265 Data truncated for column 'first_float' at row 2
1265 Data truncated for column 'second_float' at row 2  0.032 sec

Best Answer

What is the the value of float_one at the time of conversion ???

Note this example from MySQL 5.5.12 in Windows

mysql> select convert(20000,decimal(7,2));
+-----------------------------+
| convert(20000,decimal(7,2)) |
+-----------------------------+
|                    20000.00 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select convert(200000,decimal(7,2));
+------------------------------+
| convert(200000,decimal(7,2)) |
+------------------------------+
|                     99999.99 |
+------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------+
| Level   | Code | Message                                                               |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'convert(200000,decimal(7,2))' at row 1 |
+---------+------+-----------------------------------------------------------------------+

1 row in set (0.00 sec)

It may be possible that data was truncated if a number bigger than 99999.99 was in float_one. Perhaps, mysql was converting float_one and float_two to DECIMAL(7,2) individually before performing division. Try using DECIMAL(10,2) or greater to accommodate large values.

UPDATE 2011-07-25 15:05 EDT

There are definite truncation problem going on here

According to MySQL Reference Manual Page 442 Paragraphs 2,3

DECIMAL and NUMERIC values are stored as strings, rather than as binary floating point numbers, in order to preserve the decimal precision of those numbers. One character is used for each digit of the value, the deciaml point (if scale > 0) and the - sign (for negative numbers). If the scale is 0, DECIMAL and NUMERIC values contain no decimal point or fractional part.

The maximum range of DECIMAL and NUMERIC is the same as for DOUBLE, but the actual range for the given DECIMAL or NUMERIC column can be constrainted by the precision and scale for a give column, When such a column is assigned a value with more digits following the decimal point than are allowed by the specified scale, the value is rounded to that scale. When a DECIMAL or NUMERIC column is assigned a value whose magnitude exceeds the range implied by the specified (or defaulted) precision and scale, MySQL stores the value representing the corresponding endpoint of that range.

You need to accommodate a larger precision and/or scale.

Here is an example as to why

I wrote this stored procedure using your specifications for DECMIAL and NUMERIC.

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`NumTest` $$
CREATE PROCEDURE `test`.`NumTest` (num1 NUMERIC(7,2), num2 NUMERIC(7,2))
BEGIN

  DECLARE float_one,float_two,my_result NUMERIC(7,2);
  DECLARE f1,f2 DOUBLE(7,2);

  SET f1 = num1;
  SET f2 = num2;

  SET float_one = num1;
  SET float_two = num2;

  SELECT f1 / f2;
  SELECT float_one / float_two;
  SELECT CONVERT(float_one / float_two,DECIMAL(7,2));
  SET my_result = CONVERT(float_one / float_two,DECIMAL(7,2));
  SELECT my_result;

END $$

DELIMITER ;

I used two values: 290.0 and 14.5 for this test.

Before calling the NumTest stored procedure, I manually calculated 290.0 / 14.5

mysql> select 290.0 / 14.5;
+--------------+
| 290.0 / 14.5 |
+--------------+
|     20.00000 |
+--------------+
1 row in set (0.00 sec)

I divided each number by 100, 10000, and 1000000 and tried again

mysql> select 2.9 / .145;
+------------+
| 2.9 / .145 |
+------------+
|   20.00000 |
+------------+
1 row in set (0.00 sec)

mysql> select .029 / .00145;
+---------------+
| .029 / .00145 |
+---------------+
|    20.0000000 |
+---------------+
1 row in set (0.00 sec)

mysql> select .00029 / .0000145;
+-------------------+
| .00029 / .0000145 |
+-------------------+
|      20.000000000 |
+-------------------+
1 row in set (0.00 sec)

So far, so good !!! Now for the stored procedure.

mysql> call numtest(290.0,14.5);
+-----------+
| f1 / f2   |
+-----------+
| 20.000000 |
+-----------+
1 row in set (0.00 sec)

+-----------------------+
| float_one / float_two |
+-----------------------+
|             20.000000 |
+-----------------------+
1 row in set (0.00 sec)

+---------------------------------------------+
| CONVERT(float_one / float_two,DECIMAL(7,2)) |
+---------------------------------------------+
|                                       20.00 |
+---------------------------------------------+
1 row in set (0.00 sec)

+-----------+
| my_result |
+-----------+
|     20.00 |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

So my orignal two numbers work fine. Let's divide them by 100 and try again.

mysql> call numtest(2.9,0.145);
+-----------+
| f1 / f2   |
+-----------+
| 19.333333 |
+-----------+
1 row in set (0.00 sec)

+-----------------------+
| float_one / float_two |
+-----------------------+
|             19.333333 |
+-----------------------+
1 row in set (0.00 sec)

+---------------------------------------------+
| CONVERT(float_one / float_two,DECIMAL(7,2)) |
+---------------------------------------------+
|                                       19.33 |
+---------------------------------------------+
1 row in set (0.00 sec)

+-----------+
| my_result |
+-----------+
|     19.33 |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Note  | 1265 | Data truncated for column 'num2' at row 2 |
+-------+------+-------------------------------------------+
1 row in set (0.00 sec)

WAIT, we lost some precision. What happened ??? How did this happen ??? You need to accommodate more decmial digits (> 2)

UPDATE 2011-07-25 17:37 EDT

I substituted (7,2) with (10,7) in the stored procedure and got back the proper precision

mysql> call numtest(2.9,0.145);
+----------------+
| f1 / f2        |
+----------------+
| 20.00000000000 |
+----------------+
1 row in set (0.00 sec)

+-----------------------+
| float_one / float_two |
+-----------------------+
|        20.00000000000 |
+-----------------------+
1 row in set (0.01 sec)

+----------------------------------------------+
| CONVERT(float_one / float_two,DECIMAL(10,7)) |
+----------------------------------------------+
|                                   20.0000000 |
+----------------------------------------------+
1 row in set (0.03 sec)

+------------+
| my_result  |
+------------+
| 20.0000000 |
+------------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.06 sec)

mysql>
Related Question