Re: (Без темы)

On 24/07/2019 15.56, Илья Хайбуллин wrote:

According to this logic, this request should not work either, but it does. Or I something not understand? select count(1) from monet2_test t1 inner join monet1_test t2 on t1.key = t2.key;

This test is simpler and can be optimized. The test (t1.key is null and t2.key is null) is also simple enough to be optimized. Combining the two with an OR is currently too complex.

24.07.2019, 16:51, "Sjoerd Mullender" :

...

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

,

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

---

users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- Sjoerd Mullender