[MDEV-23582] Unexpected result upon division of decimal values (comparing to other implementations) Created: 2020-08-25  Updated: 2020-09-01  Resolved: 2020-08-26

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s: N/A

Type: Bug Priority: Blocker
Reporter: Elena Stepanova Assignee: Varun Gupta (Inactive)
Resolution: Not a Bug Votes: 0
Labels: regression

Issue Links:
Blocks
Relates
relates to MDEV-19232 Floating point precision / value comp... Closed

 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.



 Comments   
Comment by Alexander Barkov [ 2020-08-26 ]

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.

Comment by Sergei Golubchik [ 2020-08-26 ]

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.

Generated at Thu Feb 08 09:23:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.