Nested Common Table Expressions Crash

[monetdb-team]

Date: 2014-01-09 12:51:02 +0100
From: @hannesmuehleisen
To: SQL devs <>
Version: 11.15.11 (Feb2013-SP3)
CC: @njnes

Last updated: 2014-02-20 15:02:52 +0100

Comment 19442

Date: 2014-01-09 12:51:02 +0100
From: @hannesmuehleisen

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

I have a two-layers deep common table expression query which does not work. See the attached synthetic example which provokes the bug.

Reproducible: Always

Steps to Reproduce:

CREATE TABLE wtf(a int, b int, c int);

INSERT INTO wtf VALUES(1, 2, 3);
INSERT INTO wtf VALUES(4, 5, 6);

WITH a AS
(SELECT a,
b
FROM wtf
WHERE a IN (1,
4)),
b AS
(SELECT a,
b,
count() AS d
FROM a
GROUP BY a,
b),
c AS
(SELECT a,
count() AS e
FROM b
GROUP BY b)
SELECT *
FROM c;

Actual Results:

Crash or no reaction from server

Expected Results:

A result set or an error.

PLAN works, EXPLAIN also leads to crash.
PLAN output:

+---------------------------------------------------------------+
| rel |
+===============================================================+
| project ( |
| | group by ( |
| | | project ( |
| | | | group by ( |
| | | | | project ( |
| | | | | | select ( |
| | | | | | | table(sys.wtf) [ wtf.a, wtf.b ] COUNT |
| | | | | | ) [ wtf.a in (int[tinyint "1"], int[tinyint "4"]) ] |
| | | | | ) [ wtf.a as a.a, wtf.b as a.b ] |
| | | | ) [ a.a, a.b ] [ a.a, a.b ] |
| | | ) [ a.a as b.a, a.b as b.b ] |
| | ) [ b.b ] [ b.b, sys.count() NOT NULL as L2.L2 ] |
| ) [ b.a as c.a, L2 NOT NULL as c.e ] |
+---------------------------------------------------------------+

Comment 19443

Date: 2014-01-10 17:00:20 +0100
From: MonetDB Mercurial Repository <>

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

Changeset description:

fix Bug #3417 - Nested Common Table Expressions Crash (edit)
properly check if a column result is a group by expression (or an aggregate)

Comment 19444

Date: 2014-01-10 17:00:23 +0100
From: MonetDB Mercurial Repository <>

Changeset 11ae2f3582c6 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=11ae2f3582c6

Changeset description:

added test for Bug #3417

Comment 19445

Date: 2014-01-10 17:00:58 +0100
From: @njnes

fixed by better checking if a projection column really is a group by or
aggregate expression.

Comment 19610

Date: 2014-02-20 15:02:52 +0100
From: @sjoerdmullender

Jan2014 has been released.