Date: 2019-07-24 14:49:34 +0200
From: Ilya <>
To: GDK devs <>
Version: 11.33.3 (Apr2019)
CC: kujit1, @PedroTadim, renalkoclok

Last updated: 2020-06-15 17:22:19 +0200

Comment 27162

Date: 2019-07-24 14:49:34 +0200
From: Ilya <>

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

When I try to create a join of two tables under the null-safe equal, like (t1 = t2 or (t1 is null and t2 is null)), the query crashes with the message that there was a failed attempt to allocate some giant memory count

Reproducible: Always

Steps to Reproduce:

1. create table monet1_test ("Id" bigserial, key int, value varchar(2000));
   create table monet2_test ("Id" bigserial, key int, value varchar(2000));

2. insert into monet1_test select value "Id", value "key", value "value" from generate_series(1,60000);
   insert into monet2_test select value "Id", value "key", value "value" from generate_series(1,200000);

3. select count(1) from monet2_test t1 inner join monet1_test t2 on ((t1.key = t2.key) or ((t1.key is null) and (t2.key is null)));

Actual Results:

GDK reported error: MT_mmap: CreateFileMapping(0000000000001200, &sa, 4, 22, 1508704256, NULL) failed
OS: The printer is out of paper.
HEAPalloc: Insufficient space for HEAP of 95997921280 bytes.
MT_mmap: CreateFileMapping(00000000000011B4, &sa, 4, 22, 1508704256, NULL) failed
OS: The printer is out of paper.
HEAPalloc: Insufficient space for HEAP of 95997921280 bytes.

Expected Results:

to obtain the query result

mserver5 version:

MonetDB 5 server 11.33.3 (Apr2019) (64-bit)
Copyright (c) 1993 - July 2008 CWI
Copyright (c) August 2008 - 2019 MonetDB B.V., all rights reserved
Visit https://www.monetdb.org/ for further information
Found 7.9GiB available memory, 8 available cpu cores
Libraries:
libpcre: 8.41 2017-07-05
openssl: OpenSSL 1.1.0g 2 Nov 2017
libxml2: 2.9.8
Compiled by: monet@LAB03 (x86_64-pc-winnt)
Compilation: cl -GF -W3 -WX -MD -nologo -Ox -Zi -Oi
Linking : cl -GF -W3 -WX -MD -nologo -Ox -Zi -Oi

Comment 27163

Date: 2019-07-24 14:55:31 +0200
From: Ilya <>

Created attachment 622
test case

Attached file: test_case.txt (text/plain, 1208 bytes)
Description: test case

Comment 27164

Date: 2019-07-24 15:03:20 +0200
From: Ilya <>

The problem was detected when trying to write an upsert function:

MERGE INTO monet2_test
USING monet1_test AS t_1
ON ("monet2_test"."key" = t_1."key" OR ("monet2_test"."key" IS NULL AND t_1."key" IS NULL))
WHEN MATCHED THEN
UPDATE SET "key" = t_1."key", "value" = t_1."value"
WHEN NOT MATCHED THEN
INSERT ("Id", "key", "value")
VALUES (t_1."Id", t_1."key", t_1."value");

When trying to overwrite upsert without using merge, it was found that null-safe eqals also does not work for joins.

Changing null-safe eqauls to other expressions did not help. I tried:
stored functions, case-when and if expressions, exists (select t1.key intersect select t2.key)

Comment 27165

Date: 2019-07-24 15:10:23 +0200
From: Ilya <>

select count(1) from monet2_test t1 inner join monet1_test t2 on ((t1.key = t2.key) or ((t1.key is null) and (t2.key is null)));

also not working

Comment 27197

Date: 2019-08-02 10:18:23 +0200
From: MonetDB Mercurial Repository <>

Changeset 27336f65e008 made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

Added test for bug #6737.

Comment 27200

Date: 2019-08-05 12:34:23 +0200
From: MonetDB Mercurial Repository <>

Changeset a65fcf67b6c0 made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

Replaced merge statement's inner join with a left join between the input relation and the target table.

With this approach, it is not needed to generate an upper anti-join for "when not matched" clauses while "when matched" clauses are also present.
Also reduced the generated table sizes for bug #6737.

Comment 27767

Date: 2020-06-03 09:11:37 +0200
From: @sjoerdmullender

The content of attachment 668 has been deleted for the following reason:

spam