No error while executing a query with wrong column name

Question

I came across strange behaviour, at least on SQL Server (I still need to check other SQL engines), while trying to delete some records.

I tested the following on SQL Servers instance:

• Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 2016 LTSB 10.0 (Build 14393: )
• Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

Here is a SQL code snippet. This is simplified version of what I was trying to do, it is for sure discussable why to do a query this way, but my point is different -- why does it happen.

    drop table if exists #A
    drop table if exists #B

    create table #B (id char, foo char) -- I use different types for id columns just for the sake of readability
    create table #A (id int, b_id char) -- b_id is a link to #A, _not_ specified as FK

    insert into #B values('x', 'l')
    insert into #B values('y', 'm')

    insert into #A values(0, 'x')
    insert into #A values(1, 'z')
    insert into #A values(2, 'z')
    insert into #A values(3, 'y')
    insert into #A values(4, 'y')
    insert into #A values(5, 'y')

    -- there are 2 parent records in #B and 3 child records for each of them in #A
    select * from #A -- just to check, all good the data is there, all as expected, no problem

    -- now the fun part

    --(X) the following query, rightfully gives an error, as column b_id does not exist in #B
    -- select b_id from #B where foo='l'

    --(Y) the following query gives empty result, whereas I would expect an error:
    select * from #A where b_id in (select b_id from #B where foo='l')
    -- I can imagine that this has something to do with the fact that b_id exists in table #A in outer query

    --(Z) the following query deletes(!) all data in table #A:
    delete from #A where b_id in (select b_id from #B where foo='l')
    select * from #A
    -- once again, I can imagine that there is no error message "Invalid column name 'b_id'." because b_id exists in table #A in outer query

So here are my questions:

1. Why there is no error message about invalid column in queries (Y) and (Z)? I would be interested in details
2. Depending on the answer (1), would be interesting to know why query (Y) provides empty result. It is clear that if inner select is empty, then outer should be empty, but devil hides in details
3. Why query (Z) deletes all records from table #A? I would expect that affected records (returned in case of (Y) and deleted in case of (Z)) should be the same

Answer 1

Question 1 (Y):

The (live and actual) execution plan shows that your imagination was correct: the output from each node is shown, including the table prefix - no confusion possible: The node 3 returns the following two columns: [#A].[id], [#A].[b_id], while node 4 has nothing to return but NULL

/*

         id b_id
----------- ----
          0 x
          1 z
          2 z
          3 y
          4 y
          5 y

(6 rows affected)

Table '#B__________________________________________________________________________________________________________________000000000335'. Scan count 1, logical reads 6, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table '#A__________________________________________________________________________________________________________________000000000336'. Scan count 1, logical reads 1, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Rows  Executes StmtText                                                                             NodeId Parent PhysicalOp   LogicalOp      Argument                                                       DefinedValues            EstimateRows    EstimateIO   EstimateCPU  AvgRowSize TotalSubtreeCost OutputList              Warnings Type      EstimateExecutions
----  -------- ------------------------------------------------------------------------------------ ------ ------ ------------ -------------- -------------------------------------------------------------- ----------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------- -------- --------- ------------------
   6         1 select * from #A where b_id in (select b_id from #B where foo='l')                        1      0 NULL         NULL           NULL                                                           NULL                                6          NULL          NULL        NULL       0,00701002 NULL                    NULL     SELECT                  NULL
   6         1   |--Nested Loops(Left Semi Join)                                                         2      1 Nested Loops Left Semi Join NULL                                                           NULL                                6             0     2,508E-05          12       0,00701002 [#A].[id], [#A].[b_id]  NULL     PLAN_ROW                   1
   6         1        |--Table Scan(OBJECT:([tempdb].[dbo].[#A]), WHERE:([#A].[b_id]=[#A].[b_id]))       3      2 Table Scan   Table Scan     OBJECT:([tempdb].[dbo].[#A]), WHERE:([#A].[b_id]=[#A].[b_id])  [#A].[id], [#A].[b_id]              6      0,003125     0,0001636          12        0,0032886 [#A].[id], [#A].[b_id]  NULL     PLAN_ROW                   1
   6         6        |--Table Scan(OBJECT:([tempdb].[dbo].[#B]), WHERE:([#B].[foo]='l'))                4      2 Table Scan   Table Scan     OBJECT:([tempdb].[dbo].[#B]), WHERE:([#B].[foo]='l')           NULL                                1     0,0032035      8,07E-05           9        0,0036877 NULL                    NULL     PLAN_ROW                   6

(4 rows affected)

*/

Question 2: The query does return results.

Question 3:

SELECT * FROM #A WHERE b_id IN (SELECT b_id    FROM #B WHERE foo = 'l');
SELECT * FROM #A WHERE  EXISTS (SELECT 'YAAAY' FROM #B WHERE foo = 'l');
SELECT * FROM #A WHERE  EXISTS (SELECT 'YAAAY' FROM #B WHERE foo = 'asdafadsf');

I think that the OP's query can be rewritten into an equivalent EXISTS query.

The b_id will always be equal to b_id, except when there are no results returned from the IN () part.