| Bug #4835 | insert, numeric column, string with float value, strange result | ||
|---|---|---|---|
| Submitted: | 30 Jul 2004 21:33 | Modified: | 31 Jul 2004 19:43 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 4.1 | OS: | |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
[30 Jul 2004 21:35]
Matthias Leich
test case
Attachment: ml_err16.test (application/octet-stream, text), 581 bytes.
[30 Jul 2004 22:59]
Sergei Golubchik
1. document as is. What happens is that for integer columns MySQL converts the string to integer, so, converting "2.0e+6" you naturally get 2, as "." is not a part of a valid integer number. For floating point numbers MySQL converts the string to float, and "2.0e+6" is the valid float. MySQL cannot convert string to float for integer types (that is it cannot convert string->double->integer) because double has lower precision than bigint, and reading the string as a float you won't be able to store "18446744073709551612" number correctly
[31 Jul 2004 19:43]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant product(s). Additional info: See: http://dev.mysql.com/doc/mysql/en/INSERT.html
Description: If I assign a string with a float value to a numeric column may occur strange effects. Example: create table t1 ( insert_value CHAR(30),str2int INT, num2int INT, str2decimal DECIMAL(10), num2decimal DECIMAL(10) ); insert into t1 values ( '1000000', '1000000', 1000000, '1000000', 1000000 ) ; insert into t1 values ( '1.0e+6', '1.0e+6', 1.0e+6, '1.0e+6', 1.0e+6 ) ; -> Warning 1265 Data truncated for column 'str2int' at row 1 !! What is the problem with the INT column or why is there no problem with the DECIMAL column ?? !! select * from t1 ; insert_value 1000000 str2int 1000000 num2int 1000000 str2decimal 1000000 num2decimal 1000000 ! OK, all is how expected . --------------------- --------------------- insert_value 2.0e+6 str2int 2 <------- That value seems to be the result of the truncation. num2int 2000000 str2decimal 2000000 num2decimal 2000000 If I look into the current manual chapter "14.1.4 INSERT Syntax" there is only: Assigning a value such as '10.34 a' to a numeric column. The trailing non-numeric text is stripped off and the remaining numeric part is inserted. If the string value has no leading numeric part, the column is set to 0. That means that the truncation is intended, but why is this behaviour not applied to the DECIMAL column. Some additional observations: - +2.0e+6 and 2.0e6 (without '+') cause the same effect - INT, BIGINT --> truncation - DECIMAL, REAL, FLOAT, DOUBLE --> no truncation My environment: Intel PC with Linux(SuSE 9.0) MySQL 4.1 source distrib. (last ChangeSet@1.1942.1.3, 2004-07-29) How to repeat: Please use my test file, copy it to mysql-test/t ./mysql-test-run ml_err16 Suggested fix: Please decide between: 1. The current behaviour will be documented within the manual 2. The INT/BIGINT behaviour will be also applied to DECIMAL/REAL,... 3. The DECIMAL/REAL/.. behaviour will be also applied to INT/BIGINT and the manual will be altered. I believe 3. would be the best solution.