[MDEV-17256] Decimal field multiplication bug - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.1.36, 10.3.9, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
Fix Version/s: 10.3.11, 10.1.37, 10.2.19, 5.5.63, 10.0.38
Component/s: Data types, Server
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)

Attachments

Activity

Ascending order - Click to sort in descending order

Elena Stepanova added a comment - 2018-09-21 00:24

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

Elena Stepanova added a comment - 2018-09-21 00:24 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

Alexander Barkov added a comment - 2018-09-21 03:21 - edited

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.

Alexander Barkov added a comment - 2018-09-21 03:21 - edited 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.

Alexander Barkov added a comment - 2018-09-21 04:27

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 |

+----------------------------------+----------------------------------+

Alexander Barkov added a comment - 2018-09-21 04:27 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 | +----------------------------------+----------------------------------+

Alexander Barkov added a comment - 2018-09-21 04:30

The problem happens inside decimal_mul().

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

Thanks.

Alexander Barkov added a comment - 2018-09-21 04:30 The problem happens inside decimal_mul(). FIX_INTG_FRAC_ERROR() returns error=1 at some point. holyfoot can you take over this? Thanks.

Tom Dangler added a comment - 2018-10-03 14:34

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

Tom Dangler added a comment - 2018-10-03 14:34 Do we have an ETA on a fix for this issue? I have a project on hold pending the resolution of this.

Sergei Golubchik added a comment - 2018-10-16 10:19

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...

Sergei Golubchik added a comment - 2018-10-16 10:19 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...

Tom Dangler added a comment - 2018-10-16 14:07

Ok - thank you for the update.

Tom Dangler added a comment - 2018-10-16 14:07 Ok - thank you for the update.

Alexey Botchkov added a comment - 2018-10-23 22:06

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

Alexey Botchkov added a comment - 2018-10-23 22:06 http://lists.askmonty.org/pipermail/commits/2018-October/013037.html

People

Assignee:: Alexey Botchkov

Reporter:: Tom Dangler

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2018-09-20 16:13

Updated:: 2018-10-31 08:41

Resolved:: 2018-10-31 08:41

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server