Roles of parentheses in SQL Server SELECT queries?

Question

Hello,

The following query returns no result and no error on SQL Server 2008 (tested on SP1), you can run it against any database, even master:

WITH computed_table (id) AS
(
    SELECT id FROM this_table_does_not_exist
)
(SELECT * FROM computed_table)
UNION
(SELECT * FROM another_table_that_does_not_exists)

On SQL Server 2005, you get an error because the tables do not exist. You get also get an error if you remove some parentheses:

WITH computed_table (id) AS
(
    SELECT id FROM this_table_does_not_exist
)
SELECT * FROM computed_table
UNION
(SELECT * FROM another_table_that_does_not_exists)

The same kind of problems appears with real tables: on some occasions, the query does not return any result, and if you make some some slight changes, like removing a space or a carriage return, it works again...

I think that there may be a problem in the query, because the SELECT enclosed in parentheses may be interpreted as an expression instead of a subquery, as in this page. But that should at least return an error.

Am I missing something?

Edit 26/06/2010: I ran some profiling sessions, with the following results.

For the query above, the sequence of events is:

• Exception (Error: 208, invalid object name)
• SQL:BatchStarting
• SQL:StmtStarting
• SQL:BatchCompleted

For the query without parentheses:

• Exception (Error: 208)
• SQL:BatchStarting
• SQL:StmtStarting
• Exception (Error: 208)
• User Error Message (Invalid object name 'this_table_does_not _exist')
• SQL:BatchCompleted

For a working query:

• SQL:BatchStarting
• SQL:StmtStarting
• Showplan All
• SQL:StmtCompleted
• SQL:BatchCompleted

I also ran one of the queries with real tables that's causing me the same issue. The sequence of events is:

• SQL:BatchStarting
• SQL:StmtStarting
• SQL:BatchCompleted

No early "Exception" => the tables exist. No "SQL:StmtCompleted" => it means that an error occured, I could not see any other reason why this event would not be raised. No "Showplan All" => it means that the error occurs before (or when) the execution plan is computed. It may be caused by the combination of cte and parentheses.

I will raise the issue with Microsoft support next week.

Answer 1

So, I simplified the sql just a bit, as per my earlier comment on the original question.

WITH computed_tabled AS
(
    SELECT id FROM this_table_does_not_existd
)
(SELECT id FROM computed_tabled)

This seems to give us the same behavior. Then I ran a trace. Event classes :

• SQL:Batch Starting
• SQL:Batch Completed
• SQL:StmtStarting
• SQL:StmtCompleted
• Showplan All
• Showplan XML

What I caught was unexpected:

• SQL:BatchStarting
• SQL:StmtStarting
• SQL:BatchCompleted

Note: no SQL:StmtCompleted, no plans. So, next, I go back to the capture settings, and add in every single Event Class under Errors and Warnings. ReRun the query, and what do you know? First caught event is:

Error: 208, Severity: 16, State: 1

Guess what 208 is? But, the client never sees the error.

What I think is happening is that the code in the DBMS is saying - hey, they didn't ask us to return anything or do anything, so why bother? Let's free up some resources for someone more demanding.

So I tried this piece of code:

--asdfasdf 
( SELECT 1 )

Which totally blew my theory away. The parenthesis were NOT being interpreted as an expression. Instead, they were being interpreted as a fully query ( which is to say, the client is asking for something to be returned ) and returned a recordset with 1 column and 1 row. But no plan - probably b/c no plan was needed, as no db objects involved.

So, to mess with my mind some more I tried this:

declare @id as integer;
;
WITH computed_table AS
(
    SELECT id FROM this_table_does_not_exist
)
select @id = (SELECT id FROM computed_table)

Which, much like removing the parenthesis, produces a User Error Message.

I say, you're not missing anything. I think this is an MS SQL Server bug. It certainly seems related to the cte and the parenthesis. I tried googling for mention of it, but didn't find anything. This will give me something to talk about at the next local PASS meeting. Sorry all I have to add to the situation is confusion. If I learn something, I'll be sure and post it here!

---

Update : 2010-06-26 10:09 CST I went to Microsoft Connect in an attempt to find this listed as an issue. I was unable to find something around cte 208 or cte invalid object. Honestly, I don't know of another bug listing site for sql server which one could check. I also tried searching Microsoft Support and, again, Google.