Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17256

Decimal field multiplication bug

    XMLWordPrintable

    Details

      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)
      

        Attachments

          Activity

            People

            Assignee:
            holyfoot Alexey Botchkov
            Reporter:
            tdangler Tom Dangler
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: