case statement in "order by" clause doesn't work when used together with "group by"

[monetdb-team]

Date: 2013-10-17 22:58:03 +0200
From: Klaudiusz <>
To: SQL devs <>
Version: 11.15.17 (Feb2013-SP5)
CC: @njnes

Last updated: 2013-12-03 13:59:37 +0100

Comment 19283

Date: 2013-10-17 22:58:03 +0200
From: Klaudiusz <>

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/30.0.1599.101 Safari/537.36
Build Identifier:

Following query doesn't return anything.
SELECT sum(b) from a group by k order by case when sum(b) is null then 1 else 0 end,sum(b);

Reproducible: Always

Steps to Reproduce:

CREATE table a (k int,b int);
INSERT into a values (1,2);
INSERT into a values (2,2);
INSERT into a values (3,3);
INSERT into a values (4,65);
INSERT into a values (5,21);
INSERT into a values (6,null);
INSERT into a values (7,null);
INSERT into a values (8,null);
INSERT into a values (9,null);

SELECT sum(b) from a group by k order by case when sum(b) is null then 1 else 0 end,sum(b);

Actual Results:

nothing

Expected Results:

+------+
| L1 |
+======+
| 2 |
| 2 |
| 3 |
| 21 |
| 65 |
| null |
| null |
| null |
| null |
+------+

MonetDB 5 server v11.15.17 "Feb2013-SP5" (64-bit, 64-bit oids)
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 15.6GiB available memory, 4 available cpu cores
Libraries:
libpcre: 7.8 2008-09-05 (compiled with 7.8)
openssl: OpenSSL 1.0.0 29 Mar 2010 (compiled with OpenSSL 1.0.0-fips 29 Mar 2010)
libxml2: 2.7.6 (compiled with 2.7.6)
Compiled by: n@n(x86_64-unknown-linux-gnu)
Compilation: gcc -g -O2
Linking : /usr/bin/ld -m elf_x86_64

Comment 19284

Date: 2013-10-18 09:20:07 +0200
From: Klaudiusz <>

It seems that this bug exists only in Feb2013-SP5 tag (and probably also in Feb2013-SP4).
I've checked Feb2013 and default branches and results are as expected.
So probably it is fixed.

Comment 19285

Date: 2013-10-18 09:20:38 +0200
From: MonetDB Mercurial Repository <>

Changeset 7d15a64ac3b2 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

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

Changeset description:

Added test for bug #3388.

Comment 19286

Date: 2013-10-18 09:37:40 +0200
From: @sjoerdmullender

Actually, when assertions are enabled, the test causes an assertion failure in the current Feb2013 branch.

Comment 19287

Date: 2013-10-20 13:07:45 +0200
From: MonetDB Mercurial Repository <>

Changeset 28744b9863d5 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=28744b9863d5

Changeset description:

partial fix for bug #3388, ie fixed the sql language part of the bug.
Todo handle sum with null's in case of mitosis/mergetable properly.

Comment 19288

Date: 2013-10-20 15:25:46 +0200
From: @njnes

fixed in 2 steps. Fixed the crash on rewriting the groupby/orderby part. and fixed a bug in handling null only sets.

Comment 19296

Date: 2013-10-22 15:12:04 +0200
From: MonetDB Mercurial Repository <>

Changeset 92f9d7980dd3 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

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

Changeset description:

Complete changeset [798d9ad8af9a](https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=798d9ad8af9a)  handle all cases.
Also, do it with fewer times calculating the same expression.
See bug #3388.

Comment 19382

Date: 2013-12-03 13:59:37 +0100
From: @sjoerdmullender

Feb2013-SP6 has been released.