Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Not a Bug
-
10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
Description
SELECT (0.996875 / 2791137695) / 0.000000000357; |
|
10.5 6708e67acc |
MariaDB [test]> SELECT (0.996875 / 2791137695) / 0.000000000357; |
+------------------------------------------+ |
| (0.996875 / 2791137695) / 0.000000000357 |
|
+------------------------------------------+ |
| 1.12044817927171 |
|
+------------------------------------------+ |
1 row in set (0.001 sec) |
The same result is returned on all MariaDB 10.1+.
MariaDB prior to 10.1.46, MySQL 5.6, 8.0, PostgreSQL 9.6, Oracle 11g R2, MS SQL Server 2017 and the calculator in my tablet return this (within ~6-8 decimal digit precision):
postgres=# SELECT (0.996875 / 2791137695) / 0.000000000357; |
?column? |
--------------------------------
|
1.0004404124457612918767507003
|
(1 row)
|
The result changed after this commit in 10.1:
commit 62d73df6b270cc94ba577e96d3bf325170f306fe
|
Author: Varun Gupta <varun.gupta@mariadb.com>
|
Date: Wed Jul 22 14:44:25 2020 +0530
|
|
|
MDEV-19232: Floating point precision / value comparison problem
|
I cannot figure out whether the effect was expected, but given that all other implementations agree on a different outcome, chances are it's a failure.
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
This is not a bug. div_precision_increment specifies what the precision of the decimal division should be. In your case
0.996875 / 2791137695 = .000000000357157227243136781182699766447745961167996...
|
A dividend precision is 6, div_precision_increment is 4 by default, the precision of the result should be 10. Truncated to that precision the result becomes
MariaDB [test]> select 0.996875 / 2791137695;
|
+-----------------------+
|
| 0.996875 / 2791137695 |
|
+-----------------------+
|
| 0.0000000004 |
|
+-----------------------+
|
1 row in set (0.00 sec)
|
And the second division produces 1.12044817927171
You can avoid this precision loss, with div_precision_increment=9 the result will be 1.000439775910364145658263. But no matter what the div_precision_increment is, one can always pick two numbers which are close enough to thwart any given precision increment value.
Before commit 62d73df6b270cc94ba577e96d3bf325170f306fe intermediate result did not respect div_precision_increment, this was a bug. For example, in
SELECT 0.996875 / 2791137695 / 0.000000000357; |
|
|
CREATE TEMPORARY TABLE t1 AS SELECT 0.996875 / 2791137695 as a; |
SELECT a / 0.000000000357 from t1; |
the first and last queries returned different results. That is the expression result could change if you split the expression and evaluate it in two steps. And because depending on the execution plan optimizer might decide to do just that, results were essentially unpredictable.
Looks like a bug.
It seems we can round values only on the high level Item_func_div's.
We cannot do it in intermediate ones.
Another option is to apply this inside the subquery cache, without any rounding in Item_func_div itself.
I think the latter is safer.