Date: 2020-02-20 16:25:46 +0100
From: @bernardom29
To: SQL devs <>
Version: -- development
CC: @PedroTadim, @yzchang

Last updated: 2020-06-03 16:58:53 +0200

Comment 27572

Date: 2020-02-20 16:25:46 +0100
From: @bernardom29

Monetdb server is crashing when executing a query with a window function which uses an extracted date (e.g. EXTRACT(MONTH FROM date)) as the order for the window. This doesn't happen if the window is also partitioned or if the window is ordered by the whole date.

How to reproduce:
1 - Create test table and add some values

create table employees (dep varchar(10), name varchar(20), salary double, hire_date date);
insert into employees values
('mgmt', 'Smith',81,str_to_date('10-08-2019', '%d-%m-%Y')),
('dev', 'Jones',55,str_to_date('9-08-2019', '%d-%m-%Y')),
('sls', 'Williams',55,str_to_date('14-07-2019', '%d-%m-%Y')),
('sls', 'Taylor',62,str_to_date('13-08-2019', '%d-%m-%Y')),
('dev', 'Brown',62,str_to_date('6-06-2019', '%d-%m-%Y')),
('mgmt', 'Davies',84,str_to_date('4-06-2019', '%d-%m-%Y')),
('sls', 'Evans',87,str_to_date('1-09-2019', '%d-%m-%Y')),
('sls', 'Wilson',72,str_to_date('21-09-2019', '%d-%m-%Y')),
('sls', 'Thomas',72,str_to_date('12-06-2019', '%d-%m-%Y')),
('mgmt', 'Johnson',100,str_to_date('12-07-2019', '%d-%m-%Y'));

2 - Execute this query, which crashes the server

SELECT dep, name, salary, SUM(salary) OVER (ORDER BY EXTRACT(MONTH FROM hire_date)) salary_sum FROM employees;

Note that these two queries don't crash the server:
SELECT dep, name, salary, SUM(salary) OVER (ORDER BY hire_date) salary_sum FROM employees;
SELECT dep, name, salary, SUM(salary) OVER (PARTITION BY dep ORDER BY EXTRACT(MONTH FROM hire_date)) salary_sum FROM employees;

Comment 27573

Date: 2020-02-20 16:40:07 +0100
From: MonetDB Mercurial Repository <>

Changeset 34fa1ae0cd2c 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=34fa1ae0cd2c

Changeset description:

Added test and output for Bug #6828. It gives right output on Nov2019 branch, but crashes on linear-hashing

Comment 27574

Date: 2020-02-20 16:50:36 +0100
From: @yzchang

Yeah, it crashes on default, not on Nov2019.

Comment 27575

Date: 2020-02-20 17:18:55 +0100
From: MonetDB Mercurial Repository <>

Changeset 71a80133d806 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=71a80133d806

Changeset description:

Fix for bug #6828, ie make sure window function call parameters have labels