7

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.
2
  • Perhaps the fact that the error message is exactly the same as the one in your last example, where the calculated column actually references a different table's column, hints at the way window functions are implemented internally. I mean maybe on some very low level they are indeed represented as a separate rowset from the table they are applied to, even though syntactically nothing suggests that. Not that this would be helpful as an answer without official confirmation, even if it was true. Such an interesting discovery, thanks for sharing.
    – Andriy M
    May 8, 2023 at 0:10
  • Yes, it appears that might be the case, but that might be a bug. That last example with the join is also undocumented, so I'm still none the wiser. May 8, 2023 at 0:12

1 Answer 1

4

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;
3
  • How interesting, didn't think of using deleted. Doesn't explain why the first example OUTPUT inserted.SomeValue works then (I would have thought it's a parsing-level error in the syntax, rather than a runtime error, so Compute Scalar optimizations should be irrelevant), and it does seem to be missing from the docs. May 8, 2023 at 15:45
  • OUTPUT inserted.SomeValue is just treated the same as OUTPUT inserted.value + 123 - which is a valid thing to do. But it wouldn't be valid to do OUTPUT SUM(inserted.value) OVER (PARTITION BY inserted.SomeId) May 8, 2023 at 15:52
  • It's interesting that in the case of OUTPUT inserted.SomeValue you don't get the error which is a compile-time parsing error (proven by sp_describe_first_result_set) even though the optimization you mention seems like it should need to compile a plan, which only happens at a later stage. There should be no logical reason why a syntax error would depend on compiler optimizations, I can't think of anywhere else this happens. So if anything it sounds as if that case is a bug that it doesn't throw an error. May 8, 2023 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.