PROD aggregation gives wrong result

[monetdb-team]

Date: 2019-10-11 13:00:04 +0200
From: @swingbit
To: SQL devs <>
Version: 11.33.11 (Apr2019-SP1)
CC: @PedroTadim

Last updated: 2019-11-28 10:00:05 +0100

Comment 27356

Date: 2019-10-11 13:00:04 +0200
From: @swingbit

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.90 Safari/537.36
Build Identifier:

START TRANSACTION;

CREATE TABLE "t1" (
"a1" INTEGER,
"prob" DECIMAL(7,4)
);
INSERT INTO t1 VALUES (5, 0.2000);

CREATE TABLE "t2" (
"a1" INTEGER,
"prob" DECIMAL(7,4)
);
INSERT INTO t2 VALUES (5, 0.5000);

CREATE TABLE "t3" (
"a1" INTEGER,
"prob" TINYINT
);
INSERT INTO t3 VALUES (5, 1);

SELECT tmp.a1, prod(tmp.prob) AS prob FROM (SELECT a1, prob FROM (SELECT t1.a1 AS a1, t3.a1 AS a2, t1.prob * t3.prob AS prob FROM t1,t3 WHERE t1.a1 = t3.a1) AS t__x30 UNION ALL SELECT a1, prob FROM (SELECT t2.a1 AS a1, t3.a1 AS a2, t2.prob * t3.prob AS prob FROM t2,t3 WHERE t2.a1 = t3.a1) AS t__x32) as tmp GROUP BY tmp.a1;

+------+------------------------------------------+
| a1 | prob |
+======+==========================================+
| 5 | 1000.0000 |
+------+------------------------------------------+
1 tuple

The result should be (5, 0.1).
Replacing TINYINT with DOUBLE in the CREATE statement of t3 gives the correct result.

Reproducible: Always

Comment 27357

Date: 2019-10-11 13:04:19 +0200
From: @swingbit

Also, replacing prod with sum works as expected (gives 0.7)

Comment 27358

Date: 2019-10-11 13:16:48 +0200
From: @PedroTadim

Simplifying to this query:

select prod(col1) from (values(0.2), (0.5)) as t1(col1);

Should output 0.1, but it outputs 1.0

Comment 27359

Date: 2019-10-11 13:57:10 +0200
From: MonetDB Mercurial Repository <>

Changeset f93d5290abe4 made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=f93d5290abe4

Changeset description:

Added test and fix for bug #6774. Disable prod aggregate on decimals, because applying successive multiplications implies changing scales. Instead convert them to floating points

Comment 27363

Date: 2019-10-15 11:43:14 +0200
From: @swingbit

The fix works well on new databases, but databases created before the fix still give the wrong result.

How can I fix them?