LEFT JOIN bug with CONST value

[monetdb-team]

Date: 2015-05-26 02:31:13 +0200
From: @skinkie
To: SQL devs <>
Version: 11.19.11 (Oct2014-SP3)
CC: @njnes

Last updated: 2016-01-22 13:51:48 +0100

Comment 20881

Date: 2015-05-26 02:31:13 +0200
From: @skinkie

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/43.0.2357.65 Safari/537.36
Build Identifier:

create table a (a integer);
create table b (a integer);
insert into a values (1);
select * from a left join (select a, 20 from b) as x using (a);

I expect:
1, NULL

In MonetDB I get:

+------+------+
| a | L2 |
+======+======+
| 1 | 20 |
+------+------+
1 tuple (4.655ms)

In PostgreSQL I get:

a | ?column?
---+----------
1 |
(1 row)

My original code depends on summing a column that should be 0 if the values are null. For some reason all values were 1, indicating a row was always joinable. It seems the above query has a problem with the constant, which in my own code was a true or 1.

Reproducible: Always

MonetDB 5 server v11.17.22 (64-bit, 64-bit oids)
This is an unreleased version
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 31.4GiB available memory, 8 available cpu cores
Libraries:
libpcre: 8.33 2013-05-28 (compiled with 8.33)
openssl: OpenSSL 1.0.1h 5 Jun 2014 (compiled with OpenSSL 1.0.1h 5 Jun 2014)
libxml2: 2.9.1 (compiled with 2.9.1)
Compiled by: skinkie@bigdata.openebs.nl (x86_64-unknown-linux-gnu)
Compilation: gcc -g -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wformat=2 -Wno-format-nonliteral -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -fstack-protector-all -Wstack-protector -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wunreachable-code
Linking : /usr/x86_64-pc-linux-gnu/bin/ld -m elf_x86_64

Comment 20887

Date: 2015-05-26 08:38:40 +0200
From: @sjoerdmullender

*** Bug #3726 has been marked as a duplicate of this bug. ***

Comment 20891

Date: 2015-05-27 10:45:23 +0200
From: @njnes

we pushed up the project with the constant through a left join. This is not correct. ie fixed.

Comment 20893

Date: 2015-05-27 10:59:39 +0200
From: MonetDB Mercurial Repository <>

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

Changeset description:

fixed bug #3725, ie don't push projections up through outer joins

Comment 21740

Date: 2016-01-22 13:51:48 +0100
From: MonetDB Mercurial Repository <>

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

Changeset description:

cleaner solutions for bug #3725 (ie push up projections in outerjoins)