DELETE ignores WHERE clause containing invalid subquery
-
The WHERE clause in my DELETE statement failed because the subquery has an error. But instead of throwing an error, the statement deleted every record, apparently ignoring the WHERE clause entirely.
I've tested this using table variable, temp table, and table, all with the same result. Here is the version using a temp table.
CREATE TABLE #Test_Delete(MyId int NULL)
INSERT #Test_Delete
SELECT TOP 5 AccountID FROM dbo.Account
SELECT * FROM #Test_Delete --5 records returned
DELETE #Test_Delete
WHERE MyId IN
(SELECT MyId FROM dbo.Account) --MyId field does not exist in dbo.Account
SELECT * FROM #Test_Delete --0 records returned
(if I run just the subquery alone, I get this error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'MyId'.)
So, I tested again, replacing the WHERE clause with this:
DELETE #Test_Delete
WHERE MyId IN (5/0)
and instead of deleting all the rows in #Test_Delete, I got this error:
Server: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
The statement has been terminated
So, both of my subqueries alone return an error with the same level (16), but only the second subquery error terminated the delete statement. Why is this so? It seems like a bug.
Thanks,
Jean
-
Intresting, where did you test and what version is your SQL Server (along with @@version info). I tried a couple of ways and could not duplicate the problem.
-
Tested on SQL Server 2000 on two different machines, one running SP2 and the other running SP3.
-
I see what you mean. Not good.
-
Interestingly,
(and I used the NORTHWIND Employees table and setup the same way you had)
SELECT * FROM #Test_Delete
WHERE MyId IN
(SELECT MyId FROM dbo.Employees
WHERE MyID = 1)
actually returns the record in #Test_Delete with a value of 1
hmmmm?
-
I think I've figured it out. T-SQL treats the sub-query as a correlated sub-query when a field does not exist. I'm not sure if this is a feature of the optimizer, or what. But note, that if you change MyID to anything else in the sub-query an error IS returned. I'm sure that MS would say that this is by design. But it's definitely something to be careful of when coding.
Pete
-
This is a very old trap that's been around seen 6.x days.
Basically, what's happening with a statement like:
DELETE #Test_Delete
WHERE MyId IN
(SELECT MyId FROM dbo.Account) --MyId field does not exist in dbo.Account
is in fact this:
DELETE #Test_Delete
WHERE MyId IN
(SELECT 1 FROM dbo.Account)
The subquery column is treated like a constant, thus all rows in the outer query qualifies.
It's been a long time, and I don't remember all details about this off hand, but for sure, it's not very good that there is no error thrown.
On the other hand, (if memory serves right) you will get an error if you use aliasing in the subquery.
In that case, this would then error out as expected
DELETE #Test_Delete
WHERE MyId IN
(SELECT a.MyId FROM dbo.Account a) --MyId field does not exist in dbo.Account
Note - untested.
In any case, I'd like to propose to use aliasing as a good coding practice.
It makes complex queries easier to debug and maintain, and ambiguity is avoided.
(like in this case)
/Kenneth
-
Ah, in my testing I didn't pay attention here. I have seen this and it isn't that the column be treated as a constant it is assuming the value of the outer table. Remember it is legal syntax to use values from the outer table in a subquery in the select columns or where clauses like so.
DELETE #Test_Delete
WHERE MyId IN
(SELECT AccountID FROM dbo.Account WHERE AccountID = MyID)
Or you could just change toi and exists
DELETE #Test_Delete
WHERE EXISTS (SELECT AccountID FROM dbo.Account WHERE AccountID = MyID)
The problem can be seen both ways thou and you should always qualify the subquery items for proper safety. Had you done.
DELETE #Test_Delete
WHERE MyId IN
(SELECT A.MyId FROM dbo.Account A)
You would have been given the error you expected because you are forcing the subquery to look to the Account table for the column and not allowing implicit chains of action.
Or better than that use Join Syntax for the same thing but you can still cause yourself issues with things like
DELETE #Test_Delete
FROM #Test_Delete
INNER JOIN
dbo.Account
ON
MyID = MyID
Which is like say
ON 1=1
in essence but would be more logical in reasoning to look at.
-
You're right.
It's not converted to a constant per se, but I'd say the the behaviour is identical as if it had.
By assuming the value from the outer table, the subquery sort of 'is always true', since it will be 'in' or 'exists' for every row.
..something along those lines..
/Kenneth
-
Thanks everyone. I confirmed that aliasing the subquery in the WHERE clause does raise an error, and no rows are deleted. I also confirmed that replacing the WHERE clause with a join to dbo.Account and aliasing the tables also raises an error. So aliasing is a good practice. Still, it seems that if the subquery by itself causes an error, then the entire statement should also result in an error. I'll definitely remember this trap in the future.
-Jean
-
sqljean,
You should not be using a sub-query at all, and definitely not a "Where IN". Instead, use a join...
DELETE d
From #Test_Delete d
JOIN dbo.Account a on d.MyID = a.MyID
Signature is NULL
-
From SQL Server Books Online:
In queries that include a correlated subquery(also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.
They value in MYID was actually coming from the outer query. That is why it executes successfully as a subquery but does not when it stands alone.
-
So this is a "feature"! (Still a trap, though.) And I should add a disclaimer - I found this bug in a system I inherited. I didn't write it! I hate using 'IN' and nearly always use JOINs, or EXISTS where appropriate. I will be fixing the stored proc that contains this delete, using a join, propery aliased, to be sure 🙂 Thanks again!
Jean
-
DELETE #Test_Delete
WHERE MyId IN ( select MyId FROM dbo.Account)
I tried replacing the ColumnName MyId insided subquery with MyId1, MyI, or anything else other than MyId, it does give an error in delete.
only for Same Column Name it does not give an error !!
-
Yes grosshopper,
You should use eliase in subquerry. Not only this please verify the follwoing. Using IN operator on nullable fieds is a danger. when the ansi nulls is set to on. So I think using exists is better always. see the following example
select 1 where 1 not in (2,3,null)
--this gives no result
select 1 where 1 not in (2,3)
--this gives result 1
select 1 where null in (null,2)
--gives no result
If I am wrong please Educate me.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply