0

I was experimenting with mysql, and made a query to compare two different fields using the GRATEST() function.

My query looks like this:

SELECT
  id,
  float1,
  float2,
  GREATEST(
    IFNULL(float1, 0),
    IFNULL(float2, 0)
  ) AS gtst
FROM `test`

Float1 and 2 are UNSIGNED FLOAT, with NULL as default value. Server version: 5.1.73-1-log, client: 5.0.8-dev, PHP extension: mysqli.

Running the above command gives me strange values like this:

 | id  | float1 | float2 | gtst             |
 |-----|--------|--------|------------------|
 | 872 | 348.5  | 348.58 | 348.579986572266 |

I know that MySQL handles floating point values strangely, as it is described in this article, but it's unclear to me where are these extra digits coming from?

A single comparison shouldn't alter the supplied values, right? There's no mathematical equations here where rounding errors could come into play, so what could be wrong?

Thanks in advance!

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
Gergely Lukacsy
  • 2,881
  • 2
  • 23
  • 28
  • 1
    Well that's float for you. Try giving it a precision of, better idea, don't use float where what you really want is decimal! That said, there's nothing inaccurate about this result! – Strawberry Mar 03 '15 at 08:51
  • 1
    Looks like MySQL cast your `FLOAT` to `DOUBLE`, and this produce some garbage at the end, due to different precisions of this types. – user4003407 Mar 07 '15 at 16:21

1 Answers1

0

Its seems like you have declared float1 and float2 as float datatype, which usually takes 12 fractional points like 10.123456789101 make use of

Round(10.123456789101,2);

to display 2 extra digits as, 10.12

Karthikeyan
  • 381
  • 8
  • 19