MySQL Bugs: #7361: Strange Data Truncation with float(M,30)

Bug #7361	Strange Data Truncation with float(M,30)
Submitted:	16 Dec 2004 21:49	Modified:	12 Jan 2005 21:09
Reporter:	Bill Adams
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	4.1.8	OS:	Linux (Linux)
Assigned to:	Jim Winstead	Target Version:

Description:
There is a strange thing happening when a float(M,N) where N is >= 30. Specifically, it
gives a warning "Data truncated; out of range for column 'value' at row 1" and it clamps
the value to +10 or -10.

Also, it only does it if there is a number after the decimal. In the example below, if you
just insert "14" you get no error and the value gets correctly inserted. If you insert
"14.0" then the error happens.

Honestly, I am not sure how much this matters because I can just converting my columns to
the normal float/double data type anyway. But in case you care.

This bug existed in 4.1.7 as well.

How to repeat:
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 20 to server version: 4.1.8-standard-log

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

mysql> drop table if exists float_test; create table float_test ( value float(33,30));
insert into float_test values ( -14.0 ); show warnings; select * from float_test;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.08 sec)

Query OK, 1 row affected, 1 warning (0.01 sec)

+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1264 | Data truncated; out of range for column 'value' at row 1 |
+---------+------+----------------------------------------------------------+
1 row in set (0.00 sec)

+------------------------------------+
| value                              |
+------------------------------------+
| -10.000000000000000000000000000000 |
+------------------------------------+
1 row in set (0.00 sec)

The truncation is correct, and should also have been happening when inserting
an integer. The truncation in your case is actually to a rounded-off -9.999(etc).

Mentioned in 4.1.9 change notes.

same happed in 4.1.7 when embeded query into java and inset data into table , an Exception
happened but data was inserted correctly, process was brocken:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column
'orderstatus' at row 658
at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:693)
	at com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:3286)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1728)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2228)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2159)
	at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1243)
	at com.trlm.db.ExecuteMySql.GetSqlUpdate(ExecuteMySql.java:44)
	at com.trlm.db.DatabaseAccessor.updateMySql(DatabaseAccessor.java:92)
	at com.trlm.acounting.act_splitdata.split_orders(act_splitdata.java:95)
	at com.trlm.acounting.act_splitdata.splitdata(act_splitdata.java:33)
	at com.trlm.acounting.act_server.run_splitdata(act_server.java:170)
	at com.trlm.acounting.act_server.main(act_server.java:201)