Possible bug in SQL server on DELETE?

Question

with code below will delete data in test2 which is not expected

1. there is a clear syntax error in the subquery but not detected by intellisense
2. the query run fine and deleted the data in test2 create table test1 (RYear varchar(6)) insert into test1 (RYear) values ('202107') create table test2 (RMonth varchar(6)) insert into test2(RMonth) values ('202108') select * from test1 select * from test2 delete test2 where RMonth in (select RMonth from test1) select * from test2

Answer 1

This is working as designed. When you have a subquery like
(select MyColumn From MyTable)
SQL looks for a column named in the table MyTable. If it finds one, it uses that column. But if Mytable doesn't contain a column named MyColumn, SQL will look at the outer table. If it finds one there, SQL will use it. If it doesn't find one there, then you get a syntax error.

So since RMonth exists in test2, but not in test1, your query is equivalent to

 delete test2
 where test2.RMonth in (select test2.RMonth from test1)

And, of course, test2.Month can be found in test2.Month, so the row is deleted.

Tom

Answer 2

As Tom said, this is working as intended.

Please see:
https://learn.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-ver15#qualifying

Important

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.