Math question on decimal fields
Posted by:
Tom Dangler
Date: September 20, 2018 09:23AM
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?
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)