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