Alex Kuznetsov recently showed how an INSERT performed using MERGE syntax could result in foreign key violations - both where invalid rows are allowed and valid rows are rejected. The full description and reproduction scripts can be found here:
This bug is mostly fixed in SQL Server 2012 CTP3; the purpose of this report is to:
(a) Request that this fix be ported to SQL Server 2008 R2; and
(b) Show that a residual of this bug's behaviour still exists in SQL Server 2012
The incomplete fix in 2012 still allows a CHECK constraint violation by the same underlying mechanism. This only applies to table variables (not temporary or ' normal' tables):
DECLARE @Bug TABLE
id INTEGER PRIMARY KEY,
data AS 'X' PERSISTED,
CHECK (data = 'A')
MERGE @Bug AS b USING (VALUES(1)) AS u (id) ON u.id = b.id
WHEN NOT MATCHED THEN INSERT (id) VALUES (u.id)
OUTPUT INSERTED.data, INSERTED.id;
The equivalent INSERT statement plan contains an Assert and fails with the expected constraint violation. The MERGE plan does not contain the required Assert operator; it is incorrectly optimized away based on the constant nature of the underlying 'data' column.
This contrived example is to demonstrate the small oversight in the current fix. I am not suggesting this pattern (persisted constant value column + incompatible CHECK constraint) exists or would be useful in practice.
The OUTPUT 'id' column is emitted by the Merge operator, but the 'data' column is not - it is replaced later as a Compute Scalar ([Expr1011] = Scalar Operator('X')). This optimization seems to save little and causes both the FK violation bugs and the CHECK violation.
Please consider implementing a comprehensive fix and back-porting to at least R2.