You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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;
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
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:
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?
The text was updated successfully, but these errors were encountered: