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:15] Matthias Leich
The conversion of floating point values into strings can show strange undocumented results.
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

   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 
    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
2. The current behaviour will be described within the manual.

I would prefer 1.
[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