Catch That Bug!

February's Bug Solution: From the Customers and Orders tables that you create and populate by running the code in Listing A, you need to return the details of customers who made orders.You build the following query to return the details:

SELECT custid, custname 
FROM dbo.Customers 
WHERE custid IN(SELECT custid 
  FROM dbo.Orders); 

Given the sample data in the Customers and Orders tables, you probably expect your query to return customers A and B. Instead, you get back all customers, including those who made no orders. Why?

The bug here is elusive. Look carefully at the definition of both tables in Listing A. Notice in the Customers table that the column for customer ID is called custid, but the Orders table calls this column customerid. So, the query just given looks for a column name, custid, that doesn't exist in Orders.

With this discrepancy in column names, why does the query return all customers rather than producing an error that the custid column doesn't exist in Orders? If you run only the inner query

WHERE custid IN(SELECT custid 
  FROM dbo.Orders); 

you'll get the expected error. But SQL Server resolves query attributes beginning from the current level and working outward. So, SQL Server first looks for a column called custid in Orders and doesn't find one. Then, it looks for custid in the outer level (Customers) and finds it. Thus, unintentionally, the query becomes a correlated one, as if you had written:

SELECT custid, custname 
FROM dbo.Customers 
WHERE custid IN(SELECT 
  Customers.custid 
  FROM dbo.Orders); 

Now the reason for the unexpected result is clear: The filter ends up comparing the value to itself, returning TRUE. A best practice that can help you avoid such bugs is to always table-qualify columns in queries, even when the queries are self-contained. For example, if you had written the original query as follows, SQL Server would have trapped the error at resolution time, letting you know that Orders doesn't have a custid column:

SELECT custid, custname 
FROM dbo.Customers AS C 
WHERE custid IN(SELECT O.custid 
  FROM dbo.Orders AS O); 

You could have then changed the query to

SELECT custid, custname 
FROM dbo.Customers AS C 
WHERE custid IN(SELECT 
  O.customerid FROM dbo.Orders 
  AS O); 

This query correctly returns customers A and B.

March's Bug: The following query returns the maximum OrderID from the Orders table in the Northwind database:

SELECT MAX(OrderID) FROM 
  dbo.Orders 

And SQL Server's system stored procedure sp_who returns current users and processes. You use the following code to try to run both statements in the same batch:

SELECT MAX(OrderID) FROM 
 dbo.Orders 
sp_who 

However, you get only the max OrderID from Orders.You don't receive any output from sp_who—not even an error. It seems that only the first line of code ran and that sp_who wasn't invoked at all. Can you find the bug in this code?