Window function over union gives no result

[monetdb-team]

Date: 2014-08-04 19:00:43 +0200
From: Richard Hughes <<richard.monetdb>>
To: SQL devs <>
Version: 11.17.21 (Jan2014-SP3)
CC: @njnes

Last updated: 2014-10-31 14:14:50 +0100

Comment 19945

Date: 2014-08-04 19:00:43 +0200
From: Richard Hughes <<richard.monetdb>>

sql>create table foo (id int);
operation successful (10.821ms)
sql>insert into foo values (42);
1 affected row (2.482ms)
sql>select id-row_number() over (order by id) from (select id from foo union all select id from foo) as z;

Expected result:
41
40

Actual result:
<nothing, not even column headers>

Even more interestingly, EXPLAINing that last query gives no result too.

As far as I can determine, this is a minimal repro, i.e. removing any element from the SELECT will make it work.

FYI, the original query from which this repro was reduced was:
select min(id),max(id) from (select id,id-row_number() over (order by id) from [...view which is a union all of many monthly partition tables...] where id>=?) a(id,rn) group by rn;

The intent of this query is to find all the ID ranges which exist in the database, and is part of a replication system which needs to determine whether to INSERT or UPDATE an existing row.

Comment 19969

Date: 2014-08-06 17:06:23 +0200
From: @njnes

the relational optimizer incorrectly pushed down the unsafe (window function) into the union.

Comment 19972

Date: 2014-08-06 18:03:51 +0200
From: MonetDB Mercurial Repository <>

Changeset 39b41810dc43 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=39b41810dc43

Changeset description:

fixed bug #3523 (which also fixed 3526). Make sure we only push down a project
without window functions

Comment 20392

Date: 2014-10-31 14:14:50 +0100
From: @sjoerdmullender

Oct2014 has been released.