Why does CROSS APPLY *not* get an invalid column error in this query?

Question

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.

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.

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.

Why does the CROSS APPLY clause below NOT result in an "invalid column name" error?

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;

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

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

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

Suellen

Although this behavior is predictable, it's also an extremely grody hack. Whoever came up with it deserves both kudos, and a slap on the wrist for introducing a maintenance trap like this. It's just about acceptable for covering version differences in the system views, and just about for nothing else. – Parental

I agree with @JeroenMostert. To avoid errors caused by surprising change in the column resolution ALWAYS use table aliases in from of the column. I seen production down because someone added a new column to a table causing similar effect. – Relativize

Brilliant question! And kudos to @Piotr for the mention of column aliasing. I use APPLY a lot, often nested and without aliases things can get quite confusing quickly. – Footer

I agree this is both clever and an ugly hack. I would not want to use this in production code, but I do want to use it to avoid the versioning issues in the DMVs. DBA-type queries for analyzing server activity are much simpler with this method instead of all of the version checking that I would otherwise have to do. IF @MajorVersion >= @SQL2016 AND @MinorVersion >= @SQL2016SP1 BEGIN /* write and execute dynamic SQL, etc. */ END – Suellen

Answer 1

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

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;

Relativize