Date: 2019-07-25 16:24:42 +0200
From: Dragoslav Mitrinovic <>
To: SQL devs <>
Version: 11.33.3 (Apr2019)
CC: @PedroTadim

Last updated: 2019-09-02 16:05:25 +0200

Comment 27170

Date: 2019-07-25 16:24:42 +0200
From: Dragoslav Mitrinovic <>

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

Unexpected results are obtained with expressions involving window functions such as SUM( some_int_expr ) OVER (...).

It appears that the issue is that window function returns HUGEINT, and that automatic type coercion does not work correctly.

Reproducible: Always

Steps to Reproduce:

Run the following query:

select foo,
SUM(foo) over () as s1,
1.0 * SUM(foo) over () as s2,
1.0 * CAST(SUM(foo) over () as FLOAT) as s3
from (select 7 as foo union all select 3 as foo) as t

Actual Results:

           ^
           |

note 0 here---+

Expected Results:

Here's a variation on the same theme. Running:

select foo,
SUM(foo) over () / 2
from (select 7 as foo union all select 3 as foo) as t

Results in error message:
"[22000] type combination (div(hge,bte)->lng) not supported."

This error message is what makes me think that SUM() OVER () results in HUGEINT.

Here's the mserver5 info:

$ mserver5 --version
MonetDB 5 server 11.33.3 (Apr2019) (64-bit, 128-bit integers)
Copyright (c) 1993 - July 2008 CWI
Copyright (c) August 2008 - 2019 MonetDB B.V., all rights reserved
Visit https://www.monetdb.org/ for further information
Found 31.4GiB available memory, 8 available cpu cores
Libraries:
libpcre: 8.21 2011-12-12 (compiled with 7.8 2008-09-05)
openssl: OpenSSL 1.0.1e-fips 11 Feb 2013
libxml2: 2.7.6
Compiled by: mockbuild@dev.monetdb.org (x86_64-redhat-linux-gnu)
Compilation: gcc -std=gnu99 -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -Wno-format-truncation
Linking : /usr/bin/ld -m elf_x86_64 -Wl,-Bsymbolic-functions

Comment 27171

Date: 2019-07-25 16:53:41 +0200
From: Dragoslav Mitrinovic <>

Here's another example:

select foo,
SUM(foo) OVER (ORDER BY foo) as cumsum1,
1.0 * SUM(foo) OVER (ORDER BY foo) as cumsum2
from (values (1),(2),(3),(4),(5),(6)) as t(foo)

Returns:

Comment 27172

Date: 2019-07-25 17:18:46 +0200
From: MonetDB Mercurial Repository <>

Changeset b80df006cee5 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

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

Changeset description:

Test for bug #6738.

Comment 27173

Date: 2019-07-25 17:19:56 +0200
From: @sjoerdmullender

This bug seems to be already fixed in the current Apr2019 branch and so will be fixed in the next release.

Comment 27174

Date: 2019-07-25 17:39:06 +0200
From: @PedroTadim

The query:

select foo,
SUM(foo) OVER (ORDER BY foo) as cumsum1,
1.0 * SUM(foo) OVER (ORDER BY foo) as cumsum2
from (values (1),(2),(3),(4),(5),(6)) as t(foo)

Doesn't work on default.

Comment 27175

Date: 2019-07-25 18:15:00 +0200
From: @sjoerdmullender

(In reply to Pedro Ferreira from comment 4)

The query:

select foo,
SUM(foo) OVER (ORDER BY foo) as cumsum1,
1.0 * SUM(foo) OVER (ORDER BY foo) as cumsum2
from (values (1),(2),(3),(4),(5),(6)) as t(foo)

Doesn't work on default.

Then we need to fix that before the default branch gets released, but that doesn't affect this bug report.
We'll see that from the test that will fail on the default branch after we've merged.

Comment 27189

Date: 2019-07-29 09:33:47 +0200
From: @PedroTadim

(In reply to Sjoerd Mullender from comment 5)

(In reply to Pedro Ferreira from comment 4)

The query:

select foo,
SUM(foo) OVER (ORDER BY foo) as cumsum1,
1.0 * SUM(foo) OVER (ORDER BY foo) as cumsum2
from (values (1),(2),(3),(4),(5),(6)) as t(foo)

Doesn't work on default.

Then we need to fix that before the default branch gets released, but that
doesn't affect this bug report.
We'll see that from the test that will fail on the default branch after
we've merged.

This is now fixed on default branch.