{"id":2417,"date":"2019-04-23T17:10:04","date_gmt":"2019-04-23T15:10:04","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=2417"},"modified":"2019-04-23T17:11:51","modified_gmt":"2019-04-23T15:11:51","slug":"no-this-is-not-a-bug-in-t-sql","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2019\/04\/23\/no-this-is-not-a-bug-in-t-sql\/","title":{"rendered":"No, this is not a bug in T-SQL"},"content":{"rendered":"\n
(or, Column scope and binding order in subqueries)<\/p>\n\n\n\n
I keep seeing this in all sorts of places. People getting an unexpected result when working with a subquery, typically an IN subquery, and assuming that they\u2019ve found a bug in SQL Server.<\/p>\n\n\n\n
It\u2019s a bug alright, in that developer\u2019s code though.<\/p>\n\n\n\n
Let\u2019s see if anyone can spot the mistake.<\/p>\n\n\n\n
We\u2019ll start with a table of orders.<\/p>\n\n\n
There would be more to it in a real system, but this will do for a demo. We\u2019re doing some archiving of old orders, of inactive clients. The IDs of those inactive clients have been put into a temp table<\/p>\n\n\n
And, to check before running the actual delete, we run the following:<\/p>\n\n\n
\nSELECT * FROM dbo.Orders\nWHERE ClientID IN (SELECT ClientID FROM #TempClients)\n<\/pre><\/div>\n\n\n
And it returns the entire Orders table. The IN appears to have been completely ignored. At least the query was checked before doing the delete, that\u2019s saved an unpleasant conversation with the DBA if nothing else.<\/p>\n\n\n\n
Anyone spotted the mistake yet?<\/p>\n\n\n\n
It\u2019s a fairly simple one, not easy to see in passing, but if I test the subquery alone it should become obvious.<\/p>\n\n\n\n<\/figure>\n\n\n\n<\/figure>\n\n\n\n
The column name in the temp table is missing an I, probably just a typo, but it has some rather pronounced effects.<\/p>\n\n\n\n
The obvious next question is why the select with the subquery in it didn\u2019t fail, after all, the query asks for ClientID from #TempClients, and there\u2019s no such column. However there is a ClientID column available in that query, and it\u2019s in the Orders table. And that\u2019s a valid column for the subquery, because column binding order, when we have subqueries, is first to tables within the subquery, and then, if no match is found, to tables in the outer query.<\/p>\n\n\n\n
It has to work this way, otherwise correlated subqueries would not be possible. For example:<\/p>\n\n\n
\nSELECT c.LegalName,\nc.HypernetAddress\nFROM dbo.Clients AS c\nWHERE EXISTS (SELECT 1 FROM dbo.Shipments s WHERE s.HasLivestock = 1 AND c.ClientID = s.ClientID)\n<\/pre><\/div>\n\n\n
In that example, c.ClientID explicitly references the Client table in the outer query. If I left off the c., the column would be bound to the ClientID column in the Shipments table.<\/p>\n\n\n\n
Going back to our original example…<\/p>\n\n\n
\nSELECT * FROM dbo.Orders\nWHERE ClientID IN (SELECT ClientID FROM #TempClients)\n<\/pre><\/div>\n\n\n
When the query is parsed and bound, the ClientID column mentioned in the subquery does not match any column from any table within the subquery, and hence it\u2019s checked against tables in the outer query, and it does match a column in the orders table. Hence the query essentially becomes<\/p>\n\n\n
\nSELECT * FROM dbo.Orders\nWHERE ClientID IN (SELECT dbo.Orders.ClientID FROM #TempClients)\n<\/pre><\/div>\n\n\n
Which is essentially equivalent to<\/p>\n\n\n
\nSELECT * FROM dbo.Orders\nWHERE 1=1\n<\/pre><\/div>\n\n\n
This is one reason why all columns should always, always, always, be qualified with their tables (or table aliases), especially when there are subqueries involved, as doing so would have completely prevented this problem.<\/p>\n\n\n
\nSELECT * FROM dbo.Orders o\nWHERE o.ClientID IN (SELECT tc.ClientID FROM #TempClients tc)\n<\/pre><\/div>\n\n\n
With the column in the subquery only allowed to be bound to columns within the #TempClients table, the query throws the expected column not found error.<\/p>\n\n\n\n<\/figure>\n\n\n\n
And we\u2019re no longer in danger of deleting everything from the orders table, as we would have if that subquery had been part of a delete and not a select.<\/p>\n","protected":false},"excerpt":{"rendered":"
![\"\"](\"https:\/\/www.sqlinthewild.co.za\/wp-content\/uploads\/2018\/12\/SubqueryMistake1.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.sqlinthewild.co.za\/wp-content\/uploads\/2018\/12\/SubqueryMistake2.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.sqlinthewild.co.za\/wp-content\/uploads\/2018\/12\/SubqueryMistake3-1024x196.png\")
<\/figure>\n\n\n\n