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

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.


4
0
Sign in
to vote
ID 766165 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 10/4/2012 12:29:42 PM
Access Restriction Public

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
Sign in to post a comment.
Posted by Vladimir Moldovanenko on 5/27/2015 at 2:22 PM
To Newfangled Old-fashioned Stuff: Oh, I see you saw that one too! :) Cool. I re-tested it with SQL 2014 SP1 and still broken :(
Posted by Vladimir Moldovanenko on 5/27/2015 at 2:19 PM
To Newfangled Old-fashioned Stuff: I see your point about a single value. Thanks for clarifying it.
Yet, there is still an issue, as you have acknowledged and SQL Server trips over it. Also see https://connect.microsoft.com/SQLServer/feedback/details/596086/merge-statement-bug-when-insert-delete-used-and-filtered-index
Posted by Newfangled Old-fashioned Stuff on 5/27/2015 at 7:21 AM
To Vladimir Moldovanenko :
In most cases, I agree, adding prevents the data integrity from being correct; if the filtered column is allowed to have more than one value, this doesn't work.

However, in the particular (narrow) use case I used as an example, where the filtering is on an equality operation, it does result in the desired data integrity, because the only rows in the index have exactly one value for the filtered column, so uniqueness rests solely on the non-filtered column(s) in the index, just as it would if the filtered column.

I.e. adding IsDefault to the unique index changes nothing as far as uniqueness goes AS LONG AS the WHERE filter forces IsDefault to be exactly one value; for instance, always one.
Posted by Vladimir Moldovanenko on 4/23/2015 at 10:33 AM
To Newfangled Old-fashioned Stuff :
Adding does not make sense as it lo longer enforces desired data integrity
Posted by Newfangled Old-fashioned Stuff on 4/23/2015 at 8:59 AM
Also confirmed the "add all columns in the filtering clause to the filtered index itself"workaround (per the same Paul White article Vladimir listed in workarounds):
i.e. use
CREATE UNIQUE NONCLUSTERED INDEX [UIXF_Test_IsDefault] ON dbo.Test(intID ASC, IsDefault ASC) WHERE (IsDefault=(1))
instead of
CREATE UNIQUE NONCLUSTERED INDEX [UIXF_Test_IsDefault] ON dbo.Test(intID ASC) WHERE (IsDefault=(1))

works in SQL 2014 Build 2480 (X64).
Posted by Newfangled Old-fashioned Stuff on 4/23/2015 at 8:46 AM
Confirmed that the bug is still present, AND that the OPTION (QUERYTRACEON 8790) workaround still works in SQL 2014 Build 2480 (X64).
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.