Date: 2019-04-18 18:58:25 +0200
From: Manuel <>
To: SQL devs <>
Version: 11.29.3 (Mar2018)
CC: @njnes

Last updated: 2019-11-28 10:00:02 +0100

Comment 26963

Date: 2019-04-18 18:58:25 +0200
From: Manuel <>

User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.103 Safari/537.36
Build Identifier:

3 bugs that occur when executing a subselect which is ‘joined’ to the outer query. It is possible these are all symptoms of the same underlying bug.

Setup

To Illustrate the issues, we will be using the following two tables

create table "testTable1" (
"A" varchar(255),
"B" varchar(255)
);
insert into "testTable1" values ('Cat1', 'Cat1');
insert into "testTable1" values ('Cat2', 'Cat2');
insert into "testTable1" values ('Cat3', 'Cat1');

create table "testTable2" (
"A" varchar (255),
"B" double
);
insert into "testTable2" values ('Cat1', 2);
insert into "testTable2" values ('Cat2', 3);
insert into "testTable2" values ('Cat2', 4);
insert into "testTable2" values (null, null);

Bug #1: wrong results in subselect count

Q1:

select "A", "B", (
select count(1)
from "testTable1" "inner"
where ("inner"."B" = "outer"."A")
) from "testTable1" "outer"

A B L40

Cat1 Cat1 2
Cat2 Cat2 1
Cat3 Cat1 1

The last row is wrong: because there are no records whose value of “B” is ‘Cat3’ I was expecting:

A B L40

Cat1 Cat1 2
Cat2 Cat2 1
Cat3 Cat1 0

Bug #2: records disappearing from outer select

Q2:

select "A", "B", (
select count(1)
from "testTable1" "inner"
where ("inner"."B" = "outer"."A") and ("outer"."A" is not null)
) from "testTable1" "outer"

Because none of the cells are null, this is equivalent to Q1 above.

Yet, a record disappears from the output, now containing just 2 records:

A B L40

Cat1 Cat1 2
Cat2 Cat2 1

whereas it should be the same as the expected results for Q1.

Bug #3: No records in the output if the condition in the subselect is false.

Q3:
select "A", "B", (
select sum("B")
from "testTable2" "inner"
where (
"inner"."A" = "outer"."A" or
("inner"."A" is null and "outer"."A" is null)
)
) from "testTable2" "outer"

Returns

A B L7

Cat1 2 2
Cat2 3 7

Cat2 4 7

Which is the correct result, however by changing the where condition however, it is possible to make records disappear from the output. For example:

Q4
select "A", "B", (
select sum("B")
from "testTable2" "inner"
where (
"inner"."A" = "outer"."A" or
("inner"."A" is null and "outer"."A" is null)
) and ("A" = 'Cat7')
) from "testTable2" "outer"

Here the subquery has no records matching its constraints. I expect all 4 records in the outer query to appear. But it returns just one record:

A B L7

And

Q5
select "A", "B", (
select sum("B")
from "testTable2" "inner"
where (
"inner"."A" = "outer"."A" or
("inner"."A" is null and "outer"."A" is null)
) and (true = false)
) from "testTable2" "outer"

Returns no records

A B L7

In both cases (Q4 and Q5) I was expecting

A B L7

Cat1 2
Cat2 3
Cat2 4

Note that in all these cases other vendors (e.g. PostgreSQL) provide the expected results.

Reproducible: Always

Steps to Reproduce:

1. Create tables testTable1 and testTable2 as in description
2. Execute Queries Q1 - Q5

Q1:
select "A", "B", (
select count(1)
from "testTable1" "inner"
where ("inner"."B" = "outer"."A")
) from "testTable1" "outer"

Q2:

select "A", "B", (
select count(1)
from "testTable1" "inner"
where ("inner"."B" = "outer"."A") and ("outer"."A" is not null)
) from "testTable1" "outer"

Q4:
select "A", "B", (
select sum("B")
from "testTable2" "inner"
where (
"inner"."A" = "outer"."A" or
("inner"."A" is null and "outer"."A" is null)
) and ("A" = 'Cat7')
) from "testTable2" "outer"

Q5
select "A", "B", (
select sum("B")
from "testTable2" "inner"
where (
"inner"."A" = "outer"."A" or
("inner"."A" is null and "outer"."A" is null)
) and (true = false)
) from "testTable2" "outer"

Actual Results:

Q1:
A B L40

Cat1 Cat1 2
Cat2 Cat2 1
Cat3 Cat1 1

Q2:
A B L40

Cat1 Cat1 2
Cat2 Cat2 1

Q4:
A B L7

Q5:
A B L7

Expected Results:

Q1:
A B L40

Cat1 Cat1 2
Cat2 Cat2 1
Cat3 Cat1 0

Q2:
A B L40

Cat1 Cat1 2
Cat2 Cat2 1
Cat3 Cat1 0

Q4 and Q5:
A B L7

Cat1 2
Cat2 3
Cat2 4

I will attach a pdf with a document. Happy to share the google drive version of the document with anyone interested so we can use comments and annotations (I just need the email of the people interested)

Comment 26964

Date: 2019-04-18 18:59:12 +0200
From: Manuel <>

Created attachment 616
The document describing the issue

Attached file: Monetdb Bugs in Subselect statements..pdf (application/pdf, 94432 bytes)
Description: The document describing the issue

Comment 26968

Date: 2019-04-24 08:47:31 +0200
From: Manuel <>

any thoughts ? Thanks in advance!

Comment 26969

Date: 2019-04-24 09:51:03 +0200
From: @sjoerdmullender

There is an effort underway to improve subquery support. This is being done in the subquery branch. Once this has improved enough, this branch will be merged into the default branch, ready for the next (after the April 2019 release) feature release.

In that branch, most of the problems you describe have been fixed. The two queries that don't work according to your description are the two that use count(1). If you were to replace those with count(*), the queries give the correct result (in the subquery branch).

Comment 26970

Date: 2019-04-24 09:51:19 +0200
From: MonetDB Mercurial Repository <>

Changeset 0f31cba8de23 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=0f31cba8de23

Changeset description:

Add test for bug #6700.

Comment 26972

Date: 2019-04-26 11:39:29 +0200
From: MonetDB Mercurial Repository <>

Changeset b25399891430 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=b25399891430

Changeset description:

fixed bug #6700 (is handle count(1) with outer ref like count(*))
approved output

Comment 26980

Date: 2019-05-01 12:54:51 +0200
From: @njnes

fixed in default. subquery handling has been reimplemented