Ravindra HV
(imported from SE)
Is there any explanation so as to why SSMS did not raise any compilation error for the below delete query with a subquery that is invalid on its own?
Below is the sequence of steps to reproduce the behavior:
CREATE TABLE [dbo].[delete_test]
(
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nchar](10) NOT NULL
) ON [PRIMARY];
Seed data:
insert dbo.delete_test(name) values(N'a'),(N'b'),(N'c'),(N'd'),(N'e');
Create a backup table:
select id, name
into dbo.delete_test_backup
from dbo.delete_test where id > 2;
Below statement should ideally not execute and, even if it executes, should only delete rows with `id` values that have been backed up :
delete dbo.delete_test where id in (
select id delete_test_backup
);
The message shows that 5 rows were deleted, but I expected only 3 rows to be deleted:
(5 row(s) affected)
Attempt to execute the sub-query as a standalone fails :
select id delete_test_backup ;
> Invalid column name 'id'
Below one executes as expected :
select id from dbo.delete_test_backup ;
> Output :
id
--
3
4
5