Understanding T-SQL Expression Short-Circuiting

Question

Understanding T-SQL Expression Short-Circuiting

Viewing 15 posts - 16 through 30 (of 60 total)

You must be logged in to reply to this topic. Login to reply

sknox SSChampion Points: 12388 More actions · Answer 1

magarity kerns (12/30/2010)
Excellent article - For more fun, check other DBMSes. I checked on Oracle and "select 'A' from dual where 1=0 or 1/0 = 1;" gives a division by zero error. (although it may need to be in a procedure on Oracle to do it with the IF statement). Anyone have DB/2 or Teradata handy?

You did the wrong test. You should have done "where 1=0 AND 1/0 = 1" or "where 1=1 or 1/0 = 1". Remember: AND short-circuits on false, OR short-circuits on true.

I am, however, interested in what you find out -- and we shouldn't necessarily limit the testing to DBMSes -- any system or language that evaluates boolean expressions can be tested for this behavior.

Paul White SSC Guru Points: 150467 More actions · Answer 2

sknox (12/30/2010)
I know how to write an XOR using AND/OR/NOT.

Yes, but Gianluca wasn't to know that. You might have been asking a sensible question, rather than just making the point that in the sentence "Any boolean expression is capable of being short-circuited, in the right circumstances." it would have been more accurate to say 'many' rather than 'any'...

But while you can write it, you can't short-circuit it:
(first_name IS NULL AND middle_name IS NOT NULL) OR (first_name IS NOT NULL AND middle_name IS NULL)
In that code, both first_name and middle_name have to be evaluated. First we must evaluate first_name. If it's not NULL, then we can, yes, ignore middle_name here and short-circuit the first AND. But then we return false to the first part of the OR so we must evaluate the second part. Since first_name is not NULL, we know we must evaluate the second part of second AND, which evaluates middle_name. So you have to evaluate both sides of the XOR.
You can reorder the AND and OR operators, but since the two sides are mutually exclusive, you will always have to evaluate both of the original expressions. So not all boolean expressions can be short-circuited.

So, you're saying that an XOR written in T-SQL can be short-circuited? Or just a bit? 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

sknox (12/30/2010)
So you have to evaluate both sides of the XOR.

When first name is null and middle name is not null, the second part of the or expression does not get evaluated.

-- Gianluca Sartori

Daniel Ruehle SSC-Addicted Points: 411 More actions · Answer 4

sknox (12/30/2010)
Daniel Ruehle (12/30/2010)
... case when Age > 90 then 1 when Age < 5 then 0 when Gender = 'Male' then 1 when LastName like 'SAM%' then 1 else 0
end = 1
This gets records for all people over the age of 90, males of age 5 or more and anyone with a last name that starts with the letters SAM.
Not quite. This gets records for all people over the age of 90, males of age 5 or more, and anyone age 5 or more with a last name that starts with the letters SAM. This will not retrieve a record for someone under age 5 with a last name starting with SAM.
Your point about using CASE for explicit short-circuiting is good, but your explanation is a perfect example of how careful you have to be when using CASE, for the same reason.

Yes indeed, you need to be VERY careful when using this, especially when using an exclusionary case. With great power comes great responsibility!

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

LutzM (12/30/2010)
Gianluca, thanx for sharing an EXCELLENT article!!!

Thanks, Lutz.

-- Gianluca Sartori

magarity kerns SSCrazy Points: 2763 More actions · Answer 6

You did the wrong test. You should have done "where 1=0 AND 1/0 = 1" or "where 1=1 or 1/0 = 1". Remember: AND short-circuits on false, OR short-circuits on true.

Oops, you're right... In fixing it I discovered that Oracle still gives a divide by zero error for "select 'A' from dual where 1=0 and 1/0 = 1;" but SQL Server behaves as described in the article. Perhaps Oracle can handle this in an IF statement when using a stored procedure?

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

magarity kerns (12/30/2010)
Excellent article - For more fun, check other DBMSes. I checked on Oracle and "select 'A' from dual where 1=0 or 1/0 = 1;" gives a division by zero error. (although it may need to be in a procedure on Oracle to do it with the IF statement). Anyone have DB/2 or Teradata handy?

Nice idea. Tomorrow I can try on Oracle, DB2, Firebird, MySql and Postgres.

-- Gianluca Sartori

magarity kerns SSCrazy Points: 2763 More actions · Answer 8

Check this out: My friendly local Oracle DBA told me Oracle is backwards!

select 'A' from dual where 1/0 = 1 AND 1=0;

(at least on Oracle 10g) will give an empty result instead of an error. I tested this and it works in this order.

spaghettidba SSC Guru Points: 105732 More actions · Answer 9

Daniel Ruehle (12/30/2010)
Agree, but if the query plan says its going to scan the table, then it does come down to how long does it take to process each row. Since you aren't guaranteed the order that SQL Server will evaluate the conditions when just using boolean logic, it can choose to do then in an inefficient manner, which I believe was the jist of the article. In scenarios where it might matter, this gives you absolute control the order.

