使用select和order by的tsql字符串concat与by子句中的函数不兼容？

Consider the following tsql...

考虑以下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...

我在mssql 2000和2005上尝试过它,并且在按顺序使用函数时都不会连接字符串。在2005年,for xml路径('')确实有效。输出是......

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

3 个解决方案

#1

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

看起来这是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."

“ANSI SQL-92规范要求ORDER BY子句引用的任何列都匹配由SELECT列表中的列定义的结果集。当表达式应用于ORDER BY子句的成员时,该结果列是未在SELECT列表中公开,导致未定义的行为。“

#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'前面有空格,没有尾随空格):

foo

I guess this is some obscure little bug?!

我想这是一个不起眼的小虫子?!

#1