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

This article was previously published under Q287515
This article has been archived. It is offered "as is" and will no longer be updated.
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.
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.
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.
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 T1GOCREATE TABLE T1(  C1  NCHAR(1)  )SET NOCOUNT ONINSERT 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 C1SELECT @Str1 = @Str1 + C1 FROM T1 ORDER BY LTRIM( RTRIM( C1 ) )SELECT @Str2 = @Str2 + LTRIM( RTRIM( C1 ) ) FROM T1 ORDER BY C1SELECT @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 <> @Str2BEGIN   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 OFFENDSET NOCOUNT OFFDROP TABLE T1				
undefined spec ANSI

Article ID: 287515 - Last Review: 12/06/2015 00:18:41 - Revision: 3.2

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 7.0 Standard Edition

  • kbnosurvey kbarchive kbcodesnippet kbprb KB287515