MERGE statement is deleting rows if the column is set as NOT NULL even though it should not

[v-from-meiro]

Describe the bug
MERGE statement is deleting rows if the column is set as NOT NULL even though it should not.

To Reproduce

drop table sys.v;
CREATE TABLE "sys"."v" (
	"id"      INTEGER NOT NULL,
	"version" INTEGER NOT NULL,
	"value"   DOUBLE
);

insert into sys."v" values (1,1622470128,1);
insert into sys."v" values (2,1622470128,2);
insert into sys."v" values (3,1622470128,3);
insert into sys."v" values (4,1622470128,4);
insert into sys."v" values (5,1622470128,5);

MERGE INTO sys."v" dst 
USING (SELECT id, version FROM (SELECT id, version, DENSE_RANK() OVER (PARTITION BY id ORDER BY id, version DESC) AS rn FROM sys."v")t WHERE rn > 1 ) src
ON src.id = dst.id AND src.version = dst.version
WHEN MATCHED THEN DELETE;

Above merge statement deletes all rows in the table, even if it should not.

Expected behavior

drop table sys.v;
CREATE TABLE "sys"."v" (
	"id"      INTEGER,
	"version" INTEGER,
	"value"   DOUBLE
);

insert into sys."v" values (1,1622470128,1);
insert into sys."v" values (2,1622470128,2);
insert into sys."v" values (3,1622470128,3);
insert into sys."v" values (4,1622470128,4);
insert into sys."v" values (5,1622470128,5);

MERGE INTO sys."v" dst 
USING (SELECT id, version FROM (SELECT id, version, DENSE_RANK() OVER (PARTITION BY id ORDER BY id, version DESC) AS rn FROM sys."v")t WHERE rn > 1 ) src
ON src.id = dst.id AND src.version = dst.version
WHEN MATCHED THEN DELETE;

If the column is NOT set as NOT NULL, the statement works as expected.

Software versions
Linux, Docker, latest version od MDB (MonetDB v11.39.17 (Oct2020-SP5))

[PedroTadim]

Hello! I noticed this issue a few months ago too, and it is fixed in the Jul2021 branch, so the fix will be available in the next feature release.

[v-from-meiro]

Oh, good to know. It is a pity the fix was not released earlier, still thanks a lot for the reply.

[PedroTadim]

As a side note, I had to disable subqueries on the joining clause too. This is because the current way they are handled in the query parser makes it difficult to integrate with merge statements.

[v-from-meiro]

@PedroTadim Could you, please, elaborate more? Does it mean that something like:

MERGE INTO table_name dst 
USING (subquery) src
ON condition
WHEN MATCHED THEN DELETE;

would not be possible to do?

[PedroTadim]

No, your example is fine. I disabled subqueries in the join clause, ie the ON condition

[v-from-meiro]

Oh, yeah, I read your message again and it makes sense, I need more coffe. Thank you very much for all the info and have a nice day!

[PedroTadim]

And we should have tested this feature more back then too :)