Select the product you need help with

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

Article ID: 287515 - View products that this article applies to.

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 "More Information" section of this article.

Back to the top | Give Feedback

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.

Back to the top | Give Feedback

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.

Back to the top | Give Feedback

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

Back to the top | Give Feedback