Date: 2016-08-02 07:10:35 +0200
From: Roman <<kislenok.roman>>
To: SQL devs <>
Version: 11.23.7 (Jun2016-SP1)
CC: @njnes

Last updated: 2016-10-13 10:04:32 +0200

Comment 22276

Date: 2016-08-02 07:10:35 +0200
From: Roman <<kislenok.roman>>

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

Just updated to new release and catch a bug within a query with "OR".

Reproducible: Always

Steps to Reproduce:

1. create table test1 (a int, b int, d int);

2. create table test_dic1 (a int, c int);

3. insert into test_dic1 values (1, 1), (1, 2);

4. insert into test1 values (1, 2, 1), (1, 3, 2), (2, 2, 1), (2, 3, 2)

5. select dd.*
   from test_dic1 as dd
   inner join test1 as d1 on d1.a = dd.a and d1.d = 1
   left join test1 as d2 on d2.a = dd.a and d2.d = 2
   where dd.c = 1

Get 1 row as expected

6. select dd.*
   from test_dic1 as dd
   inner join test1 as d1 on d1.a = dd.a and d1.d = 1
   left join test1 as d2 on d2.a = dd.a and d2.d = 2
   where dd.c = 1 or dd.c = 3

Got 2 rows! But there are no dd.c = 3

Actual Results:

2 rows

Expected Results:

1 row

Comment 22277

Date: 2016-08-02 07:22:36 +0200
From: Roman <<kislenok.roman>>

If I replace "left join" with "inner join" the result would be ok:
"select *
from test_dic1 as dd
inner join test1 as d1 on d1.a = dd.a and d1.d = 1
inner join test1 as d2 on d2.a = dd.a and d2.d = 2
where dd.c = 1 or dd.c = 3"
prodeces 1 row.

But:
"select *
from test_dic1 as dd
inner join test1 as d1 on d1.a = dd.a and d1.d = 1
left join test1 as d2 on d2.a = dd.a and d2.d = 2
where dd.c in (1, 3)"
also produces 1 expected row

Comment 22278

Date: 2016-08-02 07:57:21 +0200
From: Roman <<kislenok.roman>>

Actualy by some Mumbo-Jumbo you could get expected results:
"select *
from test_dic1 as dd
inner join test1 as d1 on d1.a = dd.a
left join test1 as d2 on d2.a = dd.a and d2.d = 2
where dd.c = 1 or dd.c = 3
and d1.d = 1
and (((dd.c = 1 or dd.c = 3) and d2.d is null) or ((dd.c = 1 or dd.c = 3) and d2.d = 2))"

Comment 22290

Date: 2016-08-11 12:50:39 +0200
From: MonetDB Mercurial Repository <>

Changeset 16c01c543cab made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

Add test innerjoin-leftjoin-or.Bug-4049

Comment 22293

Date: 2016-08-11 21:29:22 +0200
From: MonetDB Mercurial Repository <>

Changeset 0d2c55c221c2 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=0d2c55c221c2

Changeset description:

fixed leftjoin problem / bug #4049

Comment 22294

Date: 2016-08-11 21:30:03 +0200
From: @njnes

fixed by marking the end of the outerjoin properly

Comment 24499

Date: 2016-10-13 10:04:32 +0200
From: @sjoerdmullender

Jun2016-SP2 has been released.