MERGE Incorrectly Reports Unique Key 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.


26
0
Sign in
to vote
ID 773895 Comments
Status Closed Workarounds
Type Bug Repros 10
Opened 12/9/2012 10:15:33 AM
Access Restriction Public

Description

A MERGE statement can fail, and incorrectly report a unique key violation when:

*  The target table uses a unique filtered index; and
*  No key column of the filtered index is updated; and
*  A column from the filtering condition is updated; and
*  Transient key violations are possible

The optimizer incorrectly chooses a narrow update plan where a wide plan with split/sort/collapse is required to avoid transient nonclustered index key violations.

Forcing a wide update plan with TF 8790 (or by adding all columns referenced in the index filter predicates to the index *keys*) avoids this problem.

There is a reproduction script in the details section below, and a fuller discussion at http://sqlblog.com/blogs/paul_white/archive/2012/12/10/merge-bug-with-filtered-indexes.aspx
Sign in to post a comment.
Posted by Vaccanoll on 4/23/2015 at 1:42 PM
This is absurd. How can you close something like this as "Won't Fix" and not give an explanation.....

Amateur stuff here folks.
Posted by Newfangled Old-fashioned Stuff on 4/23/2015 at 7:06 AM
Microsoft - please explain WHY this is a "won't fix" bug when there's code already written that already forces a wide update (trace flag 8790) which could be used by the optimizer when it sees this particular combination of column types in a MERGE. You could even check for the conditions, then simply insert the trace flag into the query and re-generate a plan that would work - it's not elegant, but it shouldn't take all that long to implement.

Everyone else: no surprise, but this bug is still present in SQL 2014 build 2480 (X64) as well as SQL 2012 build 5522 (X64).
Posted by Philip Lewis on 2/13/2015 at 6:27 AM
What a waste of my time ... the standard answer is always the same 'BUG ... closed, won't fix'

I honestly don't think they care about product quality, they are obsessed with "new and shiny" functionality.
Posted by Wiggity Grub on 6/4/2014 at 5:00 AM
Four more months... Anything we can do other than avoid using the MERGE functionality?
Posted by DWalker on 2/22/2014 at 1:42 PM
"Microsoft will get back to you." OK, now it's 13 months later. Any word from them?
Posted by Microsoft on 1/4/2013 at 4:41 PM
Hi Paul White NZ ,
    
thanks for taking the time to share your feedback, this is really important to us.
We will investigate the issue and get back to you.

Best regards
Jean-Yves Devant 

Program Manager Servicing and Lifecycle Experience of High Availability Technologies in SQL Server