SQL Server SELECT查询中括号的角色？

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:

以下查询在SQL Server 2008上没有返回任何结果且没有错误(在SP1上测试),您可以针对任何数据库运行它,甚至是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:

在SQL Server 2005上,您会收到错误,因为表不存在。如果删除一些括号,也会出现错误:

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.

我认为查询中可能存在问题,因为括号中的SELECT可能会被解释为表达式而不是子查询,如本页所示。但这应该至少会返回一个错误。

Am I missing something?

我错过了什么吗?

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

编辑26/06/2010:我运行了一些分析会话,结果如下。

For the query above, the sequence of events is:

对于上面的查询,事件序列是:

• Exception (Error: 208, invalid object name)

异常(错误:208,对象名称无效)

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

For the query without parentheses:

对于没有括号的查询:

• Exception (Error: 208)

例外(错误:208)

• SQL:BatchStarting
• SQL:StmtStarting
• Exception (Error: 208)

例外(错误:208)

• User Error Message (Invalid object name 'this_table_does_not _exist')

用户错误消息(无效的对象名称't​​his_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.

没有早期的“例外”=>表存在。没有“SQL:StmtCompleted”=>这意味着发生了错误,我看不出为什么不会引发此事件的任何其他原因。否“Showplan All”=>它表示错误发生在计算执行计划之前(或之后)。它可能是由cte和括号的组合引起的。

I will raise the issue with Microsoft support next week.

我将在下周提出微软支持的问题。

1 个解决方案

#1

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

所以,根据我之前对原始问题的评论,我简单地简化了sql。

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:

注意:没有SQL:StmtCompleted,没有计划。接下来,我将回到捕获设置,并在错误和警告下添加每个事件类。重新运行查询,你知道什么?首次发现的事件是:

Error: 208, Severity: 16, State: 1

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

猜猜208是什么?但是,客户端永远不会看到错误。

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.

我认为发生的事情是DBMS中的代码说 - 嘿,他们没有要求我们返回任何东西或做任何事情,所以为什么要这么麻烦?让我们为更苛刻的人腾出一些资源。

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.

这彻底打破了我的理论。括号不被解释为表达式。相反,它们被解释为完全查询(也就是说,客户端要求返回某些内容)并返回一个包含1列和1行的记录集。但是没有计划 - 可能不需要b / c计划,因为没有涉及数据库对象。

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!

我说,你没有遗漏任何东西。我认为这是一个MS SQL Server错误。它当然似乎与cte和括号有关。我试着谷歌搜索提到它,但没有找到任何东西。这将给我在下一次本地PASS会议上谈论一些事情。对不起,我必须添加的情况是混乱。如果我学到了什么,我一定会把它贴在这里!

---

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.

更新:2010-06-26 10:09 CST我去了Microsoft Connect试图找到这个列为问题。我无法找到cte 208周围的东西或cte无效的对象。老实说,我不知道另一个可以检查sql server的错误列表网站。我也尝试过搜索Microsoft支持,再次搜索Google。