OUTPUT clause with window functions

Is there an undocumented restriction in the OUTPUT clause, or is this a bug?

Given the following table:

CREATE TABLE t1 (SomeId int, flag bit, value int);

I would like to use a calculated value in an UPDATE statement, and then use OUTPUT to output the value. Note that the calculated value is not being used in the SET part, which might have allowed a workaround by outputting the left-hand-side column.

The following works fine, it is a perfectly standard updatable subquery (derived table).

UPDATE subT1
SET flag = 1
OUTPUT inserted.SomeValue
FROM (
    SELECT *,
      SomeValue = t1.value + 123
    FROM t1
) subT1;

However using a window function gets a weird error:

UPDATE subT1
SET flag = 1
OUTPUT inserted.Sum
FROM (
    SELECT *,
      Sum = SUM(t1.value) OVER (PARTITION BY t1.SomeId)
    FROM t1
) subT1;

Msg 404 Level 16 State 1 Line 3
The column reference "inserted.Sum" is not allowed because it refers to a base table that is not being modified in this statement.

Which doesn't make sense as we already established that calculated columns can be referred to using inserted.

OK let's try using OUTPUT subT1.Sum anyway, rather than inserted.Sum:

Msg 4104 Level 16 State 1 Line 3
The multi-part identifier "subT1.Sum" could not be bound.

which makes sense, since that table reference has been modified, and the docs say:

If the table being modified is also specified in the FROM clause, any reference to columns in that table must be qualified with the INSERTED or DELETED prefix.

Meanwhile, if I use a join on a second table:

CREATE TABLE t2 (SomeId int, flag bit, value int);

it works fine

UPDATE t1
SET flag = 1
OUTPUT subT2.Sum
FROM t1
JOIN (
    SELECT t2.*,
      Sum = SUM(t2.value) OVER (PARTITION BY t2.SomeId)
    FROM t2
) subT2 ON subT2.SomeId = t1.SomeId;

db<>fiddle

So is the fact that a window function throws that error a bug, or an undocumented restriction?

Having said all that, a

I don't see this explicitly covered in the documentation but OUTPUT deleted.Sum does work.

This does make some sense though as the SUM is calculated on the values "pre-update" rather than "post-update"

i.e. the below returns a SUM of 2 rather than 0

CREATE TABLE t1 (SomeId int, flag bit, value int);

INSERT T1 values (1,1,1), (1,1,1);

UPDATE subT1
SET flag = 1, value = 0
OUTPUT deleted.Sum AS [SUM(value)],
       deleted.value AS [deleted.value], 
       inserted.value AS [inserted.value]
FROM (
    SELECT *,
      Sum = SUM(t1.value) OVER (PARTITION BY t1.SomeId)
    FROM t1
) subT1;

DROP TABLE t1

SUM(value)
deleted.value
inserted.value

2
1
0

2
1
0

db<>fiddle

For the simpler expression of SomeValue = t1.value + 123 it is able to calculate inserted.SomeValue distinct from deleted.SomeValue by adding a compute scalar on the output stream but presumably window functions would be too complex to do this too.

i.e. the below ...

CREATE TABLE t1 (SomeId int, flag bit, value int);

INSERT T1 values (1,1,1), (1,1,1);

UPDATE subT1
SET flag = 1, 
     value = 999
OUTPUT deleted.SomeValue AS [deleted.SomeValue],
       inserted.SomeValue AS [inserted.SomeValue]
FROM (
    SELECT *,
      SomeValue = t1.value + 123
    FROM t1
) subT1;

DROP TABLE t1

Returns

deleted.SomeValue
inserted.SomeValue

124
1122

124
1122

And the execution plan is the same as

UPDATE t1
SET flag = 1, 
     value = 999
OUTPUT deleted.value  + 123 AS [deleted.SomeValue],
       inserted.value  + 123 AS [inserted.SomeValue]
FROM t1;

StackExchange Database Administrators Q#326842, answer score: 4