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; 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

Well, let's say. Then the next question. Can I merge these tables? ---------------------- 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"); ---------------------- I get the same error. Union doesn't seem to fit here. The size of the heap increase not can. The original merge query had tables of ~60k rows and ~2kk rows. Then there was an attempt to allocate ~ 3TB. Or maybe you can tell me how you can implement Upsert by null-safe equals for tables of such sizes? Surely someone did something like this. 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

Try this one: select count(1) from actu.monet2_test t1 inner join actu.monet1_test t2 on coalesce(t1.key,-1) = coalese(t2.key,-1) On Wed, Jul 24, 2019 at 5:43 PM Илья Хайбуллин wrote:

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.

...

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

24.07.2019, 17:12, "Sjoerd Mullender" : 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