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

worked good, but

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

didnt work. (try allocate ~ 89 GB)

So Union is not good solution.

24.07.2019, 17:12, "Sjoerd Mullender" <sjoerd@acm.org>:
> 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" <sjoerd@acm.org>:
>>>  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
>
> ,
>
> _______________________________________________
> 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