0

I have a question about a query that I'm running on a MySQL Server (v5.5.50-0+deb8u1).

SELECT 12 - (SELECT qty FROM Table WHERE id = 5213) AS Amount

so Amount value is 12 - 8,5500000000000007 = 3.4499999999999993

But if I run the query:

 SELECT qty FROM Table WHERE id = 5213

it returns 8.55 that is the correct number written in the record, so I was expecting that the first querty returned 3.45.

The "qty" column in the table "Table" is a DOUBLE.

How is it possibile? How can I get the right answer from the query? thanks in advance

Roberto Milani
  • 760
  • 4
  • 20
  • 40

2 Answers2

2

Well that's just the way floating numbers are.

Floating-point numbers sometimes cause confusion because they are approximate and not stored as exact values. A floating-point value as written in an SQL statement may not be the same as the value represented internally.

This statement holds true for many programming languages as well. Some numbers don't even have an exact representation. Here's something from the python manual

The problem is easier to understand at first in base 10. Consider the fraction 1/3. You can approximate that as a base 10 fraction:

0.3 or, better,

0.33 or, better,

0.333 and so on. No matter how many digits you’re willing to write down, the result will never be exactly 1/3, but will be an increasingly better approximation of 1/3.

In the same way, no matter how many base 2 digits you’re willing to use, the decimal value 0.1 cannot be represented exactly as a base 2 fraction. In base 2, 1/10 is the infinitely repeating fraction

So in short generally doing is float1 = float2 type of comparison is a bad idea but everyone keeps forgetting it.

e4c5
  • 52,766
  • 11
  • 101
  • 134
1

You can define 'qty' column as decimal(10,2)

Ismail Raju
  • 154
  • 2
  • 8