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