Agreed.

With lots of rows and lots of WHEN branches it does indeed make a difference. Generally speaking (some million rows and at most 10 WHEN bracnhes) it isn't even noticeable.

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 10

Solomon Rutzky (12/30/2010)
Gianluca Sartori (12/30/2010)
As a side note, a CLR function can update data. 😉
Hello Gianluca. Thanks for a great and thorough article :).
This is slightly off topic, but how do you get a CLR Function to be able to alter the state of the DB? I have always seen this error:
System.Data.SqlClient.SqlException: Invalid use of a side-effecting operator 'INSERT' within a function.
I certainly don't think this is a good idea (to alter the state of the DB in a function), but you mention it can be done so I was curious.
Take care,
Solomon...

Thank you, Solomon. To circumvent that limitation, you can update the database using a connection different from the context one. To do so, you have to give external access rights to the assembly.

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 11

magarity kerns (12/30/2010)
Check this out: My friendly local Oracle DBA told me Oracle is backwards!
select 'A' from dual where 1/0 = 1 AND 1=0;
(at least on Oracle 10g) will give an empty result instead of an error. I tested this and it works in this order.

Also Oracle has a contraddiction detection feature in the optimizer. Your 1 = 0 gets intercepted by that feature and èrevents the statement from failing.

-- Gianluca Sartori

sknox SSChampion Points: 12388 More actions · Answer 12

SQLkiwi (12/30/2010)
sknox (12/30/2010)
I know how to write an XOR using AND/OR/NOT.
Yes, but Gianluca wasn't to know that. You might have been asking a sensible question, rather than just making the point that in the sentence "Any boolean expression is capable of being short-circuited, in the right circumstances." it would have been more accurate to say 'many' rather than 'any'...

Yes but my question was on short-circuiting an XOR, not on writing one in SQL.

But while you can write it, you can't short-circuit it:
(first_name IS NULL AND middle_name IS NOT NULL) OR (first_name IS NOT NULL AND middle_name IS NULL)
In that code, both first_name and middle_name have to be evaluated. First we must evaluate first_name. If it's not NULL, then we can, yes, ignore middle_name here and short-circuit the first AND. But then we return false to the first part of the OR so we must evaluate the second part. Since first_name is not NULL, we know we must evaluate the second part of second AND, which evaluates middle_name. So you have to evaluate both sides of the XOR.
You can reorder the AND and OR operators, but since the two sides are mutually exclusive, you will always have to evaluate both of the original expressions. So not all boolean expressions can be short-circuited.
So, you're saying that an XOR written in T-SQL can be short-circuited? Or just a bit? 😉

Absolutely NOT. The two sides of the XOR in this case are first_name and middle_name. Both of those must be evaluated in all cases. While some subsets of the expanded boolean expression can be short-circuited, you have to first expand the XOR to ( A AND NOT B) OR (B AND NOT A), and then evaluate both A and B, and then you can short-circuit some of the expanded logic, but you're still evaluating both sides of the original XOR.

sknox SSChampion Points: 12388 More actions · Answer 13

Gianluca Sartori (12/30/2010)
sknox (12/30/2010)
So you have to evaluate both sides of the XOR.
When first name is null and middle name is not null, the second part of the or expression does not get evaluated.

So you short-circuited the OR, but at the expense of not short-circuiting the AND, and more to the point, in the original XOR (first_name is null XOR middle_name is null) both sides still had to be evaulated.

In the end, you evaluated two boolean expressions, and you cannot properly perform an XOR without evaluating at least two boolean expressions. You can expand it to more expressions for languages that don't have a built-in XOR, but at least two will have to be evaluated, and they will correlate either directly or inversely to the two conditions in the original XOR.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

Well done Gianluca

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

spaghettidba SSC Guru Points: 105732 More actions · Answer 15

sknox (12/30/2010)
Gianluca Sartori (12/30/2010)
sknox (12/30/2010)
So you have to evaluate both sides of the XOR.
When first name is null and middle name is not null, the second part of the or expression does not get evaluated.
So you short-circuited the OR, but at the expense of not short-circuiting the AND, and more to the point, in the original XOR (first_name is null XOR middle_name is null) both sides still had to be evaulated.
In the end, you evaluated two boolean expressions, and you cannot properly perform an XOR without evaluating at least two boolean expressions. You can expand it to more expressions for languages that don't have a built-in XOR, but at least two will have to be evaluated, and they will correlate either directly or inversely to the two conditions in the original XOR.

Well, I must be wrong then. Have you seen my avatar picture? It should remind people (and myself) that I'm just throwing in my two cents. 🙂

However, the main point I'm trying to make in the article is that short-circuiting should not be considered part of a database developer's toolbag. Whether XOR shortcircuits entirely or partially should not be a concern, as for any other logical expression. This is the concept I wanted you to get away with. I hope I didn't fail at that.

Thanks for your valuable feedback.

-- Gianluca Sartori