Nested query crashes all versions of MonetDB or gives wrong result starting from Dec2016-SP2

[monetdb-team]

Date: 2017-03-17 10:37:29 +0100
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>
To: SQL devs <>
Version: 11.25.5 (Dec2016-SP1)
CC: @njnes

Last updated: 2017-03-31 13:28:26 +0200

Comment 25155

Date: 2017-03-17 10:37:29 +0100
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

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

All versions since at least Jul2015-SP4 are affected.

Reproducible: Always

Steps to Reproduce:

1. SELECT name, (SELECT COUNT(*) FROM sys.tables WHERE schema_id = s.id AND 1+1=3) AS table_count FROM sys.schemas AS s;

Notice the 1+1=3 condition.

Actual Results:

From Jul2015-SP4 up to Dec2016-SP1, the database crashes.

With Dec2016-SP2 and Dec2016-SP3, the results are wrongs:
+------+-------------+
| name | table_count |
+======+=============+
| sys | 38 |
| tmp | 6 |
+------+-------------+
2 tuples (11.789ms)

Expected Results:

0 for table_count.

Another example. This query gives NULL up to Jun2016-SP2 (which is not normal and should be 0), then crash on Dec2016-SP1, and gives wrong results on Dec2016-SP2 and Dec2016-SP3 (1 instead of 0).

sql>SELECT name, (SELECT COUNT(*) FROM sys.tables WHERE schema_id = s.id AND NOT system) AS table_count FROM sys.schemas AS s;
+----------+-------------+
| name | table_count |
+==========+=============+
| sys | 1 |
| tmp | 1 |
| json | 1 |
| profiler | 1 |
+----------+-------------+

while none of the schema have no system tables, thus we should have only 0 here too.

...

Comment 25160

Date: 2017-03-20 14:02:04 +0100
From: MonetDB Mercurial Repository <>

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

Changeset description:

fix bug #6245, ie properly handle outer-join in apply rewriter

Comment 25161

Date: 2017-03-20 14:02:25 +0100
From: @njnes

fixed in Dec2016 branch