MySQL Bugs: #24541: Sporadic "Data truncated..." warnings on decimal type columns

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

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)));

Fixed by the patch for WL #2934.

Pushed into 6.0.5-alpha

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.

Setting report to NDI pending push to Celosia.

Setting report to Need Merge pending push of Celosia to release tree.

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)

Noted in 5.5.3 changelog.

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100427094036-38frbg3famdlvjup) (version source revid:alik@sun.com-20100427093825-92wc8b22d4yg34ju) (pib:16)

Already fixed in 5.5.x.