[MDEV-6841] test decimal math Created: 2014-10-06  Updated: 2020-08-31

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: Sergei Golubchik Assignee: Elena Stepanova
Resolution: Unresolved Votes: 1
Labels: None

Issue Links:
Relates
relates to MDEV-6851 Decimal multiplication loses precisio... Open
relates to MDEV-6852 Sequence of decimal division and mult... Open
relates to MDEV-6853 Unexpected zero result on 1 % <very s... Open
relates to MDEV-6856 <negative value> MOD <fractional divi... Open

 Description   

Even after all these years there is a steady stream of bugs in decimal math.
We need a proactive approach instead, to try to find as many decimal bugs as
possible.

Suggestion: RQG testing of the expression

ABS(CAST(a + b AS DOUBLE)/(CAST(a AS DOUBLE) + CAST(b AS DOUBLE)) - 1) < 0.0001

where RQG randomly generates a and b, and the operation is one of +,
-, *, /, mod.

One should try also very small numbers (0.00...{~50 times}...01) as well as
large ones.

 Comments   
Comment by Elena Stepanova [ 2014-10-08 ]

This query works okay in 5.5.40 candidate, but wrong in MySQL 5.5 (apparently that's the same problem as the one serg solved in the post-merge fix). 

MySQL [test]> select 0.0000000001 % 1;
 
+------------------+
 
| 0.0000000001 % 1 |
 
+------------------+
 
|     0.1000000000 |
 
+------------------+
 
1 row in set (0.00 sec)

Comment by Sergei Golubchik [ 2020-08-26 ]

Correction

0.0001 error margin is rather arbitrary. It should depend on operands and on the operation itself. Here, revised queries:

ABS(CAST(a + b AS DOUBLE)/(CAST(a AS DOUBLE) + CAST(b AS DOUBLE)) - 1) < POW(0.1, GREATEST(LENGTH(REGEXP_SUBSTR(a, '(?<=[.]).*')), LENGTH(REGEXP_SUBSTR(b, '(?<=[.]).*'))))
 
ABS(CAST(a - b AS DOUBLE)/(CAST(a AS DOUBLE) - CAST(b AS DOUBLE)) - 1) < POW(0.1, GREATEST(LENGTH(REGEXP_SUBSTR(a, '(?<=[.]).*')), LENGTH(REGEXP_SUBSTR(b, '(?<=[.]).*'))))
 
ABS(CAST(a * b AS DOUBLE)/(CAST(a AS DOUBLE) * CAST(b AS DOUBLE)) - 1) < POW(0.1, LENGTH(REGEXP_SUBSTR(a, '(?<=[.]).*')) + LENGTH(REGEXP_SUBSTR(b, '(?<=[.]).*')))
 
ABS(CAST(a / b AS DOUBLE)/(CAST(a AS DOUBLE) / CAST(b AS DOUBLE)) - 1) < POW(0.1, LENGTH(REGEXP_SUBSTR(a, '(?<=[.]).*')) + @@div_precision_increment)

Generated at Thu Feb 08 07:14:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.