sql - 执行列名错误的查询时没有错误

Question

我遇到了奇怪的行为，至少在 SQL Server 上（我仍然需要检查其他 SQL 引擎），同时尝试删除一些记录。

我在 SQL Server 实例上测试了以下内容：

• Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 版权所有 (C) 2019 Microsoft Corporation Developer Edition (64-bit) o​​n 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 版权所有 (c) Microsoft Corporation Standard Edition (64-bit) o​​n Windows Server 2012 R2 Standard 6.3 (Build 9600:) （管理程序）

这是一个 SQL 代码片段。这是我试图做的事情的简化版本，肯定可以讨论为什么要以这种方式进行查询，但我的观点不同——为什么会发生这种情况。

    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

所以这是我的问题：

1. 为什么查询 (Y) 和 (Z) 中没有关于无效列的错误消息？我会对细节感兴趣
2. 根据答案（1），知道为什么查询（Y）提供空结果会很有趣。明明如果inner select为空，则outer应该为空，但魔鬼隐藏在细节中
3. 为什么查询 (Z) 会删除表 #A 中的所有记录？我希望受影响的记录（在 (Y) 的情况下返回并在 (Z) 的情况下删除）应该是相同的

标签： sqlsql-serverdatabaseselectsql-delete

Answer 1

问题 1（Y）：

（实时和实际）执行计划表明您的想象是正确的：显示了每个节点的输出，包括表前缀 - 不会混淆：节点 3 返回以下两列：[#A].[id], [#A].[b_id]，而节点 4 没有什么可返回但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)

*/

问题 2：查询确实返回结果。

问题 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');

我认为 OP 的查询可以重写为等效的 EXISTS 查询。

将b_id始终等于b_id，除非该IN ()部件没有返回任何结果。