Date: 2014-08-25 18:31:18 +0200
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>
To: SQL devs <>
Version: 11.17.21 (Jan2014-SP3)
CC: @njnes

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

Comment 20088

Date: 2014-08-25 18:31:18 +0200
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Firefox/31.0
Build Identifier:

MonetDB crash (or ignore the query) when using a sub SELECT with an outer ORDER BY clause, in both 11.17.21 and dev (11.20.0)

Reproducible: Always

Steps to Reproduce:

Here is the minimal query able to reproduce the problem:

select a+b from (select 1 as a,1 as b) as q order by a limit 1;

Note the three condition to trigger the bug: a computation on several fields, an sub select and on order by.

Actual Results:

No answer from mclient.

With 11.17.21, I think it's related to bugs we already filled in the past weeks. We see nothing in the serveur log.

However, in the development branch (whose tip is at 52799:d7d082274cc2), we have the following log:

2014-08-25 16:14:12 ERR nova[20887]: mserver5: rel_bin.c:2359: rel2bin_project: Assertion `0' failed.
2014-08-25 16:14:12 MSG nova[20887]: could not find q.a
2014-08-25 16:14:12 MSG nova[20887]: (null!).single_value
2014-08-25 16:14:12 MSG nova[20887]: (null!).single_value
2014-08-25 16:14:12 MSG nova[20887]: L1.L1
2014-08-25 16:14:12 MSG merovingian[3771]: database 'test' (20887) was killed by signal SIGABRT

Moreover when altering the query to use an UNION in the sub select with the following query:

select a+b from (select 1 as a,1 as b union select 1, 1) as q order by a limit 1;

we get a more surprising log message:

2014-08-25 16:06:58 ERR nova[19417]: mserver5: rel_bin.c:2275: rel2bin_project: Assertion `0' failed.
2014-08-25 16:06:58 MSG nova[19417]: could not find q.b
2014-08-25 16:06:58 MSG nova[19417]: 0ê
h�.L3
2014-08-25 16:06:58 MSG nova[19417]: q.a
2014-08-25 16:06:58 MSG merovingian[3771]: database 'test' (19417) was killed by signal SIGABRT

(There are really random bytes on the third line.)

Trying to perform the same request a second time in mclient cause it to quit and nothing is logged:

sql>select a+b from (select 1 as a,1 as b union select 1,1) as q order by a limit 1;
sql>select a+b from (select 1 as a,1 as b union select 1,1) as q order by a limit 1;
write error on stream

Comment 20089

Date: 2014-08-25 18:59:11 +0200
From: @sjoerdmullender

When trying the query
select a+b from (select 1 as a,1 as b) as q order by a limit 1;
on the Jan2014 branch, I get:
mserver5: /home/sjoerd/src/MonetDB/stable/sql/backends/monet5/sql_gencode.c:965: _dumpstmt: Assertion `s->nrcols' failed.
Aborted - core dumped

On the Oct2014 branch, I get:
could not find q.a
(null!).single_value
(null!).single_value
L1.L1
mserver5: /home/sjoerd/src/MonetDB/candidate/sql/backends/monet5/rel_bin.c:2359: rel2bin_project: Assertion `0' failed.
Aborted - core dumped

Comment 20092

Date: 2014-08-26 08:28:53 +0200
From: MonetDB Mercurial Repository <>

Changeset 6c4080d30b3e 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=6c4080d30b3e

Changeset description:

fixed reuse

fixed bug #3554, ie make sure single value order by /limit work

Comment 20093

Date: 2014-08-26 08:30:51 +0200
From: @njnes

solved in oct2014 branch. Add value to column conversion for the single result orderby/limit.

Comment 20096

Date: 2014-08-26 11:35:16 +0200
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

Thanks for the quick fix. The reactivity is much appreciated :)

However, the other part still fail (when using an UNION), when querying the following:

select a+b from (select 1 as a,1 as b union select 1,1) as q order by a limit 1;

Output in log is: (with the tip of Oct2014 at this date, 52822:972d327c8d5f)

2014-08-26 09:26:29 ERR nova[29878]: mserver5: rel_bin.c:2275: rel2bin_project: Assertion `0' failed.
2014-08-26 09:26:29 MSG nova[29878]: could not find q.b
2014-08-26 09:26:29 MSG nova[29878]: .L3
2014-08-26 09:26:29 MSG nova[29878]: q.a

The session:

Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014-SP3)
Database: MonetDB v11.19.0 (unreleased), 'mapi:monetdb://fjolliton:50000/nova'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>select a+b from (select 1 as a,1 as b union select 1, 1) as q order by a limit 1;
Connection terminated

Comment 20097

Date: 2014-08-27 11:59:24 +0200
From: MonetDB Mercurial Repository <>

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

Changeset description:

fixed second part of bug #3554, ie handle topn push down properly

Comment 20098

Date: 2014-08-27 11:59:55 +0200
From: @njnes

also fixed the topn problem with this query

Comment 20099

Date: 2014-08-27 12:40:40 +0200
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

Works great. Thanks!

Comment 20358

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

Oct2014 has been released.