Date: 2014-08-19 15:34:41 +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:53 +0100

Comment 20059

Date: 2014-08-19 15:34:41 +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:

When selecting a field from a view, made of several UNION ALL, that doesn't appear in the ORDER clause, the query produce nothing at all. Even TRACE and EXPLAIN show nothing. (The default optimizer "default_pipe" is used.)

Queries that show the issues are of the form:

SELECT field_A FROM some_view ORDER BY field_B LIMIT 1;

The bug disappear if using simple UNION instead of UNION ALL.

Reproducible: Always

Steps to Reproduce:

Create some set of data:

create table foo_1 (id int, value text); insert into foo_1 values (1, 'A');
create table foo_2 (id int, value text); insert into foo_2 values (2, 'B');
create view foo as select * from foo_1 union all select * from foo_2;

(Note that replacing UNION ALL with UNION cause the following queries to work.)

Actual Results:

Nothing is produced when selecting a field that is not the one used to sort the result:

sql>select id from foo order by value limit 1;

sql>select value from foo order by id limit 1;

sql>explain select id from foo order by value limit 1;

sql>trace select id from foo order by value limit 1;

Expected Results:

Expected something reassuring regarding MonetDB ability to perform simple SQL queries :/

We're evaluating MonetDB to replace PostgreSQL, and we wonder how such large bugs can pass through the numerous unit tests actually in MonetDB. Performances are wonderful, but we've hard time figuring if the product is robust enough for production environment.

Few weeks ago, we also found a bug related to another simple query with UNION ALL (which we filled here, as 3518), so maybe it's kind of related. We don't know.

Comment 20064

Date: 2014-08-20 12:18:32 +0200
From: @njnes

When we push down the limit (topn) all columns should be kept (also the order by column).

Comment 20066

Date: 2014-08-20 12:22:31 +0200
From: MonetDB Mercurial Repository <>

Changeset 36a9402c22b5 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=36a9402c22b5

Changeset description:

fixed push topn down, also include the order by columns, solves bug #3547.

Comment 20395

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

Oct2014 has been released.