Crash on SELECT with SERIAL aggregation and GROUP BY column alias's

[monetdb-team]

Date: 2014-03-29 20:49:00 +0100
From: Edward Clarkson <<edward.clarkson>>
To: SQL devs <>
Version: 11.17.13 (Jan2014-SP1)
CC: edward.clarkson, @njnes

Last updated: 2014-05-22 09:52:22 +0200

Comment 19722

Date: 2014-03-29 20:49:00 +0100
From: Edward Clarkson <<edward.clarkson>>

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_2) AppleWebKit/537.74.9 (KHTML, like Gecko) Version/7.0.2 Safari/537.74.9
Build Identifier:

The SELECT query in the reproduction instructions causes the server to terminate, and appears to be the result of using a 1) SERIAL column in an aggregate function with 2) the DISTINCT modifier and 3) a column alias as the aggregate. The following queries return an empty result set as expected (using the test table defined in the reproduction instructions):

[count DISTINCT int instead of serial]
SELECT a as t, count(distinct b) FROM test GROUP BY t;
[don't aggregate by column alias]
SELECT a, count(distinct c) FROM test GROUP BY a;
[don't count DISTINCT]
SELECT a as t, count(c) FROM test GROUP BY t;

The following also terminates:

SELECT a as t, sum(distinct b) FROM test GROUP BY t;

Reproducible: Always

Steps to Reproduce:

1. Start the server using the default demo database.
2. In the M5 client, execute:

CREATE TABLE test (a int, b int, c serial);
SELECT a as t, count(distinct c) FROM test GROUP BY t;

Actual Results:

Server terminates.

Expected Results:

Empty result set.

This is a stock windows install, but just in case this is helpful:

C:\Program Files\MonetDB\MonetDB5>M5server.bat --version
MonetDB 5 server v11.17.9 "Jan2014" (64-bit, 64-bit oids)
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 5.9GiB available memory, 4 available cpu cores
Libraries:
libpcre: 8.13 2011-08-16
openssl: OpenSSL 1.0.1b 26 Apr 2012
libxml2: 2.7.8
Compiled by: monet@LAB03 (x86_64-pc-winnt)
Compilation: cl -GF -W3 -WX -MD -nologo -Ox -Zi
Linking : cl -GF -W3 -WX -MD -nologo -Ox -Zi

Comment 19723

Date: 2014-03-30 12:22:42 +0200
From: MonetDB Mercurial Repository <>

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

Changeset description:

fixed bug #3463, make sure we also handle aliases in the aggr list when reducing
groupby expressions

Comment 19726

Date: 2014-03-31 15:59:28 +0200
From: Edward Clarkson <<edward.clarkson>>

Wow, that was fast, thanks Niels!

FWIW, I am really pleased with working with MonetDB the past several months. Really impressive OSS project.