为什么CROSS APPLY * not *在此查询中没有得到无效的列错误? [英] Why does CROSS APPLY *not* get an invalid column error in this query?

问题描述

我正在编写一些代码来查询某些DMV.根据SQL版本的不同，DMV中某些列可能存在也可能不存在.我在网上找到了一个有趣的建议，如何跳过使用CROSS APPLY的特定检查.

I am writing some code to query some DMVs. Some of the columns may or may not exist in the DMV depending on SQL version. I found an interesting suggestion online how to skip specific checking using CROSS APPLY.

下面的查询是一个代码示例，用于读取可能缺少的列的DMV.该代码为该列创建一个默认值，并使用CROSS APPLY从DMV中提取实际的列(如果存在).

The query below is an example of code to read a DMV for a potentially missing column. The code creates a default value for the column and uses CROSS APPLY to extract the actual column, if it exists, from the DMV.

代码尝试提取的列BogusColumn不存在.我希望下面的查询会生成有关无效列名的错误...但事实并非如此.它返回NULL且没有错误.

The column the code tries to extract, BogusColumn, does not exist. I would expect the query below to generate an error about an invalid column name... but it does not. It returns NULL without error.

为什么下面的CROSS APPLY子句不会导致无效的列名"错误?

declare @x int
select @x = b.BogusColumn
from
(
    select cast(null as int) as BogusColumn
) a
cross apply
(
    select BogusColumn from sys.dm_exec_sessions
) b;
select @x;

如果我分别在CROSS APPLY中运行查询:

If I run the query in the CROSS APPLY separately:

select BogusColumn from sys.dm_exec_sessions;

我收到有关无效列名的预期错误:

I get an expected error about an invalid column name:

Msg 207, Level 16, State 1, Line 9
Invalid column name 'BogusColumn'.

如果将DMV列名更改为BogusColumn2以使其唯一，则会出现预期的列名错误:

If I change the DMV column name to BogusColumn2 to make it unique, I get the expected column name error:

select a.BogusColumn1, b.BogusColumn2
from
(
    select cast(null as int) as BogusColumn1
) a
cross apply
(
    select BogusColumn2 from sys.dm_exec_sessions
) b

我已经在SQL 2012到SQL 2017的版本上测试了此行为，并且该行为在所有版本中都是一致的.

I have tested this behavior on versions of SQL 2012 through SQL 2017, and the behavior is consistent across all versions.

推荐答案

BogusColumn在第一个查询中定义为有效列.

BogusColumn is defined as a valid column in the 1st query.

当我们应用交叉应用时，它使用列分辨率如下:
1.在第二个查询(dmv)中查找列'BogusColumn'
2.如果dmv中存在该列，它将解析为dmv
3.如果dmv中不存在该列，它将在外部查询(最上面的查询)中查找此列，并使用那里提供的值.

When we are applying cross apply, it is using column resolution as follow:
1. It looks for the column 'BogusColumn' in the 2nd query (dmv)
2. If the column exists in the dmv, it will be resolved to the dmv
3. If the column do not exists in the dmv, it will look for this column in the outer query (the top one) and use value provided there.

换句话说，如果在视图中未定义伪列，则最终查询的工作方式为:

In other words when bogus column is not defined in the view the final query will work as:

select * from
(
    select cast(null as int) as BogusColumn
) a
cross apply
(
    select a.BogusColumn AS BogusColumn from sys.dm_exec_sessions
) b;

如果已定义，查询将解析为:

If it is defined, query will resolve to:

select * from
(
    select cast(null as int) as BogusColumn
) a
cross apply
(
    select s.BogusColumn AS BogusColumn from sys.dm_exec_sessions as s
) b;

这篇关于为什么CROSS APPLY * not *在此查询中没有得到无效的列错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！