MERGE query plans allow FK and CHECK constraint violations - by Paul White NZ

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


8
0
Sign in
to vote
ID 699055 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 11/4/2011 4:35:30 AM
Access Restriction Public

Description

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:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2011/10/17/trusted-foreign-keys-allow-orphans-reject-valid-child-rows.aspx

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.

Thanks,

Paul White
Sign in to post a comment.
Posted by Paul White NZ on 11/8/2011 at 5:16 PM
Hi Eric,

I was in two minds whether to submit this or not, but decided in the end it was better to make sure you guys were at least aware of the issue in case it had been missed in testing, or might have other side-effects that I haven't encountered yet.

Thanks anyway :)
Posted by Eric [MSFT] on 11/8/2011 at 10:59 AM
Dear Paul,

Thanks for the feedback. We'll consider fixing this, probably in a SQL Server 2012 service pack. As you observed, this is not a very realistic case, so that may impact our decision whether to back-port the fix.

Best regards,
Eric Hanson
Program Manager
SQL Server Query Processing