Bug 6774 - PROD aggregation gives wrong result
Summary: PROD aggregation gives wrong result
Status: RESOLVED NEXTRELEASE
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: 11.33.11 (Apr2019-SP1)
Hardware: x86_64 (amd64/em64t) Linux
: Normal normal
Assignee: SQL devs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2019-10-11 13:00 CEST by Roberto Cornacchia
Modified: 2019-10-15 11:43 CEST (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Roberto Cornacchia 2019-10-11 13:00:04 CEST
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 1 Roberto Cornacchia 2019-10-11 13:04:19 CEST
Also, replacing prod with sum works as expected (gives 0.7)
Comment 2 Pedro Ferreira 2019-10-11 13:16:48 CEST
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 3 MonetDB Mercurial Repository cwiconfidential 2019-10-11 13:57:10 CEST
Changeset f93d5290abe4, made by Pedro Ferreira <pedro.ferreira@monetdbsolutions.com> in the MonetDB repo, refers to this bug.

For complete details, see https://dev.monetdb.org/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 4 Roberto Cornacchia 2019-10-15 11:43:14 CEST
The fix works well on new databases, but databases created before the fix still give the wrong result.

How can I fix them?