Roles of parentheses in SQL Server SELECT queries?_问答_开发者

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 b开发者_如何学运维ecause 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.

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.

From the microsoft doc https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql?view=sql-server-ver15, it looks like select with parentheses is only a special case in union parser. I doubt you can use it as a regular select.