tsql string concat with select and order by does not work with function in order by clause?

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've tried it on mssql 2000 and 2005 and both do not concat the string when using a function in the order by. On 2005, the for xml path('') does work. The output is...

bar
blah
foo

barblahfoo

foo --nothing concatenated?

barblahfoo

I can't find where this is documented. Can someone shed some light on why this doesn't work?

EDIT:

Here are the actual execution plans. Obviously the sort and compute scalar are not in the same order...

Answer 1

It would appear that this is a known issue with Aggregate Concatenation Queries.

From the link:

"The ANSI SQL-92 specification requires that any column referenced by an ORDER BY clause match the result set, defined by the columns present in the SELECT list. When an expression is applied to a member of an ORDER BY clause, that resulting column is not exposed in the SELECT list, resulting in undefined behavior."