如何不小心删除表中的所有行

Someone wants to take a stab at explaining the mechanics of this... this little quirk of the query parser almost caused major damage for me today.

有人想要解释一下这个问题的机制...这个查询解析器的小怪癖几乎对我造成了重大损害。

Create a test table with 100 rows, with 1-100.

创建一个包含100行的测试表,其中包含1-100。

create table test( JobID int primary key);

;with numbers as (
    select 1 as n
    union all
    select n + 1 as n 
    from numbers
    where n < 100
)
insert into test
select n from numbers

Create a temp table with integers 1-50 in it:

创建一个包含整数1-50的临时表:

select jobid as number into #deletions
from test 
where jobid <= 50

Now do a delete using an IN clause but with the wrong column name in the inner query:

现在使用IN子句进行删除,但内部查询中的列名错误:

delete from test where JobID in (select JobID from #deletions)

That last delete statement, from appearances, gives the appearance of deleting 50 rows... However, there is no JobID in #deletions, so it kind-of pulls that from the outer query and ends up, somehow, deleting all the rows in test.

从外观上看,最后一个删除语句给出了删除50行的外观......但是,在#deletions中没有JobID,所以它从外部查询中拉出来并最终以某种方式删除了所有的行测试。

My question is, how on earth is it interpreting that inner query... #deletions only has 50 rows, so how is it pulling all 100 id's from the outer table? This type of typo/mistake almost caused major damage to me today.

我的问题是,它究竟是如何解释内部查询... #deletions只有50行,那么如何从外表中提取所有100个id?这种类型的错字/错误几乎对我今天造成了重大损害。

In my opinion, this should throw some kind of parsing/syntax error or some kind of ambiguity error.

在我看来,这应该抛出某种解析/语法错误或某种歧义错误。

Here's a SQL Fiddle Demo

这是一个SQL小提琴演示

1 个解决方案

#1

If you use table aliases, the logic would be clear. You think you are writing:

如果使用表别名,则逻辑清晰。你以为你在写:

delete from test
    where test.JobID in (select d.JobID from #deletions d);

That makes sense, but it would generate a syntax error because JobId does not exist in #deletions. So, scoping rules of SQL go to the next level to find JobId and interpret the query as:

这是有道理的,但它会产生语法错误,因为#Iletions中不存在JobId。因此,SQL的范围规则进入下一级别以查找JobId并将查询解释为:

delete from test
    where test.JobID in (select test.JobID from #deletions d);

This will delete all non-NULL values of JobId.

这将删除JobId的所有非NULL值。

The moral: Always use qualified column names.

道德:始终使用合格的列名。

#1