Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.1.36, 10.3.9, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
-
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) |