[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: |
|
||||||||||||
| Description |
|
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):
The result changed after this commit in 10.1:
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. 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
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
And the second division produces 1.12044817927171 Before commit 62d73df6b270cc94ba577e96d3bf325170f306fe intermediate result did not respect div_precision_increment, this was a bug. For example, in
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. |