MySQL Bugs: #63704: Conversion of '1.' to a number gives error 1265 (WARN_DATA

Bug #63704	Conversion of '1.' to a number gives error 1265 (WARN_DATA_TRUNCATED)
Submitted:	9 Dec 2011 20:00	Modified:	9 Feb 2012 11:57
Reporter:	Vyacheslav Brover	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.5.15-log, 5.5.17	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	1265, Converison, number, string, WARN_DATA_TRUNCATED

Description:
Conversion of '1.' to a number in SQL statements gives error 1265 (WARN_DATA_TRUNCATED).

How to repeat:
create table test.AA (F float);
insert into test.AA (F) values ('1.');  
  # Error message: "Data truncated for column 'F' at row 1"  ErrorNr. 1265
This situation often happens when importing data from text files.

-- There is an inconsistence with FLOAT/DOUBLE and other numerical types here:

CREATE TABLE test.aa (F FLOAT);
INSERT INTO test.aa (F) VALUES ('1.');  -- error or warning dependent on sql_mode

CREATE TABLE test.bb (I INTEGER);
INSERT INTO test.bb (I) VALUES ('1.');  -- 1 row(s) affected

CREATE TABLE test.cc (D DECIMAL);
INSERT INTO test.cc (D) VALUES ('1.');  -- 1 row(s) affected

--Also it seems only to happen when inserting to a table. Not when casting happens in memory 
SELECT CAST(1. AS SIGNED); -- returns "1"

-- now we cannot cast to float unless doing a trick It is evaluated as "1".
SELECT 1. + 'a'; -- -- returns "1" (1+0)

Peter
(not a MySQL person)

Thank you for the problem report. Verified just as described:

macbook-pro:5.5 openxs$ 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 2
Server version: 5.5.17-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table test.AA (F float);
Query OK, 0 rows affected (0.17 sec)

mysql> insert into test.AA (F) values ('1.');  
Query OK, 1 row affected, 1 warning (0.02 sec)

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

mysql> select * from AA;
+------+
| F    |
+------+
|    1 |
+------+
1 row in set (0.03 sec)

mysql> insert into test.AA (F) values ('1.0');  
Query OK, 1 row affected (0.01 sec)

mysql> insert into test.AA (F) values ('.1');  
Query OK, 1 row affected (0.01 sec)

mysql> select * from AA;
+------+
| F    |
+------+
|    1 |
|    1 |
|  0.1 |
+------+
3 rows in set (0.00 sec)

One more observation: It makes a difference if the number is 'quoted':

INSERT INTO test.aa (F) VALUES (1.); -- success
INSERT INTO test.aa (F) VALUES ('1.'); -- warning

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Fixed in 5.6. Documented in the 5.6.5 changelog as follows:

      Converting a string ending with a decimal point (such as '1.')
      to a floating-point number raised a data truncation warning.

Closed.