AND after ON () of LEFT OUTER JOIN with certain expressions will cause crash

[monetdb-team]

Date: 2014-02-21 14:43:51 +0100
From: janmartijn
To: SQL devs <>
Version: 11.17.9 (Jan2014)
CC: @njnes

Last updated: 2014-03-11 09:19:33 +0100

Comment 19632

Date: 2014-02-21 14:43:51 +0100
From: janmartijn

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:28.0) Gecko/20100101 Firefox/28.0
Build Identifier:

The query with a LEFT OUTER JOIN of two tables has problems with the AND clause after the ON clause. After starting the query the monetdb server will crash.

Reproducible: Always

Steps to Reproduce:

1.Startup client in windows 7 connecting to local database
2.Entering query:

DROP TABLE table1;
DROP TABLE table2;
CREATE TABLE "sys"."table1" (
"taskid" INTEGER,
"cid" INTEGER);

CREATE TABLE "sys"."table2" (
"taskid" INTEGER,
"cname" VARCHAR(255));
SELECT * FROM table1 t1
LEFT OUTER JOIN table2 t2
ON (t1.taskid = t2.taskid)
AND (
t1.cid IS NULL OR
t1.cid = t2.taskid OR
t2.cname = 'test'
);

Actual Results:

client reports : Connection terminated

Expected Results:

With the WHERE clause same result will be reached in this case i think.

DROP TABLE table1;
DROP TABLE table2;
CREATE TABLE "sys"."table1" (
"taskid" INTEGER,
"cid" INTEGER);

CREATE TABLE "sys"."table2" (
"taskid" INTEGER,
"cname" VARCHAR(255));
SELECT * FROM table1 t1
LEFT OUTER JOIN table2 t2
ON (t1.taskid = t2.taskid)
WHERE (
t1.cid IS NULL OR
t1.cid = t2.taskid OR
t2.cname = 'test'
);

Also when you remove "OR t2.cname = 'test'" from the AND clause of the problem query it will not cause a crash

MonetDB 5 server v11.15.19 "Feb2013-SP6"
Serving database 'demo', using 8 threads
Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linke
Found 7.874 GiB available main-memory.
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
Listening for connection requests on mapi:monetdb://127.0.0.1:50000/
MonetDB/JAQL module loaded

Welcome to mclient, the MonetDB/SQL interactive terminal (Feb2013-SP6)
Database: MonetDB v11.15.19 (Feb2013-SP6), 'demo'
MonetDB/SQL module loaded

os: Windows 7 uptodate

Comment 19635

Date: 2014-02-21 17:16:04 +0100
From: MonetDB Mercurial Repository <>

Changeset 1784cbdea0c8 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=1784cbdea0c8

Changeset description:

Test for bug #3444.

Comment 19636

Date: 2014-02-21 19:34:39 +0100
From: @njnes

fix bug in handling the combination of OUTER and OR expressions. Normal expression are pushed down very early, we need to prevent this on OUTER/OR expressions.

Comment 19637

Date: 2014-02-21 21:50:26 +0100
From: MonetDB Mercurial Repository <>

Changeset 4b6b92785afa 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=4b6b92785afa

Changeset description:

fixed bug #3444,ie handle or and outer properly

Comment 19694

Date: 2014-03-11 09:19:33 +0100
From: @sjoerdmullender

Jan2014-SP1 has been released.