SQL inner query conundrum

SoftSprocket · 12-25-2020, 06:07 PM

I recently suffered through a minor database disaster. The query I meant to run was something like this

Code:

delete from table_a where x_id in (select x_id from table_b where field_a = 2345)

What I in fact ran was:

Code:

delete from table_a where x_id in (select x_id from table_c where field_a = 2345)

Now, table_c didn't have a field x_id but instead of exiting with an error all records were deleted from table_c. We were able to recover using backups and transaction logs.

This was run on SQL Server. I verified the behavior substituting 'select count(*)' for delete.

I was shocked by this since my expectation is that the query would fail however I found this on a Microsoft page discussing subqueries:

Quote:

If a column is referenced in a subquery that does not exist in the table referenced by the subquery's FROM clause, but exists in a table referenced by the outer query's FROM clause, the query executes without error. SQL Server implicitly qualifies the column in the subquery with the table name in the outer query.

In the query in question table_a did not have a column named field_a so SQL Server must not only have satisfied the requirement for x_id in the subquery but also ignored the where clause giving

Code:

delete from table_a where x_id in (select x_id from table_a)

If this is what it does I have to wonder is this an expected SQL Server behavior? A SQL behavior? A bug perhaps?

To me it makes no sense.

astrogeek · 12-26-2020, 05:30 PM

As no one else has stepped up I'll offer a few comments.

Code:

delete from table_a where x_id in (select x_id from table_b where field_a = 2345)

First comment: I avoid SQL Server same as I avoid anything from the source, so cannot offer help specific to the platform. An initial quick test indicated to me that MySQL behaved differently, but after paying better attention I am suitably horrified to find that is not the case when table_a has a column of same name - yikes!

I suppose the relevant logic is that described by MySQL as correlated subqueries, which permits references to tables in the outer query from within the subquery. That combined with implicit qualification of field names results in this behavior.

BUT:

Quote:

Originally Posted by SoftSprocket

In the query in question table_a did not have a column named field_a so SQL Server must not only have satisfied the requirement for x_id in the subquery but also ignored the where clause

MySQL definitely fails with an Unknown column in where clause in this case. I am surprised at that behavior even for a micros-chism! That fails the implicit qualification case described so that is a pretty serious error.

Second comment: My own habit is to explicitly qualify table.column in all but the simplest queries, and to avoid subqueries wherever possible as it is almost always possible|better to do it with a join. For the example you posted my own approach would have been something like...

Code:

DELETE table_a FROM table_b JOIN table_a USING(x_id) where table_b.field_a=2345

That said, I have no idea whether that platform supports multi-table delete, but if so this would have kept you out of that particular spot of trouble.

SoftSprocket · 12-26-2020, 07:21 PM

Quote:

Originally Posted by astrogeek

MySQL definitely fails with an Unknown column in where clause in this case. I am surprised at that behavior even for a micros-chism! That fails the implicit qualification case described so that is a pretty serious error.

I think the issue there was that the column in the where clause was valid in the second table as well. I suppose the fact that it had no relevance to the query, since the only column in the subquery was being satisfied by the outer query, isn't enough to invalidate the whole thing.

Quote:

Originally Posted by astrogeek

Second comment: My own habit is to explicitly qualify table.column in all but the simplest queries, and to avoid subqueries wherever possible as it is almost always possible|better to do it with a join. For the example you posted my own approach would have been something like...

Code:

DELETE table_a FROM table_b JOIN table_a USING(x_id) where table_b.field_a=2345

That said, I have no idea whether that platform supports multi-table delete, but if so this would have kept you out of that particular spot of trouble.

It does and I have to often write that particular query and would have had to look up (well, I gues I should have). Using a join in the inner query also prevents the query from being satisfied by the outer query. Live and learn.