Re: (Без темы)

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" :

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

,

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