[MDEV-17256] Decimal field multiplication bug Created: 2018-09-20  Updated: 2018-10-31  Resolved: 2018-10-31

Status: Closed
Project: MariaDB Server
Component/s: Data types, Server
Affects Version/s: 5.5, 10.0, 10.1, 10.1.36, 10.3.9, 10.2, 10.3
Fix Version/s: 10.3.11, 10.1.37, 10.2.19, 5.5.63, 10.0.38

Type: Bug Priority: Critical
Reporter: Tom Dangler Assignee: Alexey Botchkov
Resolution: Fixed Votes: 0
Labels: upstream-fixed
Environment:

Linux mariadb-10-3-devel 3.16.0-4-amd64 #1 SMP Debian 3.16.43-2+deb8u5 (2017-09-19) x86_64 GNU/Linux


 Description   

I'm having a problem when multiplying decimal fields together. I have included the table schema and data below. When I multiply all of these fields together in 1 order, I get a different result than when I multiply them in a different order. I would think that multiplying them in any order would produce the same result, but it clearly does not. Can anyone shed some light on this? Behavior exists in MariaDB 10.1.36 and MariaDB 10.3.9 ( I have not tested MariaDB 10.2). I tested this in MySQL 5.7, and both queries in 5.7 produce the same (correct) result.

*UPDATE - I think this might be related to MySQL Bug #45860. I tested and confirmed that the bug existed in 5.1, but not 5.5, 5.6, or 5.7.*

