Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

AVG changes scale of its results #6783

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

AVG changes scale of its results #6783

monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2019-11-05 17:25:12 +0100
From: @yzchang
To: SQL devs <>
Version: 11.33.11 (Apr2019-SP1)
CC: @PedroTadim

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

Comment 27393

Date: 2019-11-05 17:25:12 +0100
From: @yzchang

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/13.0.1 Safari/605.1.15
Build Identifier:

With the following queries, the first SELECT (i.e. without AVG) returns 175.000, while the second SELECT (i.e. with AVG) returns 175000. AVG has silently changed the scale of its return value here. It should return 175 (i.e. 1.75e2), not 175000 (i.e. 1.75e5).

sql>CREATE TABLE ts (t TIMESTAMP);
operation successful
sql>INSERT INTO ts values ('1970-01-01 00:02:55.000000');
1 affected row
sql>select t - SYS.STR_TO_TIMESTAMP('0', '%s') from ts;
+---------+
| L1 |
+=========+
| 175.000 |
+---------+
1 tuple
sql>select avg(t - SYS.STR_TO_TIMESTAMP('0', '%s')) from ts;
+--------------------------+
| L2 |
+==========================+
| 1.75e+05 |
+--------------------------+
1 tuple

This silent change of scale doesn't happen with all data types, e.g.:

sql>create table t (i decimal(8,3));
operation successful
sql>insert into t values (175);
1 affected row
sql>select * from t;
+------------+
| i |
+============+
| 175.000 |
+------------+
1 tuple
sql>select avg(i) from t;
+--------------------------+
| L3 |
+==========================+
| 175 |
+--------------------------+
1 tuple

Reproducible: Always

Comment 27394

Date: 2019-11-05 17:32:12 +0100
From: MonetDB Mercurial Repository <>

Changeset cbb14cfab751 made by Ying Zhang y.zhang@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Added test for Bug #6783

Comment 27395

Date: 2019-11-06 09:56:25 +0100
From: MonetDB Mercurial Repository <>

Changeset f445d2949471 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=f445d2949471

Changeset description:

Several changes:

-Removed 'avg' window function aggregate with sec_interval as input, derived from the respective aggregate being removed.
-Added SYS.STR_TO_TIMESTAMP('0', '%s') call back in bug #6783 test, because it's failing on Windows.
-Approved tests from recent changes.

Comment 27396

Date: 2019-11-06 15:30:37 +0100
From: MonetDB Mercurial Repository <>

Changeset fbdb41cd5249 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=fbdb41cd5249

Changeset description:

Fix for bug #6783 on Windows. Added missing '%s' case at Windows port of strptime function
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant