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

Bug #7361	Strange Data Truncation with float(M,30)
Submitted:	16 Dec 2004 20:49	Modified:	12 Jan 2005 20:09
Reporter:	Bill Adams	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.8	OS:	Linux (Linux)
Assigned to:	Jim Winstead	CPU Architecture:	Any

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)