Thursday, May 26, 2011

A Bug Using INTERSECT and ORDER BY

I was teaching a TSQL course to Total Jobs Group in London, UK, and we stumbled on the following bug. This script uses the Northwind sample database on SQL Server 2008 R2.

use northwind

select contactName, Phone
from customers
intersect
select lastname + ', ' + firstname
, homephone
from employees
order by contactName

If you execute this statement with INTERSECT and ORDER BY, you get the following error:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

If you remove the ORDER BY, it works just fine. This is definitely a bug and I found that it was reported on Connect. I'll test this out to see if they've fixed this in Denali.