Microsoft KB Archive/287515

= PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location =

Article ID: 287515

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q287515



SYMPTOMS
You may encounter unexpected results when you apply any operators or expressions to the ORDER BY clause of aggregate concatenation queries. The results of aggregate concatenation queries in Microsoft SQL Server depend upon whether or not Transact-SQL expressions are applied to the columns in the ORDER BY clause of the query. For more information or to see an example of a aggregate concatenation query and the behavior exhibited, refer to the &quot;More Information&quot; section of this article.



CAUSE
The correct behavior for an aggregate concatenation query is undefined.

An examination of the SHOWPLAN output of the query reveals that the SQL Server query processor builds an different execution plan when expressions are applied to columns in the query's ORDER BY clause, than when those same expressions are applied to columns in the query's SELECT list. The decision made by the query processor is based on the cost of possible execution plans.

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.

Additionally, the ANSI specifications regarding how an ORDER BY clause is to be applied state that the effect of the ORDER BY should be the same as if you take the entire result set produced by the SELECT list, and then perform the ordering of the table based on those columns in the SELECT list.



WORKAROUND
In order to achieve the expected results from an aggregate concatenation query, apply any Transact-SQL function or expression to the columns in the SELECT list rather than in the ORDER BY clause.



MORE INFORMATION
An aggregate concatenation query is a query that combines the values of multiple rows into one row.

Steps to Reproduce Behavior
Use the following Transact-SQL script to reproduce the behavior.

Note the application of the LTRIM and RTRIM functions to the [C1] column in the ORDER BY clause versus the SELECT list. IF EXISTS( SELECT * FROM sysobjects WHERE name = 'T1' ) DROP TABLE T1 GO

CREATE TABLE T1( C1  NCHAR(1)  )

SET NOCOUNT ON

INSERT T1 VALUES( 'A' ) INSERT T1 VALUES( 'B' )

DECLARE @Str0 VARCHAR(4) DECLARE @Str1 VARCHAR(4) DECLARE @Str2 VARCHAR(4)

SET @Str0 = '' SET @Str1 = '' SET @Str2 = ''

SELECT @Str0 = @Str0 + C1 FROM T1 ORDER BY C1 SELECT @Str1 = @Str1 + C1 FROM T1 ORDER BY LTRIM( RTRIM( C1 ) ) SELECT @Str2 = @Str2 + LTRIM( RTRIM( C1 ) ) FROM T1 ORDER BY C1

SELECT @Str0 'No functions applied to column.' SELECT @Str1 'LTRIM and RTRIM applied to ORDER BY clause.' SELECT @Str2 'SELECT list with LTRIM(RTRIM) (Workaround)'

IF @Str1 <> @Str2 BEGIN PRINT '' PRINT 'Execution plan depends on where the functions are applied:' PRINT '==========================================================' PRINT ''

SET @Str1 = '' SET @Str2 = ''

SET STATISTICS PROFILE ON  SELECT @Str1 = @Str1 + C1 FROM T1 ORDER BY LTRIM( RTRIM( C1 ) ) SELECT @Str2 = @Str2 + LTRIM( RTRIM( C1 ) ) FROM T1 ORDER BY C1  SET STATISTICS PROFILE OFF END

SET NOCOUNT OFF

DROP TABLE T1

Additional query words: undefined spec ANSI

Keywords: kbcodesnippet kbprb KB287515

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.