On 24/07/2019 15.14, Илья Хайбуллин wrote:
How can I write a workable join by null-safe eqauls?
I have two tables: create table monet1_test ("Id" bigserial, key int, value varchar(2000)); create table monet2_test ("Id" bigserial, key int, value varchar(2000));
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);
I tried
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)));
and got errors
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.
Clearly a cross product. Table monet1_test has (60000-1) rows and table monet2_test has (200000-1) rows. If you multiply those numbers and multiply that with 8 (the size of the items produced by the cross product), you get 95997920008 which is only slightly smaller than the heap the server is trying to allocate. Of course, cross product produces two of those heaps, so you need double this space. Perhaps you can use UNION instead of OR to make the join condition simpler. I know it's only a work around.
I create bug-report: https://www.monetdb.org/bugzilla/show_bug.cgi?id=6737
I saw that. ;-) I won't close the bug report, even if the work around works. -- Sjoerd Mullender