By Wjdavis5


2019-06-07 15:02:08 8 Comments

So we have a query with a subquery that has a typo in it. It's missing the FROM clause. But when you run it, it doesnt error out! Why!?


SELECT

    1
   ,r.id
   ,'0D4133BE-C1B5-4141-AFAD-B171A2CCCE56'
   ,GETDATE()
   ,1
   ,'Y'
   ,'N'
   ,oldItem.can_view
   ,oldItem.can_update

FROM Role r

JOIN RoleObject oldReport
    ON r.customer_id = oldReport.customer_id

JOIN RoleItem oldItem
    ON oldReport.id = oldItem.role_object_id
        AND r.id = oldItem.role_id

WHERE r.id NOT IN (SELECT
        role_id
    WHERE role_object_id = '0D4133BE-C1B5-4141-AFAD-B171A2CCCE56')

AND oldReport.id = '169BA22F-1614-4EBA-AF45-18E333C54C6C'

3 comments

@Aaron Bertrand 2019-06-07 16:19:57

This statement is legal (in other words, no FROM is required):

SELECT x = 1;
SELECT x = 1 WHERE 1 = 1; -- also try WHERE 1 = 0;

The trick is when you introduce a column name that clearly can't exist. So these fail:

SELECT name WHERE 1 = 1;

SELECT x = 1 WHERE id > 0;

Msg 207, Level 16, State 1
Invalid column name 'name'.
Msg 207, Level 16, State 1
Invalid column name 'id'.

But when the invalid column is introduced in something like a subquery, what SQL Server does when it can't find that column in the inner scope of the subquery, is traverse to an outer scope, and make the subquery correlated to that outer scope. This will return all rows, for example:

SELECT * FROM sys.columns WHERE name IN (SELECT name WHERE 1 = 1);

Because it's essentially saying:

SELECT * FROM sys.columns WHERE name IN (SELECT sys.columns.name WHERE 1 = 1); /*
              ^^^^^^^^^^^                       -----------
                   |                                 |
                   -----------------------------------    */

You don't even need a WHERE clause in the subquery:

SELECT * FROM sys.columns WHERE name IN (SELECT name);

You can see that it's really looking at the outer scoped table, because this:

SELECT * FROM sys.columns WHERE name IN (SELECT name WHERE name > N'x');

Returns far fewer rows (11 on my system).

This involves adherence to the standard about scoping. You can see similar things when you have two #temp tables:

CREATE TABLE #foo(foo int);
CREATE TABLE #bar(bar int);

SELECT foo FROM #foo WHERE foo IN (SELECT foo FROM #bar);

Obviously, this should error, right, since there is no foo in #bar? Nope. What happens is that SQL Server says, "oh, I didn't find a foo here, you must have meant the other one."

Also, in general, I would avoid NOT IN. NOT EXISTS has the potential to be more efficient in some scenarios, but more importantly, its behavior doesn't change when it is possible that the target column could be NULL. See this post for more info.

@Marc.2377 2019-06-09 03:17:05

I asked a question on Stack Overflow to which the answer is essentially the same as this (although yours is more thorough). Why is referencing a column (as a left-hand operand) that is not part of the table being queried not an error in the EXISTS operator?

@Piotr 2019-06-07 16:19:49

In SQL Server SELECT syntax do not require FROM section. If you omit FROM, select statement will use "dummy" table which has one row and no columns. So

select 'x' as c where ...

will return one row if the expression is true and no rows when it is false.

@Aaron Bertrand 2019-06-07 16:21:51

But that doesn't work if you just say select c and c does not exist in some outer object. I agree that FROM is not required, but the mechanics in play here when you explicitly name a column that does exist in an outer scope are definitely different than a dummy table, and if you don't provide a constant for a column that doesn't exist, you get a runtime error, so no dummy table there either. Dummy table can come into play in other scenarios, but not when the reference is in a subquery / derived table.

@Piotr 2019-06-07 16:23:46

In you example it is a correlated sub-select, role_id and role_object_id belongs to one of the tables in the outer select.

@Aaron Bertrand 2019-06-07 16:24:35

Right, but saying SELECT 'x' AS c is a completely different scenario than the OP's, who just said SELECT c. In a subquery / derived table.

@paulbarbin 2019-06-07 15:10:12

I reproduced this in 2016 with a simplified example:

declare @t1 table (c1 int, c2 int, c3 int)
insert into @t1 values (1,2,3), (2,3,4), (3,4,5)

select * from @t1
where
    c1 not in 
    (select c2 where c3 = 3)

It appears that c2 and c3 are evaluated for each row.

Related Questions

Sponsored Content

2 Answered Questions

[SOLVED] Why is Query Store missing details?

1 Answered Questions

[SOLVED] Why doesn't this invalid subquery raise a syntax error?

3 Answered Questions

[SOLVED] Why does this query take so long to execute?

2 Answered Questions

[SOLVED] Why does sp_executesql use a different query plan?

7 Answered Questions

[SOLVED] Why does SQL try to convert rows that are excluded by my where clause?

2 Answered Questions

4 Answered Questions

[SOLVED] Weird WHERE Clause Behavior. Why does this return a row?

  • 2016-05-17 20:42:38
  • GWR
  • 262 View
  • 4 Score
  • 4 Answer
  • Tags:   sql-server

1 Answered Questions

[SOLVED] Why is this select statement not returning what I've provided?

  • 2015-05-03 13:06:07
  • user3323654
  • 3370 View
  • 2 Score
  • 1 Answer
  • Tags:   sql-server

1 Answered Questions

[SOLVED] SQL Server Query: Inefficient where clause

Sponsored Content