MySQL Bugs: #14302: DOUBLE/FLOAT results, difference between MySQL 4.1 and 5.0, Upgrade

Bug #14302	DOUBLE/FLOAT results, difference between MySQL 4.1 and 5.0, Upgrade
Submitted:	25 Oct 2005 17:46	Modified:	25 Nov 2005 10:02
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0	OS:
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
There is an incompatibility between MySQL 4.1 and 5.0 in the area
of the data types DOUBLE and FLOAT. 
MySQL 4.1 and 5.0 can convert the same FLOAT input value
to different FLOAT/DOUBLE numbers.
This can be a real upgrade problem for some customers.

Example:
1. MySQL 4.1 
   Create table with column of data type DOUBLE
   Insert into <this table> SET <DOUBLE column> 
                                             = 1.175494345e-15

   All is fine like
   - Print of record shows  1.175494345e-15
   - SELECT ... WHERE <DOUBLE column> 
                                             = 1.175494345e-15
      gets exact this record as response

2. Upgrade to MySQL 5.0 (without mysqldump)
    Insert into <this table> SET <DOUBLE column> 
                                             = 1.175494345e-15

    Fine: Print of record inserted with 4.1 and 5.0
            shows  1.175494345e-1
    Bad: SELECT ... WHERE <DOUBLE column> 
                                           = 1.175494345e-15
            gets only the record inserted with 5.0
            as response
            A join shows a comparable bad behaviour.

            The reason is a difference of about
            value * 1e-15 between the values stored
            with different MySQL versions but the same
            statement. This is exact the precision of
            data type DOUBLE.

The manual mentions
A.5.7. Solving Problems with No Matching Rows
   ... 
   If you are comparing FLOAT or DOUBLE columns with numbers 
   that have decimals, you can't use equality (=) comparisons .
   (My example does not compare with numbers that have decimals!)
A.5.8. Problems with Floating-Point Comparisons
A customer might get an idea that a comparison  via "="
FLOAT/DOUBLE  with FLOAT/DOUBLE might be also problematic
and use different statements.

But this is not so probable, as long as  
             INSERT ... SET <double column> = <float value>
             SELECT ... WHERE <double column> = <float value>
works consistent and fine (and it does this in pure 4.1 or 5.0).
He will most probably use direct comparisons via "=" .

If this customer does an upgrade to MySQL 5.0, he will
be faced with the unexpected problem that an
             SELECT ... WHERE <double column> = <float value>
will no more catch the records inserted with MySQL 4.1.

Please have a look into the attached scripts and especially the
commented tests protocol.

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.2025.1.2, 2005-10-21
BTW: Version 4.1 was ChangeSet@1.2460, 2005-10-23

How to repeat:
1. Please create a new directory, cd to this new directory
    and store my attached files there.
2. Adjust the script floattest.sh
    Variables: TOP_DIR41, TOP_DIR50 and maybe VAR_DIR
3. Execute the script floattest.sh
4. Inspect mlfloat1.reject within the current directory

There might be the following issues with my bug replay scripts
(sorry for unconvenience)
1. floattest.sh fails, because you have binary and not
    source distributions
       Please adjust floattest.sh
2. There appears a mlfloat.reject
    Maybe there are differences between your and my MySQL
    versions. Please ignore minor differences. I SELECT the 
    versions for checking that I really work on the
    desired version/record.
3. You do not get the bad effects I observed
    Please experiment with the variable $FLOAT_VAL
    (You will have to change mlfloat.test AND mlfloat1.test).
    I get the first bad effect with
        1.175494345e-15
    maybe you should try a float value some magnitudes smaller.

Bourne shell script

Attachment: floattest.sh (application/x-shellscript, text), 5.08 KiB.

first test script

Attachment: mlfloat.test (application/test, text), 956 bytes.

second test script

Attachment: mlfloat1.test (application/test, text), 1.42 KiB.

expected results

Attachment: mlfloat.result (application/octet-stream, text), 931 bytes.

My test protocol showing the problem with comments

Attachment: mlfloat1.txt (text/plain), 3.42 KiB.

There seems to be a workaround:
If the upgrade to MySQL 5.0 is done via mysqldump (in MySQL 4.1)
of the databases and loading them into a fresh installed MySQL 5.0
the problem will disappear.
The reason is that a
SELECT * INTO OUTFILE .... FROM t1 produces the outfile content
1.175494345e-15 4.1.16-debug-log       1
Please note, that the printed float value is exact the value used during
Inserts and Selects. 
If you want to check that this kind of upgrade works fine, please
try the attached script  floattest-ok.sh. My corresponding protocol
,which shows the expected result sets, is mlfloat1-ok.txt.

