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

Article translations Article translations
Article ID: 2322209 - View products that this article applies to.
Expand all | Collapse all

On This Page

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.

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.

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 How to obtain the latest service pack for SQL Server 2008

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

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).


MORE INFORMATION

For more information about how to use the WITH statement with a common table expression, visit the following MSDN webpage:
WITH common_table_expression (Transact-SQL)
For more information about sys.dm_exec_query_stats, visit the following MSDN webpage:
sys.dm_exec_query_stats (Transact-SQL)

Properties

Article ID: 2322209 - Last Review: September 21, 2010 - Revision: 3.0
APPLIES TO
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Workgroup
Keywords: 
kbqfe kbfix kbexpertiseadvanced kbsurveynew kbtshoot kbquery KB2322209

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com