Date: 2019-10-24 12:28:31 +0200
From: Manuel <>
To: SQL devs <>
Version: 11.33.11 (Apr2019-SP1)
CC: @PedroTadim

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

Comment 27381

Date: 2019-10-24 12:28:31 +0200
From: Manuel <>

User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36
Build Identifier:

I have attach the SQL to create the table referenced in the queries.

The query

select , rank() over (partition by "c2_h_field_2" order by "count_1" desc) as "RANK"
from
(
select
count() as "count_1",
((floor(((floor(extract(month from "Field 2")) - 1) / 6.0)) * 6.0) + 1) as "c2_h_field_2",
"Ticker"
from
"BondPricesWithNulls"
where
"Ticker" is not null
group by "c2_h_field_2", "Ticker"
) as "t0"
where "c2_h_field_2" is null order by "count_1" desc

returns

count_1 c2_h_field_2 Ticker RANK

8 HBOS 1
5 EIB 1
5 KFW 1
5 RBS 1
4 HSBC 1
3 PRUFIN 1
...
...
...

The value yield by rank is wrong, and in this particular query it should reflect the value of "count".

By replacing the expression used in the "group by" clause with a field:

select , rank() over (partition by "c2_h_field_2" order by "count_1" desc) as "RANK"
from
(
select
count() as "count_1",
--((floor(((floor(extract(month from "Field 2")) - 1) / 6.0)) * 6.0) + 1) as "c2_h_field_2",
"Field 2" as "c2_h_field_2",
"Ticker"
from
"BondPricesWithNulls"
where
"Ticker" is not null
group by "c2_h_field_2", "Ticker"
) as "t0"
where "c2_h_field_2" is null order by "count_1" desc

I get

count_1 c2_h_field_2 Ticker RANK

8 HBOS 1
5 EIB 2
5 KFW 2
5 RBS 2
4 HSBC 5
3 PRUFIN 6
...
...
...

which is what I expected (and what the behaviour was in older versions of monetdb: last tested on 11.29.7).

Reproducible: Always

Steps to Reproduce:

1. Import table attached
2. Run queries in the description

Actual Results:

count_1 c2_h_field_2 Ticker RANK

8 HBOS 1
5 EIB 1
5 KFW 1
5 RBS 1
4 HSBC 1
3 PRUFIN 1
...

Expected Results:

count_1 c2_h_field_2 Ticker RANK

8 HBOS 1
5 EIB 2
5 KFW 2
5 RBS 2
4 HSBC 5
3 PRUFIN 6
....

Comment 27382

Date: 2019-10-24 12:29:06 +0200
From: Manuel <>

Created attachment 637
Sql to create tables used in the queries.

Attached file: bpwn.sql (application/octet-stream, 434082 bytes)
Description: Sql to create tables used in the queries.

Comment 27383

Date: 2019-10-24 18:14:50 +0200
From: MonetDB Mercurial Repository <>

Changeset 982ddeda4aa1 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=982ddeda4aa1

Changeset description:

Test and fixes for bug #6780. We use NaN for null floating-point values, so be carefull with floating-point comparisons

Comment 27410

Date: 2019-11-14 13:58:31 +0100
From: MonetDB Mercurial Repository <>

Changeset 2ad260d4df96 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=2ad260d4df96

Changeset description:

Use csv import at bug #6780 test