Shellscript showing the correct working workaround

Attachment: floattest-ok.sh (application/x-shellscript, text), 5.12 KiB.

My protocoll when using floattest-ok.sh

Attachment: mlfloat1-ok.txt (text/plain), 2.21 KiB.

Please do not start to work on server code for this bug/problem.
1. I repeated this test with the latest MySQL Network binary distibutions for Linux
    (pure tar gzip packages, no RPM)
      mysql-pro-gpl-4.1.15-pc-linux-gnu-i686-glibc23
      mysql-pro-gpl-5.0.15-linux-i686-glibc23
    and I was not able to reproduce this bug.
2. I cloned the 4.1 and the 5.0 source development tree and compiled my own 
    source distributions and the problem also disappeared.
So it looks like the reason for the problem was in the history of my source trees.
I am not a developer of server code and I cannot remember that I did other 
things than pull+compile+develop some tests for mysqltests. Maybe the SuSE
update replaced some libs during the history of my suspicious source trees. 
But there were also several ./BUILD/compile-pentium-debug-max within the
last week an I thought they must rebuild everything.
BTW: I can archive my old source trees, but I assume it is not worth while to
        analyse them. 
So my conclusions are
- the risk that a customer using official binaries sees this bug/problem could be 
  be much lower  
  I did not check combinations of official binaries, which fit to my hardware and OS.
- the risk that somebody using his own source distibution or maybe a historical
  mixup of official binaries  like
      - MySQL 4.1 general release x86,  ( tar gzip archive, no glibc6 ...)
      Upgrade to
      - MySQL 5.0 RPM's for SuSE Enterprise ...
  sees this problem could be significant
  (I fear that important actions like use of different compilers or libs on the
   build engine could cause that the behaviour of FLOAT/DOUBLE changes.)

1. There is most probably no or only a minor bug within our code.
2. There is a documentation issue.
    The manual should warn in the sense of:
    You might have the strong "working" experience, that   
          INSERT ... SET <column of type double/float> = <float value>
          SELECT ... WHERE <column of type double/float> = <float value>
                 and you get the inserted record.
    works fine and everywhere and that the manual chapter 
    A.5.7. Solving Problems with No Matching Rows
    is most probably not valid for your installation/application.
    Please do not write code, which relies on this expectation.
    There is a significant risk that this expectation will
    be no more valid after an upgrade of your MySQL software 
    even on the same hardware and OS. 
    That means the paradox effect
    older MySQL: INSERT ... SET <column of type double/float> = <float value1>
    older MySQL: SELECT ... WHERE <column of type double/float> = <float value1>
                 and you get the inserted record
    --- Upgrade of the MySQL version -----------
    newer MySQL: INSERT ... SET <column of type double/float> = <float value2>
    newer MySQL: SELECT ... WHERE <column of type double/float> = <float value2>
                 and you get the inserted record
    but
    newer MySQL: SELECT ... WHERE <column of type double/float> = <float value1>
                 and you get the totally unexpected ROW NOT FOUND  !!
    might occur.

The same problem remains when using the Network binary distributions
mysql-pro-gpl-4.1.15-pc-linux-gnu-i686-glibc23 and
mysql-pro-gpl-5.0.15-linux-i686-glibc23 for columns of data type float.
History of the tables:
1. MySQL 5.0: create <comparison_table_1> and fill it with data
2. MySQL 5.0: dump the databases
3. downgrade to MySQL 4.1
4. MySQL 4.1: recreate the databases by using the dump
5. MySQL 4.1: fix the privilege tables etc.
6. MySQL 4.1: create <comparison_table_2> and fill it with data(same routine as 1.)
7. Compare the tables in the style of
    WHERE <comparison_table_1>.<float_column1>
            = <comparison_table_2>.<float_column2>

I am not going to analyze this or write a short replay script, as long as it is not 
really important, because the analysis is very time consuming. So please inform
me if it is needed.

This is not a bug. Direct double=double comparison isn't expected to work anyway.

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:

Updated 5.0 Upgrading From 4.1 section to warn users about this possibility per Sergei's suggestion.