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

issue with window functions and hugeint type coercion #6738

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

issue with window functions and hugeint type coercion #6738

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

Comments

@monetdb-team
Copy link

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

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:

foo s1 s2 s3
7 10 10 10
3 10 0 10
           ^
           |

note 0 here---+

Expected Results:

foo s1 s2 s3
7 10 10 10
3 10 10 10

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:

foo cumsum1 cumsum2
1 1 1
2 3 0
3 6 3
4 10 0
5 15 6
6 21 0

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.

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