Typo in a column name inside a sub-query, but no "Invalid column name" error-CodePudding

I have a table, let's call it A. I want to delete rows with IDs 1 and 2 from that table. For that, I created a table variable @B, containing values 1 and 2 but that column I will name PK.

Now I do this:

DELETE FROM A
WHERE ID IN (
    SELECT ID
    FROM @B
)

Notice my (deliberate) programming error. In the sub-select, I have used a wrong column name. Accidentally it is the same name used in table A.

This should result in an 'invalid column name' error, right? Except it does not. It executes. Not only that, all data from table A gets deleted. As if there is no more predicate.

I have created a full demo script:

-- What happened to my data???

IF OBJECT_ID('tempdb..#JustATable') IS NOT NULL
    DROP TABLE #JustATable

CREATE TABLE #JustATable (
    PK INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    ID INT NOT NULL,
    NOTE VARCHAR(100) NOT NULL
)

INSERT INTO #JustATable (ID, NOTE)
SELECT database_id, DB_NAME(database_id)
FROM sys.databases;

SELECT NULL [inserted all the rows from sys.databases into the temptable], *
FROM #JustATable;

DECLARE @JustATableVariable TABLE (
    PK INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    ID_2 INT NOT NULL,
    NOTE VARCHAR(100) NOT NULL
)

INSERT INTO @JustATableVariable (ID_2, NOTE)
SELECT database_id, DB_NAME(database_id)
FROM sys.databases
WHERE database_id = 2;

SELECT NULL [this is my table variable data], *
FROM @JustATableVariable;

DELETE FROM #JustATable
WHERE ID IN (
    SELECT ID_2
    FROM @JustATableVariable
);

SELECT NULL [I have just removed tempdb from the temptable], *
FROM #JustATable;

DELETE FROM #JustATable
WHERE ID IN (
    SELECT ID /* this is the wrong column name but the same name as used in the temptable column */
    FROM @JustATableVariable
);

SELECT NULL [wait...where is my data?], *
FROM #JustATable;

Can someone explain to me what is going on here? Has anyone seen this behavior before? Could this be a bug?

CodePudding user response：

In the subquery:

... (select id from @b)

... the column id is not fully qualified. So according to SQL specs, the RDBMS will first see if id column exists in table @b. If it does not, it will search "upwards" until it finds the id column in table a. The query is effectively identical to:

delete from a where id in (select a.id from @b)

Syntactically correct, semantically wrong.