Home Dashboard Directory Help
Search

MERGE evaluates filtered index per row, not post operation, which causes filtered index violation by Vladimir Moldovanenko


Status: 

Closed
 as Won't Fix Help for as Won't Fix


3
0
Sign in
to vote
Type: Bug
ID: 766165
Opened: 10/4/2012 12:29:42 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

with filtered index in place MERGE fails to evaluate index post operation but seems to evaluate during it, for each row.

Data sets before and after are valid, and index can be created after MERGE, confirming data validity
Details
Sign in to post a comment.
Posted by Vladimir Moldovanenko on 4/16/2013 at 7:01 PM
Why was this closed as Won't Fix? Any explanation?
Posted by Vladimir Moldovanenko on 11/9/2012 at 11:39 AM
I think another connect item of mine is variant of the same underlining issue
https://connect.microsoft.com/SQLServer/feedback/details/596086/merge-statement-bug-when-insert-delete-used-and-filtered-index
Posted by Vladimir Moldovanenko on 11/9/2012 at 11:37 AM
I understand the current behavior but it should not be so. The result is valid before and after the statement and MERGE is supposed to be a set operation. From generic SQL point of view an order in which rows are updated should not matter. And I think you know that well.
However difficult it is, it should be fixed otherwise SQL Server's technical implementation is at odds with generic SQL set operation.
Therefore, I would expect that you would fix this and other cases of this behavior
Thanks
Vladimir
Posted by Microsoft on 11/9/2012 at 11:04 AM
Hi Vladimir,

Thank you so much for taking the time to provide feedback. I spoke to a developer about this issue and here are her comments:

Here's what happened with the merge query in the repro:

destination table d has

1, 1, 0

1, 2, 1

1, 3, 0

source table s has

1, 1, 1

1, 2, 0

d and s merge on the first 2 columns initID and initID2, here's what happened during merge

1, 1, 0 => 1, 1, 1

1, 2, 1 => 1, 2, 0

1, 3, 0 no change

After the 1st row gets updated, there's 2 rows with IsDefault=1 and initID=1, violating the uniqueness enforced by the filtered index. But soon the violation gets fixed when the second row gets updated. So the violation is transient.



This issue is not limited to merge. Executing the following update statement generates the same error:

update Test set IsDefault= case when IsDefault=1 then 0 else 1 end



The problem has to do with the unique key constraint enforced by filtered indices: updating the filtered cololumn can insert or delete rows from filtered indices, causing transient violation of the constraint. Unfortunately, this problem cannot be easily fixed with our current design for DML.


Please let me know if this answers your question,

Rapinder Jawanda
Sr. program manager, DW team.
Posted by Microsoft on 11/9/2012 at 11:04 AM
Hi Vladimir,

Thank you so much for taking the time to provide feedback. I spoke to a developer about this issue and here are her comments:

Here's what happened with the merge query in the repro:

destination table d has

1, 1, 0

1, 2, 1

1, 3, 0

source table s has

1, 1, 1

1, 2, 0

d and s merge on the first 2 columns initID and initID2, here's what happened during merge

1, 1, 0 => 1, 1, 1

1, 2, 1 => 1, 2, 0

1, 3, 0 no change

After the 1st row gets updated, there's 2 rows with IsDefault=1 and initID=1, violating the uniqueness enforced by the filtered index. But soon the violation gets fixed when the second row gets updated. So the violation is transient.



This issue is not limited to merge. Executing the following update statement generates the same error:

update Test set IsDefault= case when IsDefault=1 then 0 else 1 end



The problem has to do with the unique key constraint enforced by filtered indices: updating the filtered cololumn can insert or delete rows from filtered indices, causing transient violation of the constraint. Unfortunately, this problem cannot be easily fixed with our current design for DML.


Please let me know if this answers your question,

Rapinder Jawanda
Sr. program manager, DW team.
Sign in to post a workaround.