SQL Server SELECT 查询中括号的作用? [英] Roles of parentheses in SQL Server SELECT queries?

问题描述

以下查询在 SQL Server 2008(在 SP1 上测试)上没有返回结果和错误，您可以对任何数据库运行它，甚至是 master:

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)

在 SQL Server 2005 上，由于表不存在，您会收到错误消息.如果你删除一些括号，你也会得到一个错误:

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

我认为查询中可能有问题，因为括号中的 SELECT 可能会被解释为表达式而不是子查询，如 此页面.但这至少应该返回一个错误.

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.

我错过了什么吗?

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

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

对于上面的查询，事件的顺序是:

For the query above, the sequence of events is:

• 异常(错误:208，无效的对象名称)
• SQL:批处理启动
• SQL:StmtStarting
• SQL:批量完成

对于没有括号的查询:

• 异常(错误:208)
• SQL:批处理启动
• SQL:StmtStarting
• 异常(错误:208)
• 用户错误消息(无效的对象名称this_table_does_not _exist")
• SQL:批量完成

对于工作查询:

• SQL:批处理启动
• SQL:StmtStarting
• Showplan 全部
• SQL:StmtCompleted
• SQL:批量完成

我还使用真实表运行了一个查询，这导致了我同样的问题.事件的顺序是:

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

• SQL:批处理启动
• SQL:StmtStarting
• SQL:批量完成

没有早期的异常"=> 表存在.没有 "SQL:StmtCompleted" => 这意味着发生了错误，我看不出任何其他原因不会引发此事件.没有Showplan All"=> 表示在计算执行计划之前(或何时)发生错误.可能是cte和括号组合导致的.

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.

我将在下周向 Microsoft 支持部门提出这个问题.

I will raise the issue with Microsoft support next week.

推荐答案

因此，根据我之前对原始问题的评论，我稍微简化了 sql.

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:批量启动
• SQL:批量完成
• SQL:StmtStarting
• SQL:StmtCompleted
• Showplan 全部
• 演出计划 XML

我发现的东西出乎意料:

What I caught was unexpected:

• SQL:批处理启动
• SQL:StmtStarting
• SQL:批量完成

注意:没有 SQL:StmtCompleted，没有计划.所以，接下来，我回到捕获设置，并在错误和警告下添加每个事件类.重新运行查询，你知道什么?第一个捕获的事件是:

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

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

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

我认为发生的事情是 DBMS 中的代码在说 - 嘿，他们没有要求我们返回任何东西或做任何事情，所以何必呢?让我们为要求更高的人腾出一些资源.

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 )

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

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.

我说，你没有遗漏任何东西.我认为这是一个 MS SQL Server 错误.它似乎与 cte 和括号有关.我尝试谷歌搜索提到它，但没有找到任何东西.这将使我在下一次本地 PASS 会议上有话要说.对不起，我要补充的情况就是混乱.如果我学到了一些东西，我肯定会在这里发布！

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!

更新:2010-06-26 10:09 CST我访问了 Microsoft Connect，试图将其列为问题.我无法找到有关 cte 208 或 cte invalid object.老实说，我不知道可以检查的另一个 sql server 错误列表站点.我还尝试搜索 Microsoft 支持和 Google.

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.

这篇关于SQL Server SELECT 查询中括号的作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！