CREATE TABLE `test` (
 
`FLD1` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD2` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD3` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD4` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD5` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD6` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD7` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD8` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD9` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD10` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD11` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD12` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD13` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD14` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD15` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD16` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD17` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD18` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD19` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD20` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD21` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD22` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD23` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8


MariaDB [market_basket]> select * from test\G
 
*************************** 1. row ***************************
 
FLD1: 001.0760
 
FLD2: 000.9500
 
FLD3: 001.0000
 
FLD4: 001.0000
 
FLD5: 001.0000
 
FLD6: 001.0000
 
FLD7: 001.0000
 
FLD8: 001.0000
 
FLD9: 001.0000
 
FLD10: 001.0000
 
FLD11: 001.0000
 
FLD12: 000.5949
 
FLD13: 001.0194
 
FLD14: 001.0000
 
FLD15: 001.0000
 
FLD16: 001.0000
 
FLD17: 001.0000
 
FLD18: 001.0000
 
FLD19: 001.0000
 
FLD20: 000.9220
 
FLD21: 001.1890
 
FLD22: 001.2130
 
FLD23: 327.2690
 
1 row in set (0.000 sec)


select FLD1*FLD2*FLD3*FLD4*FLD5*FLD6*FLD7*FLD8*FLD9*FLD10*FLD11*FLD12*FLD13*FLD14*FLD15*FLD16*FLD17*FLD18*FLD19*FLD20*FLD21*FLD22*FLD23 as calc1 from test;
 
+--------------------------------------------+
 
| calc1 |
 
+--------------------------------------------+
 
| 226.89298366395334762168800000000000000000 |
 
+--------------------------------------------+


select FLD23*FLD2*FLD1*FLD4*FLD5*FLD11*FLD12*FLD13*FLD3*FLD15*FLD16*FLD17*FLD18*FLD19*FLD20*FLD21*FLD22*FLD14*FLD6*FLD7*FLD8*FLD9*FLD10 as calc2 from test;
 
+--------------------------------------------+
 
| calc2 |
 
+--------------------------------------------+
 
| 269.77575757644053032218703200000000000000 |
 
+--------------------------------------------+
 
1 row in set (0.001 sec)



 Comments   
Comment by Elena Stepanova [ 2018-09-21 ]

Thanks for the report. Reproducible as described.

CREATE TABLE `test` (
 
`FLD1` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD2` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD3` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD4` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD5` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD6` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD7` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD8` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD9` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD10` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD11` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD12` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD13` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD14` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD15` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD16` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD17` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD18` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD19` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD20` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD21` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD22` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
 
`FLD23` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000
 
);
 
 
 
INSERT INTO `test` VALUES (001.0760,000.9500,001.0000,001.0000,001.0000,001.0000,001.0000,001.0000,001.0000,001.0000,001.0000,000.5949,001.0194,001.0000,001.0000,001.0000,001.0000,001.0000,001.0000,000.9220,001.1890,001.2130,327.2690);
 
 
 
select FLD1*FLD2*FLD3*FLD4*FLD5*FLD6*FLD7*FLD8*FLD9*FLD10*FLD11*FLD12*FLD13*FLD14*FLD15*FLD16*FLD17*FLD18*FLD19*FLD20*FLD21*FLD22*FLD23 as calc1 from test;
 
select FLD23*FLD2*FLD1*FLD4*FLD5*FLD11*FLD12*FLD13*FLD3*FLD15*FLD16*FLD17*FLD18*FLD19*FLD20*FLD21*FLD22*FLD14*FLD6*FLD7*FLD8*FLD9*FLD10 as calc2 from test;
 
 
 
DROP TABLE test;


select FLD1*FLD2*FLD3*FLD4*FLD5*FLD6*FLD7*FLD8*FLD9*FLD10*FLD11*FLD12*FLD13*FLD14*FLD15*FLD16*FLD17*FLD18*FLD19*FLD20*FLD21*FLD22*FLD23 as calc1 from test;
 
calc1
 
226.89298366395334762168800000000000000000
 
select FLD23*FLD2*FLD1*FLD4*FLD5*FLD11*FLD12*FLD13*FLD3*FLD15*FLD16*FLD17*FLD18*FLD19*FLD20*FLD21*FLD22*FLD14*FLD6*FLD7*FLD8*FLD9*FLD10 as calc2 from test;
 
calc2
 
269.77575757644053032218703200000000000000

Comment by Alexander Barkov [ 2018-09-21 ]

This query also produces a wrong result

SELECT
 
  FLD1*FLD2*FLD3*FLD4*FLD5*FLD6*FLD7*FLD8*FLD9*FLD10*FLD11*FLD12*FLD13*FLD14*FLD15*FLD16*FLD17*FLD18*FLD19*FLD20 AS calc1,
 
  FLD1*FLD2*FLD3*FLD4*FLD5*FLD6*FLD7*FLD8*FLD9*FLD10*FLD11*FLD12*FLD13*FLD14*FLD15*FLD16*FLD17*FLD18*FLD19*FLD20*FLD21 AS calc2
 
FROM t1;


+------------------------------------------+------------------------------------------+
 
| calc1                                    | calc2                                    |
 
+------------------------------------------+------------------------------------------+
 
| 0.57155153551250400000000000000000000000 | 0.57155153551250400000000000000000000000 |
 
+------------------------------------------+------------------------------------------+

Notice, calc1 and calc2 erroneously return the same value. The multiplication to F21 did not happen for some reason in calc2.

Comment by Alexander Barkov [ 2018-09-21 ]

So does this:

SELECT
 
  FLD5*FLD6*FLD7*FLD8*FLD9*FLD10*FLD11*FLD12*FLD13*FLD14*FLD15*FLD16*FLD17*FLD18*FLD19*FLD20 AS calc1,
 
  FLD5*FLD6*FLD7*FLD8*FLD9*FLD10*FLD11*FLD12*FLD13*FLD14*FLD15*FLD16*FLD17*FLD18*FLD19*FLD20*FLD21 AS calc2
 
FROM t1;


+----------------------------------+----------------------------------+
 
| calc1                            | calc2                            |
 
+----------------------------------+----------------------------------+
 
| 0.559138657320000000000000000000 | 0.559138657320000000000000000000 |
 
+----------------------------------+----------------------------------+

Comment by Alexander Barkov [ 2018-09-21 ]

The problem happens inside decimal_mul().

FIX_INTG_FRAC_ERROR() returns error=1 at some point.
holyfoot can you take over this?

Thanks.

Comment by Tom Dangler [ 2018-10-03 ]

Do we have an ETA on a fix for this issue? I have a project on hold pending the resolution of this.

Comment by Sergei Golubchik [ 2018-10-16 ]

Yes, the plan is to have it fixed in 10.1.37.
But it's a plan, not a promise, not everything goes according to the plan...

Comment by Tom Dangler [ 2018-10-16 ]

Ok - thank you for the update.

Comment by Alexey Botchkov [ 2018-10-23 ]

http://lists.askmonty.org/pipermail/commits/2018-October/013037.html

Generated at Thu Feb 08 08:35:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.