Bug #4457 Different results in SQL-Statements for the same record
Submitted: 8 Jul 2004 9:29 Modified: 30 Aug 2004 14:24
Reporter: Peter Gsaxner Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.17-nt-log OS:Microsoft Windows (Windows 2003)
Assigned to: Ingo Strüwing CPU Architecture:Any

[8 Jul 2004 9:29] Peter Gsaxner
Description:
Using the following table  (MyISAM) "pressure":
Col. seq - bigint(15) Primary Key
Col. value  - float

The query
SELECT  max( `pressure`.`value`) FROM  `pressure` 
     returns 40825084 

The query 
Select * from pressure Where seq = 2088624630 (this is the record having the highest value) returns 4.08251e+007 (so the result seems to be rounded).

Looking at the table with mySQL-Controlcenter, sorted by "value", also 
4.08251e+007 is displayed. The user gets two different results depending on the query executed. Which result is correct ?

How to repeat:
Trying the same with a test-table and adding some test-values manualy.
[11 Aug 2004 20:08] Ingo Strüwing
Reproduced with 4.0.21 on Linux.
[12 Aug 2004 13:29] Ingo Strüwing
I'm not sure if this can be seen as a serious bug.
From the manual:
"The FLOAT type is used to represent approximate numeric data types."
This means IMO that no exact values can be expected from such column.
And even tests for equality of two such values should not be expected as accurate.
The problem hit by this bug report is that the output formatting of
"select max(column) ..." is different from the formatting for "select column ...".
I doubt that this should be expected at all.
Functions like max() take an expression as argument, which can combine values of
different data types. It would be difficult to tell which output formatting to use.
If one specifies FLOAT with display width and fraction (e.g. FLOAT(8,3)), this is
obeyed even through a max() function. Then both values display the same.
An alternative for the high values at hand would be to use DOUBLE instead of FLOAT.
This extends the default display width so that the mentioned values could be displayed
without rounding.
As a convenience to the user we could try to select the same default display width for
a function as for its input column, if there is only one. But I don't think that this is
a serious bug to fix, if a bug at all.
[14 Aug 2004 23:31] Sergei Golubchik
though it may be not a bug, the behaviour is not nice, I agree.
But it most probably will go away when we'll have our own printf (in todo).