Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Fixed Help for as Fixed


9
0
Sign in
to vote
Type: Bug
ID: 682645
Opened: 8/2/2011 6:26:28 AM
Access Restriction: Public
0
Workaround(s)
view
4
User(s) can reproduce this bug

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
Details
Sign in to post a comment.
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.
Sign in to post a workaround.