Expression in ORDER BY clause causes self-referencing variables in the SELECT clause to evaluate only once rather than once per row by Country Dave

Closed

as Won't Fix Help for as Won't Fix

Type:	Bug
ID:	126129
Opened:	3/7/2006 3:18:45 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

When an ORDER BY clause includes a scalar expression, self-referencing variables in the SELECT clause (e.g. @i = @i + 1) evaluate only once per statement execution rather than once per row. This happens with table variables as well as database tables. My example is simplistic but does a good job of showing the bug. I actually have a production-level need to be building a string that relys on the ORDER BY clause to make the string correct.

Details (expand)

English

SQL Server 2005 - Enterprise Edition (32)

SQL Engine

Windows Server 2003

US English

DECLARE @temp table([id] int);
INSERT INTO @temp VALUES(1);
INSERT INTO @temp VALUES(2);
INSERT INTO @temp VALUES(3);

DECLARE @i int;

SET @i = 0;

SELECT
@i = @i + 1
FROM
@temp
ORDER BY
([id] + 1);

PRINT @i;

@i = 1

@i = 3

0 attachments

Posted by Erland Sommarskog on 3/11/2006 at 2:58 PM

Please look at
http://support.microsoft.com/default.aspx?scid=287515

It has not been updated to say that it applies to SQL 2005, but I have not heard anything of that the result of a query like this has become defined.

However, SQL 2005 does provide a way of doing what you want, although it is a bit obscure and uses XQuery. Here is a sample that I got from one of the XML developers:

select CustomerID,
         substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
         -- strip the last ',' from the list
from
    Customers c cross apply
    (select convert(nvarchar(30), OrderID) + ',' as [text()]
     from Orders o
     where o.CustomerID = c.CustomerID
     order by o.OrderID
     for xml path('')) as Dummy(OrdIdList)
go

Posted by Microsoft on 3/15/2006 at 12:28 PM

Thanks Dave for reporting this issue. Also, thanks Erland for a good analysis and a relevant KB reference. The results of an assignment in the SELECT list do indeed depend on what plan the optimizer chooses to execute the query, so technically speaking, the results are undefined. Specifically in the presence of ORDER BY, if the Project operator that computes @i+1 is located in the plan below the Sort that implements ORDER BY, the assignment is postponed and the results of the query become very unintuitive. This behavior did not change between SQL2000 and SQL2005, and it is not feasible to fix in a SQL2005 service pack. However, I understand that this behavior is unexpected and confusing, and I'll file this as a bug to change the behavior in a future version of the product.

Eugene Zabokritski, SQL Server Engine