| Bug #5270 | strange results during conversion floating point -> string | ||
|---|---|---|---|
| Submitted: | 27 Aug 2004 22:15 | Modified: | 2 Sep 2004 15:48 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1 | OS: | |
| Assigned to: | Sergei Golubchik | CPU Architecture: | Any |
[27 Aug 2004 22:19]
Matthias Leich
test case
Attachment: ml_err28.test (application/octet-stream, text), 1.13 KiB.
[31 Aug 2004 6:04]
MySQL Verification Team
Thank you for the bug report. I was able to repeat with latest 4.1.4 Windows server.
[1 Sep 2004 8:00]
Brian Aker
Paul, is this issue documented well enough?
[2 Sep 2004 15:17]
Sergei Golubchik
data truncation is a bug here
[2 Sep 2004 15:48]
Sergei Golubchik
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 bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Description: The conversion of floating point values into strings can show strange undocumented results. Example: create table t1 ( c1 tinyint, insert_val char (30), char_2 char(2), char_10 char(10), char_30 char(30), primary key(c1) ) ; several sequences with set @arg00= <float value> ; insert into t1 ( c1, insert_val, char_2, char_10, char_30 ) values ( <small integer>, 'set @arg00= <float value>', @arg00, @arg00, @arg00 ) ; .... select * from t1; in char_10 c1 insert_val char_2 char_10 char_30 TRUNCATION 50 set @arg00= -50.0E-32 -5 -5e-31 -5e-31 no 51 set @arg00= -5.0E-31 -5 -5e-31 -5e-31 no 52 set @arg00= -5.0E+2 -5 -500 -500 no 53 set @arg00= 4E+16 4e 4e+16 4e+16 no 54 set @arg00= 4E+12 40 4000000000 4000000000000 yes 55 set @arg00= 4E+9 40 4000000000 4000000000 no Hypothesis: There seems to be an implicit conversion of the float value stored within the user variable to BIGINT if possible (c1 in (52,54,55)). The string column gets the float value direct, if this value exceeds the BIGINT range . 1. This behaviour is not documented (http://dev.mysql.com/doc/mysql/en/INSERT.html ..). 2. We have a bad anomaly If I try to insert a "midsize" float like 4E+12, there will be a truncation during the insert into char_10 and we get there only "4000000000" (= 4E+9). If I try to insert a bigger float value like 4e+16, there will be no truncation during the insert. So the question arizes: If I assign a float user variable to a string column, why is the server not able to store it always in float style. So everybody would get clear conversion results depending on the type of the user variable. He will get back what he puts in ! And the anomaly would be avoided. My environment: Intel PC with Linux(SuSE 9.0) MySQL 4.1 source distrib. (last bk pull 27.08.2004) How to repeat: How to repeat: Please use my test file, copy it to mysql-test/t ./mysql-test-run ml_err28 Suggested fix: Please choose between 1. assignment of float values to string columns gives a float style string or 2. The current behaviour will be described within the manual. I would prefer 1.