tsql string concat with select and order by does n

问题:

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...

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

回答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."

回答2:

You can do this by using a computed column, for instance:

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

回答3:

I don't know if this is at all helpful, 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 there are spaces prepending the 'foo' and none trailing):

  foo

I guess this is some obscure little bug?!