SQL Server 2019 bug nested query

Question

CREATE TABLE [dbo].Orders

(

[order_id] [int] NULL

) ON [PRIMARY]

GO

INSERT INTO Orders VALUES (1)

INSERT INTO Orders VALUES (2)

INSERT INTO Orders VALUES (3)

INSERT INTO Orders VALUES (4)

INSERT INTO Orders VALUES (5)

CREATE TABLE [dbo].Orders_Delete

(

[order_id_delete] [int] NULL

) ON [PRIMARY]

GO

INSERT INTO Orders_Delete VALUES (3)

SELECT* FROM Orders

WHERE order_id IN (SELECT order_id_delete FROM Orders_Delete)

-- attention: order_id column not exists in Orders_Delete - query results a wrong

SELECT* FROM Orders

WHERE order_id IN (SELECT order_id FROM Orders_Delete)

--WTF??

Answer 1

Hi @Chen Shacham

Someone asked a similar question on the forum before and it was solved. You can refer to it.
possible-bug-in-sql-server-2014-with-temp-tables.html

Hope this helps you.

Best regards,
Percy Tang

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

SELECT * FROM Orders o

WHERE o.order_id IN (SELECT od.order_id_delete FROM Orders_Delete od)

Answer 3

No, it is not a bug, but this is how the SQL language is defined.

The query that follows IN is correlated. That is, it can refer to tables outside the subquery, for instance:

   SELECT ...  
   FROM  tbl  
   WHERE tbl.col IN (SELECT tbl2.col FROM tbl2 WHERE tbl2.othercol= tbl1.othercol)  

When you have un prefixed column, SQL will first try to find a matching table in the subquery. If that fails, it will try to find a matching table in the outer query.

The way to avoid this problem is as Jingyang suggests: Always prefix your tables with column aliases in all your queries, or at least all queries referring to more than one table.