Home Dashboard Directory Help
Search

MERGE: Compiler should give warning for "unwise" ON clauses by TechVsLife2


Status: 

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


15
2
Sign in
to vote
Type: Suggestion
ID: 564676
Opened: 6/1/2010 6:27:16 PM
Access Restriction: Public
0
Workaround(s)
view

Description

Currently BOL warns that certain ON clauses in the MERGE statement, while legal, can produce what it calls "incorrect" results, i.e. results that are clearly unexpected based on the syntax. The compiler should issue a warning in such cases, at least whenever it can detect the problem. (In some of the cases it is simply a matter of moving an extra ON clause restriction, such as comparison to a constant, to the WHEN section.)

See BOL here:
http://msdn.microsoft.com/en-us/library/bb510625.aspx
Do not attempt to improve query performance by filtering out rows in the target table in the ON clause, such as by specifying AND NOT target_table.column_x = value. Doing so may return unexpected and incorrect results.

AND here:
http://msdn.microsoft.com/en-us/library/cc879317.aspx
Do not include comparisons to other values such as a constant.
...
This method [i.e. using CTEs to filter source or target rows in a MERGE] is similar to specifying additional search criteria in the ON clause and may produce incorrect results. We recommend that you avoid using this method or test thoroughly before implementing it.

AND here:
http://msdn.microsoft.com/en-us/library/bb522522.aspx
Because the additional search condition is not required to determine source and target matching, the insert and delete actions are applied to all input rows. In effect, the filtering condition EmployeeName LIKE 'S%' is ignored. When the statement is run, the output of the inserted and deleted tables shows that two rows are incorrectly modified.
Details
Sign in to post a comment.
Posted by TechVsLife2 on 6/22/2011 at 5:25 PM
Thanks, though I don't know who Mike is.
Posted by Microsoft on 3/18/2011 at 10:47 AM
Hello Mike,

Thank you for submitting this suggestion, but we're trying to clean house and remove items we feel we will likely not address given their priority relative to other items in our queue. We believe it is unlikely that we will address this suggestion, and so we are closing it as “won’t fix”.

This cleaning will help us focus on the high-priority items that we feel need to get done, and we hope that it help provide better clarity to you about the issues we will (and won't) address.

--
Umachandar, SQL Programmability Team
Posted by Microsoft on 6/2/2010 at 11:37 AM
Hi,
Thanks for your feedback. We will consider it for a future version of SQL Server.

--
Umachandar, SQL Programmability Team
Sign in to post a workaround.