Query executes despite invalid IN clause

Question

I was purging some invalid information from a database and ran the following query

delete from table1 where 
    table1id in( select table1id from wrongTable    )
    and
    table1id not in (select validColumn from table3)

And it deleted several thousand rows.

Then I executed just :

select table1id from wrongTable

and got the error message:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'table1id'

I then realized my error. My IN clause should have been

 select fktable1id from correctTable

Luckily, I had the second clause. Otherwise, I would have completely wiped out the table.

Am I missing something or is this a bug? Shouldn't the first query have failed to execute?

Here is script to reproduce:

USE [SynDoc_Demo]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp1]') AND type in (N'U'))
DROP TABLE [dbo].[temp1]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[rightTable]') AND type in (N'U'))
DROP TABLE [dbo].[rightTable]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[wrongTable]') AND type in (N'U'))
DROP TABLE [dbo].[wrongTable]
GO





create table temp1 (itemid int, name varchar(25))
go
insert into temp1 values (1,'bob')
insert into temp1 values (2,'dave')
go

create table wrongTable (junk int)
go
insert into wrongTable values (10)
go

create table rightTable (fkitemid int)
go
insert into rightTable values (2)

go

select * from temp1
go

delete from temp1 where itemid in (select itemid from wrongTable)
go

select * from temp1

EDIT: Edited sample query to clarify for anyone attempting to reproduce

I couldn't reproduce the issue on my end. Can you setup an sqlfiddle for us demonstrating the problem? — Giorgos Betsos, Sep 06 '16 at 17:23
@GiorgosBetsos Unfortunately, sqlfiddle is blocked for me, but I added a script to the question that does it — Kevin, Sep 06 '16 at 17:50

score 1 · Answer 1 · answered Sep 06 '16 at 20:54

Not an answer but too long winded for the short comments.

Like Giorgos I couldn't reproduce until you added your script.

If I change the delete to

SELECT * FROM TEMP1 WHERE ITEMID IN (SELECT ITEMID FROM WRONGTABLE)

it still returns the two rows. Suspecting some kind of assumption by the engine that because ITEMID exists in TEMP1, tried to qualify it further

the following fails.

SELECT * FROM TEMP1 WHERE ITEMID IN (SELECT A.ITEMID FROM WRONGTABLE AS A)

Still - it's a great spot and one to be wary of.

Query executes despite invalid IN clause

1 Answers1