4

I tested with SELECT 0.1 + 0.2;, queried with MySQL (MariaDB), and it returned the right answer

MariaDB [(none)]> SELECT 0.1 + 0.2;
+-----------+
| 0.1 + 0.2 |
+-----------+
|       0.3 |
+-----------+
1 row in set (0.000 sec)

Floating point calculation is inaccurate in most programming languages because of IEEE 754 as explained here.

How MySQL does the floating point calculation that makes it return the right answer?

Philip Choi
  • 7
  • 1
DarkSuniuM
  • 2,523
  • 2
  • 26
  • 43
  • *In most programming languages it calculates it kinda wrong*: can you provide an actual example of a software that is not able to compute `0.1 + 0.2` accurately? – GMB Mar 23 '19 at 00:42
  • @GMB in Python it returns `0.30000000000000004`, also there are a lot of examples of other languages here => http://0.30000000000000004.com – DarkSuniuM Mar 23 '19 at 00:43

2 Answers2

5

I know SQL 92 is old standard but iám pretty sure this is not changed in the newer SQL standard versions.

SQL 92 defines

73)Subclause 6.12, "<numeric value expression>": When the data type of both operands of the addition. subtraction, multiplication, or division operator is exact numeric, the precision of the result is implementation-defined."*

75)Subclause 6.12, "<numeric value expression>": When the data type of either operand of an arithmetic operator is approximate numeric, the precision of the result is implementation-defined."*

The question is: 0.1 and 0.2 in the query SELECT 0.1 + 0.2 a approximate or is it exact?
The answer is: you don't know also the database can't know.
So the database will run the implemention defined for MySQL and MariaDB engines this seams to be handled as DECIMAL(1,1) datatypes

Why does Nick's answer return the correct values or expected ones with a table definition

SQL 92 also defines

Implicit type conversion can occur in expressions, fetch opera-
tions, single row select operations, inserts, deletes, and updates.
Explicit type conversions can be specified by the use of the CAST
operator.

Which Nick has done by defining the datatype in the table.

Edited this answer because i found something in the MySQL's manual today.

The query

SELECT (0.1 + 0.2) = 0.3

Results into 1 in MySQL which means MySQL uses exact numeric calculation and uses Precision Math where possible. So the MySQL does know that 0.1, 0.2 and 0.3 are exact datatypes here and needs to calculate exact, like i was expecting before this edit.

Meaning the query

SELECT (0.1 + 0.2) = 0.3

will run under the hood more or less like 

SELECT CAST((0.1 + 0.2) AS DECIMAL(1, 1)) = CAST((0.3) AS DECIMAL(1, 1));
Raymond Nijland
  • 11,488
  • 2
  • 22
  • 34
4

That is just MySQL being intelligent in data type/formatting choice because you haven't specified that those values are floating point. Try this:

create table test (f float);
insert into test values (0.1), (0.2);
select sum(f) from test

Output:

sum(f)
0.30000000447034836

Demo on dbfiddle

If you use double precision you get the classic 0.30000000000000004 result. Demo on dbfiddle

Nick
  • 138,499
  • 22
  • 57
  • 95
  • Seems like it's right, Can u explain how it's being intelligent? that's the important part for me, Thanks – DarkSuniuM Mar 23 '19 at 00:47
  • 4
    @DarkSuniuM unfortunately I'm not that familiar with the internals of MySQL. I would suspect that it is just choosing to use `DECIMAL` for accuracy. – Nick Mar 23 '19 at 00:48
  • *"I'm not that familiar with the internals of MySQL. I would suspect that it is just choosing to use DECIMAL for accuracy. "* indeed @Nick it's been confirmed (just found it in the manual) MySQL uses exact numeric calculation where [possible](https://dev.mysql.com/doc/refman/8.0/en/precision-math-examples.html) – Raymond Nijland Mar 25 '19 at 00:53
  • 1
    @RaymondNijland thanks for the update. It is nice to have it confirmed - I guess I should have hunted a bit harder. If I could upvote your answer again I would! :-) – Nick Mar 25 '19 at 04:18