Select the product you need help with

FIX: Incorrect results if you use parentheses around SELECT in a statement that has a common table expression in SQL Server 2008

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

SYMPTOMS

On a computer that is running Microsoft SQL Server 2008, consider the following scenarios.

Scenario 1

You specify a common table expression (CTE) by using the WITH statement. For example, you run the following query.

WITH common_table_expression AS 

(SELECT *
 FROM sys.objects)

(SELECT *

 FROM common_table_expression)

Then, you run this query.

SELECT *

FROM sys.dm_exec_query_stats

WHERE statement_start_offset > statement_end_offset

AND

statement_end_offset <> -1

In this scenario, the cached query plan for this query stores incorrect statement_start_offset and statement_end_offset values. Specifically, you notice the statement_end_offset value is smaller than the statement_start_offset value when you run sys.dm_exec_query_stats dynamic management view (DMV).

Because of this problem, a performance report may fail when the report assumes that the statement_start_offset value is always smaller than the statement_stop_offset value.

Scenario 2

You specify a common table expression by using the WITH statement, and you use the RECOMPILE option. For example, you create a function in SQL Server 2008 by using the following script.

CREATE FUNCTION function1() RETURNS int AS
BEGIN
DECLARE @x int
SET @x=1;
WITH common_table_expression AS 

(SELECT @x AS column1) 

(SELECT @x=column1 
FROM common_table_expression) 

OPTION(RECOMPILE)
RETURN @x
END

When you run a “select dbo.function1()” query, you receive the following error message:

A severe error occurred on the current command. The results, if any, should be discarded.

Additionally, this error can occur without the RECOMPILE option if the SQL server is experiencing heavy server traffic.

Scenario 3

You specify a common table expression by using the WITH statement. In the WITH statement, you specify a table that does not exist. For example, you run the following query.

WITH computed_table (id) AS
(SELECT id 
FROM this_table_does_not_exist) 
(SELECT id 
FROM dbo.computed_table) 
GO

When you run this query, you do not receive an error for the missing table.

Back to the top | Give Feedback

CAUSE

These problems occur because of a malfunctioning processing of a SELECT statement that follows a WITH statement. The WITH statement uses the following syntax.

WITH common_table_expression AS

(CTE_query_definition)

This statement then creates a result set that is assigned to the common table expression name that was specified. Then, you follow this statement with a SELECT statement. If this second statement is enclosed in parentheses, and then you try to perform an operation on the results or you try to run a sys.dm_exec_query_stats dynamic management view, you receive incorrect results or an error.

Back to the top | Give Feedback

RESOLUTION

Service pack information

To resolve this problem, obtain the latest service pack for SQL Server 2008. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

968382

(http://support.microsoft.com/kb/968382/ )

How to obtain the latest service pack for SQL Server 2008

Back to the top | Give Feedback

WORKAROUND

To work around these problems, you have to remove the parentheses from the statement that follows the common table expression. For example, consider the following WITH statement that uses such parentheses.

WITH common_table_expression AS 

(SELECT *
FROM sys.objects)

(SELECT *

 FROM common_table_expression)

To work around these problems, change to the following WITH statement that does not use these parentheses.

WITH common_table_expression AS 

(SELECT *
FROM sys.objects)

SELECT *

 FROM common_table_expressio

Back to the top | Give Feedback

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

This problem was first corrected in SQL Server 2008 Service Pack 2 (SP2).

Back to the top | Give Feedback