You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
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:
Comment 20392
Date: 2014-10-31 14:14:50 +0100
From: @sjoerdmullender
Oct2014 has been released.
The text was updated successfully, but these errors were encountered: