On the fly NOT NULL checks for MERGE without DELETE-clause

Detail name	Value
Changelog Number	2616
Type	Improvement
Status	Resolved
Fix Versions	EXASolution 5.0.21, EXASOL 6.0.7
Resolution Date	2017-12-11

Improvement

At the moment NOT NULL constraints in MERGE statement are checked for all rows and all modified columns of the table.

We aim to improve NOT NULL constraint checks in MERGE-statement by checking only changed rows for NULL on the fly. This will be done for a MERGE-statement unless it contains both UPDATE and DELETE-clause.

-- This MERGE statement will get a quick NOT NULL check
MERGE INTO target USING source ON target.t_col = source.s_col
WHEN MATCHED THEN UPDATE SET target.t_updatecol = source.s_updatecol

Limitation

Even with this update a MERGE-statement with both UPDATE and DELETE clauses will still perform a complete (expensive) full NOT NULL check on all rows of the table.

-- still needs full NOT NULL check
MERGE INTO target USING source ON target.t_col = source.s_col
WHEN MATCHED THEN UPDATE SET target.t_updatecol = source.s_updatecol
     DELETE WHERE source.s_checkcol=42

Changed behavior

A merge statement with UPDATE-clause that sets a column to NULL and also to another value could get a constraint-violation-exception instead of a inconsistent-update-exception with this change.