Date: 2016-09-06 11:04:52 +0200
From: @swingbit
To: SQL devs <>
Version: 11.23.7 (Jun2016-SP1)
CC: martin.van.dinther

Last updated: 2016-10-13 10:03:55 +0200

Comment 22356

Date: 2016-09-06 11:04:52 +0200
From: @swingbit

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/52.0.2743.116 Safari/537.36
Build Identifier:

The following query gives:

2016-09-06 10:52:05 ERR trec2[8169]: could not find xy.a1
2016-09-06 10:52:05 ERR trec2[8169]: mserver5: <...>/src/sql/backends/monet5/rel_bin.c:2390: rel2bin_project: Assertion `0' failed.

It looks like a push-down of the LIMIT clause is the cause, as removing the LIMIT altogether makes the query work.

create table x (a1 int, prob double);
create table y (a1 int, prob double);
create table z (a1 int, prob double);

with xy as (
SELECT x.a1 as a1, x.prob + y.prob as prob FROM x, y WHERE x.a1=y.a1
UNION ALL
SELECT x.a1 as a1, x.prob as prob FROM x WHERE x.a1 NOT IN (select a1 from y)
UNION ALL
SELECT y.a1 as a1, y.prob as prob FROM y WHERE y.a1 NOT IN (select a1 from x)
)

SELECT xy.a1 as a1, xy.prob + z.prob as prob FROM xy, z WHERE xy.a1=z.a1
UNION ALL
SELECT xy.a1 as a1, xy.prob as prob FROM xy WHERE xy.a1 NOT IN (select a1 from z)
UNION ALL
SELECT z.a1 as a1, z.prob as prob FROM z WHERE z.a1 NOT IN (select a1 from xy)

LIMIT 5;

Reproducible: Always

Comment 22357

Date: 2016-09-06 14:21:56 +0200
From: @swingbit

Actually, it may not be the limit. The following query has no limit but some more joins in the subqueries instead), fails as well:

with xy as (
SELECT x.a1 as a1, x.prob + y.prob as prob FROM x, y WHERE x.a1=y.a1
UNION ALL
SELECT x.a1 as a1, x.prob as prob FROM x WHERE x.a1 NOT IN (SELECT x.a1 FROM x, y WHERE x.a1=y.a1)
UNION ALL
SELECT y.a1 as a1, y.prob as prob FROM y WHERE y.a1 NOT IN (SELECT x.a1 FROM x, y WHERE x.a1=y.a1)
)
SELECT xy.a1 as a1, xy.prob + z.prob as prob FROM xy, z WHERE xy.a1=z.a1
UNION ALL
SELECT xy.a1 as a1, xy.prob as prob FROM xy WHERE xy.a1 NOT IN (SELECT xy.a1 FROM xy, z WHERE xy.a1=z.a1)
UNION ALL
SELECT z.a1 as a1, z.prob as prob FROM z WHERE z.a1 NOT IN (SELECT xy.a1 FROM xy, z WHERE xy.a1=z.a1);

Comment 22372

Date: 2016-09-15 13:30:08 +0200
From: MonetDB Mercurial Repository <>

Changeset f740287a7755 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

Add test for Bug #4064.

Comment 22373

Date: 2016-09-15 13:33:17 +0200
From: Martin van Dinther <<martin.van.dinther>>

It appears that the LIMIT 5 is causing the problem, see test case results.
Without it both queries run okay.

Comment 22388

Date: 2016-09-23 22:31:29 +0200
From: MonetDB Mercurial Repository <>

Changeset cb072f9b954e 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=cb072f9b954e

Changeset description:

fixes for both bug #4058 and bug #4064

In both cases the same expression (pointer) was used, which isn't correct.
Because with rewrites both uses change. They are now correctly referencing
a single projection column

Comment 24489

Date: 2016-10-13 10:03:55 +0200
From: @sjoerdmullender

Jun2016-SP2 has been released.