Possible Bug in SQL Server 2014,2019 With Temp tables

Question

I Have this Query (Minimal reproduction)
CREATE TABLE Product ON [PRIMARY]
GO

create table #TmpProduct
(
ID int
)
go

INSERT INTO Product([Num],[Name],[Price]) values(1,'P01',4);
INSERT INTO Product([Num],[Name],[Price]) values(2,'P02',41);
INSERT INTO Product([Num],[Name],[Price]) values(3,'P03',55);
INSERT INTO Product([Num],[Name],[Price]) values(4,'F04',21);
INSERT INTO Product([Num],[Name],[Price]) values(5,'F05',6);
INSERT INTO Product([Num],[Name],[Price]) values(6,'S06',1);

go
INSERT INTO #TmpProduct(ID) SELECT Num FROM Product WHERE Num < 3;
UPDATE Product SET Price =0 WHERE Num IN (SELECT Num FROM #TmpProduct);

I Have 2 problems
1- I Can run UPDATE Query Without any error, although Num Column is not part of TmpProduct !!!
2- With this query all Rows in Product table are set to 0!!! this is very dangerous

Nb: SQl server 2014 Version is 12.0.6024.0,
2019 15.0.2000.5

Answer 1

This is working as designed.

1. When you reference a column name in a subquery (Num in your case) SQL firsts looks for that column in the tables in your subquery (#TmpProduct in your case). If it doesn't find that column in the subquery tables it then looks for that column in the outer query (Product in your case). If it finds it there it then uses that column. You will only get an error if that column name is not in the tables of either the inner subquery or outer query. Since Num is in Product you do not get an error. This is the way SQL has always worked.
2. Since Num is in Product and not #TmpProduct, SQL uses the Num in Product. So your query effectively becomes UPDATE Product SET Price =0 WHERE Num IN (SELECT Num FROM Product);
   And since that is true for every row in Product, Price gets set to zero for every row.

You want to get into the practice of always explicitly specifying the table each column is in (particulary when the query references more than one table). So write your query like

UPDATE Product SET Price =0 WHERE Product.Num IN (SELECT #TmpProduct.Num FROM #TmpProduct);

If you do that, you will be given an error for invalid column name 'Num' and the query will not run.

Tom

Answer 2

Just to add to Tom's response.

This is normal, expected and documented behavior.

See "Important" note:
https://learn.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-ver16#qualifying