Home Dashboard Directory Help
Search

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


Status: 

Active


5
0
Sign in
to vote
Type: Bug
ID: 699055
Opened: 11/4/2011 4:35:30 AM
Access Restriction: Public
0
Workaround(s)
view
1
User(s) can reproduce this bug

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
Details
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 Microsoft 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
Sign in to post a workaround.