OUTPUT clause with window functions

Question

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 join inside a subquery is also not allowed, so most likely it is an undocumented restriction.

UPDATE subT1
SET flag = 1
OUTPUT inserted.Sum
FROM (
    SELECT t1.*,
      Sum = t2.value
    FROM t1
    JOIN t2 ON t2.SomeId = t1.SomeId
) 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.

score 3 · Accepted Answer · answered Sep 23 '24 at 06:30

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

An (almost) undocumented and incompletely implemented restriction.

Prior to the SQL Server 2012 release this kind of syntax was allowed but the optimizer wasn't set up correctly internally to handle it. This could cause incorrect results or unhandled internal exceptions.

One specific case was fixed for 2012, but the others have not been addressed and the blocked syntax has not been extended to be comprehensive.

My original bug report (archive link because Connect was retired)

Response from Umachandar Jayachandran of the SQL Programmability Team (emphasis added):

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.

Original repro:

-- This one is fixed
DECLARE @T1 TABLE (col1 integer NULL);
INSERT @T1 
    (col1)
VALUES
    (1), (1), (2), (2);
-- The Compute Scalar after the Table Delete
-- contains a naked COUNT() that always
-- evaluates to zero
WITH 
    D AS 
    (
        SELECT 
            col1, 
            cnt = COUNT() OVER () 
        FROM @T1
    )
DELETE D
OUTPUT DELETED.*
WHERE D.col1 = 2;
GO
-- Not fixed
DECLARE @T1 TABLE (col1 integer NULL);
INSERT @T1 
    (col1)
VALUES
    (1), (1), (2), (2);
-- Any expression or scalar function on a window/ranking function
-- produces an Access Violation (also try + 0 instead of ABS)
WITH 
    D AS 
    (
        SELECT 
            col1, 
            rn = ABS(ROW_NUMBER() OVER (ORDER BY col1)) 
        FROM @T1
    )
DELETE D
OUTPUT DELETED.*;

Note: Not all cases with problematic behaviour were successfully blocked by this change (recent example), which only applies to expressions.

Meantime, the safest thing is not to reference columns based on window functions in the OUTPUT clause. You may or may not get correct results depending on the query and plan shape.

OUTPUT clause with window functions

1 Answers1