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)
- SQL:BatchStarting
- SQL:StmtStarting
- SQL:BatchCompleted
异常(错误:208,对象名称无效)
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
例外(错误:208)
例外(错误:208)
用户错误消息(无效的对象名称'this_table_does_not _exist')
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
3
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。
#1
3
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。