Bug #24541 Sporadic "Data truncated..." warnings on decimal type columns
Submitted: 23 Nov 2006 12:22 Modified: 28 Apr 2010 1:54
Reporter: Valeriy Kravchuk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0.32-BK, 5.0.22 OS:Linux (Linux)
Assigned to: Alexey Kopytov CPU Architecture:Any
Tags: bfsm_2006_12_07, bfsm_2007_04_19, bfsm_2007_05_31
Triage: D2 (Serious)

[23 Nov 2006 12:22] Valeriy Kravchuk
Description:
Warnings "Data truncated for column <column> at row <row>" are given out for some (constat) values that are not having too high precision. Look:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.32-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table table1 (column1 decimal(10,6));
iQuery OK, 0 rows affected (0.01 sec)

mysql> insert into table1 (column1) values (9.99e-4);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1265
Message: Data truncated for column 'column1' at row 1
1 row in set (0.00 sec)

And, at the same time:

mysql> insert into table1 (column1) values (9.98e-4); -- different value, same "precision"
Query OK, 1 row affected (0.00 sec)

mysql> insert into table1 (column1) values (0.000999); -- same value, in fact!
Query OK, 1 row affected (0.00 sec)

And, with explicit cast:

mysql> insert into table1 (column1) values (cast(9.99e-4 as decimal(10,6)));
Query OK, 1 row affected (0.00 sec)

We do not have any warnings! I expect the last statement is exactly what should be done by default, as we treat numeric constants as FLOAT/DOUBLE:

mysql> create view vvvvv as select 9.99e-4 as c1;
Query OK, 0 rows affected (0.01 sec)

mysql> show fields from vvvvv;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| c1    | double | NO   |     | 0       |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.01 sec)

So, I think, it is a bug.

How to repeat:
create table table1 (column1 decimal(10,6));
insert into table1 (column1) values (9.99e-4);
show warnings\G
insert into table1 (column1) values (9.98e-4);
insert into table1 (column1) values (0.000999);
insert into table1 (column1) values (cast(9.99e-4 as decimal(10,6)));

Suggested fix:
Cast constant to column time implicitely, to have results like in:

insert into table1 (column1) values (cast(9.99e-4 as decimal(10,6)));
[8 Dec 2007 19:49] Alexey Kopytov
Fixed by the patch for WL #2934.
[25 Jan 2008 12:38] Bugs System
Pushed into 6.0.5-alpha
[25 Jan 2008 19:59] Paul Dubois
Noted in 6.0.5 changelog.

"Data truncated for column col_num at row row_num" warnings were
generated for some (constant) values that did not have too high
precision.
[7 Jan 2010 16:33] Paul Dubois
Setting report to NDI pending push to Celosia.
[24 Feb 2010 20:29] Paul Dubois
Setting report to Need Merge pending push of Celosia to release tree.
[6 Mar 2010 11:08] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@fedora12-20091225154921-x25a5pyw1pxiwobv) (merge vers: 5.5.99) (pib:16)
[6 Mar 2010 19:30] Paul Dubois
Noted in 5.5.3 changelog.
[27 Apr 2010 9:48] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (version source revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (merge vers: 5.5.5-m3) (pib:16)
[27 Apr 2010 9:51] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100427094036-38frbg3famdlvjup) (version source revid:alik@sun.com-20100427093825-92wc8b22d4yg34ju) (pib:16)
[28 Apr 2010 1:54] Paul Dubois
Already fixed in 5.5.x.