Error in SQL Server 2012 OUTPUT clause

Question

Given the following SQL code against the AdventureWorks Database:

begin transaction;

drop trigger Sales.iduSalesOrderDetail;
alter table Sales.SalesOrderDetail
    add LineNumber int null;
go

with sod as (
    select
        SalesOrderID, 
            /* other cols */
        LineNumber,
        NewLineNumber = row_number() over 
            (partition by SalesOrderID order by SalesOrderDetailID) + 1
    from Sales.SalesOrderDetail
    where SalesOrderID <= 44000)
update sod
set LineNumber = NewLineNumber,
    ModifiedDate = getdate()
output
    inserted.SalesOrderID, 
    /* other cols */
    inserted.LineNumber;

select
    SalesOrderID, 
    /* other cols */
    LineNumber
from Sales.SalesOrderDetail
where SalesOrderID <= 44000;

rollback transaction;

When running this code on SQL Server 2008R2, it runs fine; that is to say, it updates the data in Sales.SalesOrderDetail and outputs the results, which match the data from the following SELECT statement.

However, when running this code on SQL Server 2012, I get the message:

Msg 4196, Level 16, State 1, Line 2
Column 'deleted.NewLineNumber' cannot be referenced in the OUTPUT clause because the column definition contains an expression using a window function.

This is odd because I am not referencing deleted.NewLineNumber in any way. I have attempted removing various OUTPUT columns and get the same result. The only two ways I have found to stop the error have been to

• make the NewLineNumber column be exactly the result of the row_number() (that is to say: NewLineNumber = row_number() over (...)); or
• remove the OUTPUT clause altogether.

Is this possibly a bug in how SQL Server is processing the expressions in the CTE? Has anyone else run into this?

I'm working on possible workarounds, but I am curious about this.

Answer 1

This is a bug in SQL Server 2012, and will be fixed in SP2:

http://connect.microsoft.com/SQLServer/feedback/details/779077/output-clause-fails-when-doing-update-using-cte

A workaround is to remove the + 1 from the ROW_NUMBER() calculation; this spurious error message goes away. Rewrite this portion:

  NewLineNumber = row_number() over 
    (partition by SalesOrderID order by SalesOrderDetailID)
    ---------------- remove the +1 here --------------------^^^
  from Sales.SalesOrderDetail
  where SalesOrderID <= 44000)
update sod 
  set LineNumber = NewLineNumber + 1,
  ------- and put it here -------^^^