Amazing variable reassignment behavior for each row in the selected

Question

This is the simplest example with which I could reproduce the problem. In fact, it looks a little far-fetched, but carry me.

declare @t table(number int) insert into @t values (1), (2) declare @sum bigint = 0 select @sum = @sum + number from (select top 2 number from @t order by number) subquery order by number desc select @sum 

Here's the query in the data explorer .

I expect this to return 3, the sum of the values ​​in the @t table. Instead, it returns 1.

Doing any of the following will return request 3 correctly:

• make @t.number and @sum are of the same type (by creating @sum a int or @t.number a bigint ).
• delete external order by
• delete internal order by
• doing as order by sorting in one direction, adding desc to the inside or removing it from the outside
• delete a subquery (i.e. select from @t )

None of these things amazes me as something that should change the behavior of this request.

Swapping sort orders (descent in the subquery, increasing from the outside) will force the query to return 2 instead of 1.

A similar situation occurs with strings instead of numbers, so this is not limited to int and bigint .

This happens with both SQL Server 2014 and 2016, or, to be precise,

 Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 10586: ) 

and

 Microsoft SQL Server 2016 (RTM-CU1) (KB3164674) - 13.0.2149.0 (X64) Jul 11 2016 22:05:22 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) 

(the latter is a data explorer).

What's happening?

Answer 1

The answer seems to be that you / relied on undocumented behavior that changed on Sql Server 2012.

In the documentation:

• https://msdn.microsoft.com/en-us/library/ms187330.aspx

SELECT @local_variable is usually used to return a single value to a variable. However, when an expression is a column name, it can return multiple values. If the SELECT statement returns more than one value, the last value returned is assigned to the variable.

It is not documented what happens if the target variable (to be assigned) is part of the original expression. This behavior seems to have changed. In earlier versions, the variable will be assigned once for each row, but this is no longer similar.

This is most noticeable for many functions in which the "group concat" trick stops working:

 SELECT @sentence = @sentence + ' ' + word from SENTENCE_WORDS order by position 

They are usually replaced by the xml concat trick.

 set @sentence = ( select word as "text()", ' ' as "text()" from SENTENCE_WORDS order by position for xml path(''), root('root'), type ).value('(/root)[1]', 'nvarchar(max)') 

Answer 2

Delete the second ORDER BY (Ie "desc order").

You are using the undocumented T-SQL function (I suppose this is called ROW concatenation?), Which is not guaranteed to work in future versions of SQL. This is a bit hacked but very useful by anyone! As you discovered, it breaks when you use the ORDER BY clause. This is a known issue using Row concatenation.