There EXISTS a place where SELECT 1/0 doesn’t return an error.

3

September 7, 2017 by Kenneth Fisher

Kendra Little (b/t) reminded me of this fun little trick (with fairly important ramifications) in her latest quiz on logical joins (Q7)

Using AdventureWorks2014

SELECT *
FROM Sales.SalesOrderHeader as head
WHERE EXISTS (
	SELECT 1/0 as y
	FROM Sales.SalesOrderDetail AS det
	WHERE det.SalesOrderID = head.SalesOrderID);

At this point you are probably expecting something like this:

Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.

And most of the time you will. But the cool thing about this trick is that the field list in an EXISTS statement isn’t actually executed. So query plan for this:

EXISTS (
	SELECT 1/0 as y
	FROM Sales.SalesOrderDetail AS det
	WHERE det.SalesOrderID = head.SalesOrderID);

Is the same as:

EXISTS (
	SELECT *
	FROM Sales.SalesOrderDetail AS det
	WHERE det.SalesOrderID = head.SalesOrderID);

Is the same as:

EXISTS (
	SELECT det.SalesOrderId * det.OrderQty / det.UnitPrice + det.UnitPriceDiscount
	FROM Sales.SalesOrderDetail AS det
	WHERE det.SalesOrderID = head.SalesOrderID);

Or even:

EXISTS (
	SELECT (SELECT TOP 1 AccountNumber FROM Sales.Customer)
	FROM Sales.SalesOrderDetail AS det
	WHERE det.SalesOrderID = head.SalesOrderID);

Using SET STATISTICS IO, TIME ON and Richie Rump’s (b/t) statisticsparser.com. I noticed two important things when running these in queries.

  • The execution and CPU times were almost identical.
  • There was no reference to Sales.Customer in the last query plan or the IO statistics.

 
Ok. So that seems to prove that the field list in the EXISTS isn’t executed. But how about this?

SELECT *
FROM Sales.SalesOrderHeader as head
WHERE EXISTS (
	SELECT Bob
	FROM Sales.SalesOrderDetail AS det
	WHERE det.SalesOrderID = head.SalesOrderID);

Msg 207, Level 16, State 1, Line 44
Invalid column name ‘Bob’.

In fact I can’t think of a query I’ve ever written where this would work. So what does this prove?

SQL Server hates Bob!

No, of course not. What it does mean however is that the field list is still parsed even though it isn’t executed. Bob in this case is a field. So if Sales.SalesOrderDetail had a column called Bob then the query would parse and execute. If you put quotes around it ‘Bob’ then it will parse just fine anyway because it’s now a string. The end result is that if you put a literal, or a valid field, table, query, etc in the field list then it will parse and not execute.

So, want to astonish and confuse your friends? Put 1/0 in your EXISTS subqueries. But I recommend putting in a comment so hey don’t get TOO confused.

SELECT *
FROM Sales.SalesOrderHeader as head
WHERE EXISTS (
	SELECT 1/0 as y /*Yes this works so please don't change/delete.*/
	FROM Sales.SalesOrderDetail AS det
	WHERE det.SalesOrderID = head.SalesOrderID);

3 thoughts on “There EXISTS a place where SELECT 1/0 doesn’t return an error.

  1. Still gets parsed! Damn you forget about that aspect of the QO after a while. Thanks for the memory jog 🙂

  2. gserdijn says:

    “SQL Server hates Bob!”

    Made me chuckle. If that Bob is the grown up version of Bobby Tables, well I completely understand.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,682 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: