Bug #34139 Extreme double precision values change since 5.1
Submitted: 29 Jan 2008 16:51 Modified: 23 Dec 2009 16:32
Reporter: Peter Gulutzan Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:6.0.5-alpha-debug OS:Linux (SUSE 10 / 64-bit)
Assigned to: Alexey Kopytov CPU Architecture:Any
Tags: regression
Triage: Triaged: D4 (Minor) / R3 (Medium) / E3 (Medium)

[29 Jan 2008 16:51] Peter Gulutzan
Description:
In MySQL 5.1, (1E308) * 10 = INF.
In MySQL 6.0, (1E308) * 10 = 0.

In MySQL 5.1, (1E-308/2) / 999999999999999 = 4.9406564584125e-324.
In MySQL 6.0, (1E-308/2) / 999999999999999 = 5e-324.

I am aware that displayed value is not stored value.
I am aware that the original is not better than the new.
But it is different.
Maybe it will affect an existing index.

How to repeat:
For the first case:
CREATE TABLE tdouble (s1 DOUBLE PRECISION);
INSERT INTO tdouble VALUES (1E308);
SELECT s1 * 10 FROM tdouble;
Do it in both 5.1 and 6.0 with mysql client.
Look at results on the screen.

For the second case:
CREATE TABLE tdouble (s1 DOUBLE PRECISION);
INSERT INTO tdouble VALUES (1E-308/2);
SELECT s1 / 999999999999999  FROM tdouble;
Do it in both 5.1 and 6.0 with mysql client.
Look at results on the screen.
[29 Jan 2008 17:01] Miguel Solorzano
Thank you for the bug report. Verified as described.
[22 Dec 2009 14:57] Philip Stoev
Alexey,

From what I can see , the first example provided by PeterG is indeed a borderline issue, 10^308.

The second example that he gives is indeed a different precision, in this case, I think the precision increased, since the 6.0 value is what is being returned by the Windows Calc as well.

So the only open item as far as I am concerned are older indexes being run in a new MySQL or vice-versa. Should we flag all tables having Float as requiring an upgrade before they can be used again?
[23 Dec 2009 16:32] Alexey Kopytov
Philip,

The first example is actually a numeric overflow. 6.0 returns 0, because we don't support infinite values. If we did, then we had to also support [+-]INF literals in the parser, otherwise mysqldump might create dumps that could not be imported back, for example.

The second example is not actually about precision. 4.9406564584125e-324 and 5e-324 correspond to the same binary number. It's easy to check, for example "select 5e-324 =  4.9406564584125e-324;" returns "1" on both 5.1 and 6.0.

Now the 6.0 representation of this binary number is better, since it is the shortest string whereas 5.1 prints many digits carrying no value (i.e. garbage).

Regarding the question about indexes, I don't see a scenario where changing string representation of FLOAT/DOUBLE values could affect indexes. The server code uses binary numbers for all index operations.

Going back to the first example in the back report, numeric overflows will result in "numeric value out of range" error when the patch for bug #8433 is pushed.

To sum up, I agree with the bug report that the server behavior in 5.1 and 6.0 is different for the mentioned examples. On the other hand, I don't think we should do anything about it. So I'm closing this bug as "Won't fix".