Tsql concat string with choice and order doesn't work with function in order by condition?

Question

Consider the following tsql ...

create function dbo.wtfunc(@s varchar(50)) returns varchar(10) begin return left(@s, 2); end GO select t.* into #test from ( select 'blah' as s union select 'foo' union select 'bar' ) t select * from #test; declare @s varchar(100); set @s = ''; select @s = @s + s from #test order by s; select @s; set @s = ''; select @s = @s + s from #test order by dbo.wtfunc(s); select @s; /* 2005 only*/ select cast((select s+'' from #test order by dbo.wtfunc(s) for xml path('')) as varchar(100)) drop function dbo.wtfunc; drop table #test; 

I tried this on mssql 2000 and 2005 and both do not concatenate the string when using the function in order. In 2005, the for xml ('') path works. Output signal ...

 bar blah foo barblahfoo foo --nothing concatenated? barblahfoo 

I can not find where this is documented. Can someone shed some light on why this is not working?

EDIT:

Here are the actual implementation plans. Obviously, sorting and computational scalar are not in the same order ...

alt text http://i41.tinypic.com/2d6pht3.jpg alt text http://i41.tinypic.com/w2og48.png

Answer 1

This seems to be a known issue with concatenation aggregation queries .

From the link:

“The ANSI SQL-92 specification requires that any column referenced by an ORDER BY clause matches a result set defined by columns in the SELECT list. When an expression is applied to a member of an ORDER BY clause, the resulting column does not appear in the SELECT list, which leads to undefined behavior.

Answer 2

You can do this using a computed column, for example:

 DROP TABLE dbo.temp; CREATE TABLE dbo.temp ( s varchar(20) ,t AS REVERSE(s) ); INSERT INTO dbo.temp (s) VALUES ('foo'); INSERT INTO dbo.temp (s) VALUES ('bar'); INSERT INTO dbo.temp (s) VALUES ('baz'); INSERT INTO dbo.temp (s) VALUES ('blah'); GO -- Using the function directly doesn't work: DECLARE @s varchar(2000); SELECT s, REVERSE(s) FROM dbo.temp ORDER BY REVERSE(s); SET @s = ''; SELECT @s = @s + s FROM dbo.temp ORDER BY REVERSE(s); SELECT @s; GO -- Hiding the function in a computed column works: DECLARE @s varchar(2000); SELECT s, t FROM dbo.temp ORDER BY t; SET @s = ''; SELECT @s = @s + s FROM dbo.temp ORDER BY t; SELECT @s; GO 

Answer 3

I don't know if this is really useful, but when I try this:

 set @s = ' '; select @s = @s + s from #test order by dbo.wtfunc(s); select @s AS myTest 

I get this (note that there are spaces preceding "foo" and not a single trailing):

  foo 

I think this is some strange little mistake ?!