Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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
share|improve this question
    
I'm not sure, but it is possible to get the warning from a DECLARE cursor_one CURSOR FOR SELECT float_one, float_two WHERE .....?? –  dole doug Jul 25 '11 at 16:58
    
I updated my answer with valid reasons for precision truncation. –  RolandoMySQLDBA Jul 25 '11 at 19:06
    
@dole - what is "the problem" with the truncation other than the warning message? –  Jack Douglas Jul 25 '11 at 19:40
1  
I changed the precision in my answer's stored procedure from (7,2) to (10,7) and it's running perfect again. Since all numbers in your table is no bigger than 50, the DECIMAL(10,7) should work out just fine for you. Mystery Solved !!! –  RolandoMySQLDBA Jul 25 '11 at 21:38
1  
This question is worthy of +1 because DECMIAL and NUMERIC are always taken for granted until precision goes bonkers on you. A lesson for us all is summed up in this one question. Thank you asking and helping DBAs and Developers get back to basics. –  RolandoMySQLDBA Jul 26 '11 at 6:16

3 Answers 3

up vote 2 down vote accepted

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>
share|improve this answer
    
all my float_one, float_two are smaller than 50, but they are float(7,2) in db –  dole doug Jul 25 '11 at 17:32
    
wow, ty for the ellaborate answer. –  dole doug Jul 26 '11 at 18:54
    
in my table, i've changed from float(7,2) to decimal(7,2) –  dole doug Jul 27 '11 at 11:15

From what I can see your stored procedure just loops through the rows - it does not 'save the data' anywhere and you have commented out the select:

-- SELECT first_float, second_float;

Yes you are getting warnings when the float(10,3) fields are converted to decimal(10,3). Whether they are a problem or not depends on what you want to do with the fields.

Is this just a sample of the real code? You may need to post some more...

share|improve this answer
    
I'll insert them into a table with float(10,3) fields. Inserts doesn't take place and I think it is the warnings fault. –  dole doug Jul 26 '11 at 19:30
    
How will you insert them? Your procedure does not return them. You need to show the code... –  Jack Douglas Jul 26 '11 at 19:32
    
it is nothing special about the insert. The code works fine under windows, even with the warnings. Under Linux environment, the insert doesn't take place and I assume that it's the warnings fault. Do you have any idea about how can I solve the warnings? I'm totally stucked. –  dole doug Jul 26 '11 at 19:59
    
The code you have shown does not do an insert. It does not even return any values. It throws two warnings that seem to be irrelevant, because whatever you are inserting does not come out of the procedure that generates the warnings. Show the actual code that "doesn't work" and any error messages that are thrown by that code, and we may be able to help you. –  Jack Douglas Jul 27 '11 at 5:27

If you r using if-condition you should close the if condition and u should put an else condition. its must.. Then execute it, it will work Good.. (eg:) IN MY CODE

if($cid =='') { $consumer_detail = db_insert('student') ->fields(array(

'name' => $name,
'age' => $age,
'country' => $country,
'place' => $place,
'id_no' => $id_no,
'reg_no' => $reg_no,
'state' => $state,
'hick1' => $hick1,
'hick2' => $hick2,
'hick3' => $hick3,
))

->execute(); drupal_set_message('Product Details Inserted Successfully.'); drupal_goto("form");

} else { echo "It's not working..."; }

} You Should End with a Else Part.. If It work's good Let me know ... If there Any ?.....

share|improve this answer
2  
I'm sorry I don't see what that has to do with the question above, at all. –  Mat Sep 23 '13 at 10:07
    
hmm.No dude, i have only given a simple example for how & why the 'Data Truncated for Column' error is creating –  Sujan Nov 4 '13 at 11:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.