Wrong Results / Unhandled Exception using DELETED in the OUTPUT clause - by Paul White NZ

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


9
0
Sign in
to vote
ID 682645 Comments
Status Closed Workarounds
Type Bug Repros 4
Opened 8/2/2011 6:26:28 AM
Access Restriction Public

Description

The parser currently allows us to reference expressions with the DELETED prefix in an OUTPUT clause.  This should not be allowed: it causes wrong results, or an unhandled exception in the optimizer - which terminates the connection.

Two examples in the Details section below.  There are any number of 'fun' ways to break things using this flaw.  Raised against CTP 3 because the problem is not fixed there - it applies to all current version of SQL Server as far as I can tell.

For more information see:

http://sqlblog.com/blogs/piotr_rodak/archive/2011/07/31/output-clause-and-windowing-functions.aspx
Sign in to post a comment.
Posted by xor88 on 10/18/2014 at 10:04 AM
This issue is unfixed as of SQL Server 2014 CU3: http://dba.stackexchange.com/questions/80558/strange-query-results-for-delete-with-row-number (short repro code in the linked question).
Posted by Paul White NZ on 8/18/2011 at 6:26 PM
Thank you for the detailed feedback UC, it is much appreciated. Look forward to testing the changes for myself in due course.

Paul
Posted by Microsoft on 8/18/2011 at 2:38 PM
Hello Paul,
We have now fixed the issue in SQL Server "Denali". We will consider backporting it to older versions of SQL Server.
The COUNT(*) OVER case has been fixed to provide the current SQL Server behavior i.e., the function gets evaluated on the DELETE stream. The case where the window function is used in an expression, we will now throw an error saying it is unsupported. This is similar error that is thrown when you have such expression in the CTE with INSERT statment for example.
Long term, we need to actually fix the behavior of OUTPUT clause to match that of the ANSI SQL standard which will result in change of results. So we will look at the correct semantics for a future version of SQL Server since there might be apps that rely on the current behavior.

--
Umachandar, SQL Programmability Team
Posted by Microsoft on 8/2/2011 at 5:51 PM
Hello Paul,
Thanks for reporting this issue. We will take a look at it and see what we can do about it. Maybe some restriction on the columns that can be referenced in the OUTPUT clasue is in order here.

--
Umachandar, SQL Programmability Team
Posted by Piotr Rodak on 8/2/2011 at 7:35 AM
The behavior occurs on SQL Server 2008 and 2008 R2 as well.