SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Understanding T-SQL Expression Short-Circuiting


Understanding T-SQL Expression Short-Circuiting

Author
Message
Bill Kline-270970
Bill Kline-270970
SSC Veteran
SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)

Group: General Forum Members
Points: 281 Visits: 730
Great article. It should be read by anyone who codes T-SQL.
Mark Cowne
Mark Cowne
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3087 Visits: 24051
Excellent, really interesting article!

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537




peter-757102
peter-757102
Right there with Babe
Right there with Babe (799 reputation)Right there with Babe (799 reputation)Right there with Babe (799 reputation)Right there with Babe (799 reputation)Right there with Babe (799 reputation)Right there with Babe (799 reputation)Right there with Babe (799 reputation)Right there with Babe (799 reputation)

Group: General Forum Members
Points: 799 Visits: 2559
To make matters worse:

Sometimes an expression in a select clause will be evaluated before filtering expressions in the where clause.

This means that if we have an expression in the select clause that performs a division, where the right side value is queried from a table and a where clause exists to filter any zero's out, it can still go wrong due to a divide by 0!

In such a case you need to harden your select expression by using a case construct to filter out the 0 values before doing the division. It does not matter what the result of the expression is in such a case, as the where clause will filter out the result afterwards anyway.

I doubt many SQL statements in existence that involve such sensitive expressions (and there are quite a few) are in fact hardened. I say this foremost as nearly all of the time code works just fine without, but then it can suddenly break after years of fine operation as some unseen threshold is reached. The second reason is that it generates complicated hard to maintain code, not to mention that it also works slower.

I also think few wil be aware of how far the freedom of execution order in SQL stretches. Personally I think it goes too far as it results in unreliable code or complicated code and thus is counter productive to the things we all want. At a minimum where clause filtering should always happen before select expressions to provide a simple model that is just as good 99.9% of the time anyway.
Bart Duncan
Bart Duncan
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 21
This post seems to recommend CASE as a way to get deterministic short circuiting in T-SQL. Unfortunately, even CASE does not always provide deterministic order of evaluation with short circuiting. See http://bartduncansql.wordpress.com/2011/03/03/dont-depend-on-expression-short-circuiting-in-t-sql-not-even-with-case/.
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9790 Visits: 13349
Bart Duncan (3/3/2011)
This post seems to recommend CASE as a way to get deterministic short circuiting in T-SQL. Unfortunately, even CASE does not always provide deterministic order of evaluation with short circuiting. See http://bartduncansql.wordpress.com/2011/03/03/dont-depend-on-expression-short-circuiting-in-t-sql-not-even-with-case/.


Nice catch, Bart.
It looks like that behaviour is determined by the fact that the code executes inside a TVF.

Try this:


-- Autonomous T-SQL batch: everything runs just fine
DECLARE @input int
SELECT @input = 0
SELECT calculated_value =
CASE
WHEN @input <= 0 THEN 0
ELSE LOG10 (@input)
END

-- Scalar function: runs fine
CREATE FUNCTION dbo.test_case_short_circuit2 (@input INT)
RETURNS int
AS BEGIN
RETURN (
SELECT calculated_value =
CASE
WHEN @input <= 0 THEN 0
ELSE LOG10 (@input)
END
)
END
GO

SELECT dbo.test_case_short_circuit2 (-1);
GO



I think it should be reported on connect as a bug. BOL is quite clear on that point.
Thanks for sharing

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15544 Visits: 11354
Gianluca Sartori (3/3/2011)
It looks like that behaviour is determined by the fact that the code executes inside a TVF.

It is constant-folding at work. If you replace the literal constant zero with a variable, the problem no longer occurs. SQL Server expands the in-line TVF at optimization time and fully evaluates the CASE with the constant values available.

I think it should be reported on connect as a bug. BOL is quite clear on that point.

I agree. Constant-folding should never cause an error condition (such as an overflow) at compilation time - there have been other bugs in this area fixed for the same reason. Bart, if you put this on Connect, please leave a link here so I can vote for it.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15544 Visits: 11354
Just to be clear, Bart's example is compiled as:


SELECT CASE WHEN 0 <= 0 THEN 0 ELSE LOG10(0) END;



..which also gives the error (when it should not). Reproduces on 2005 (build 0.05254) and 2008 (build 10.0.4272) for me.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9790 Visits: 13349
SQLkiwi (3/3/2011)
Just to be clear, Bart's example is compiled as:


SELECT CASE WHEN 0 <= 0 THEN 0 ELSE LOG10(0) END;



..which also gives the error (when it should not). Reproduces on 2005 (build 0.05254) and 2008 (build 10.0.4272) for me.


And reproduced on 2005 SP3 (9.00.4035.00 X64) and 2008 R2 RTM (10.50.1734.0 Intel X86) for me.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Bart Duncan
Bart Duncan
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 21
Agree it's an edge case & not representative of typical CASE behavior. I just updated my post to clarify that.
Bart Duncan
Bart Duncan
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 21
FWIW I think CASE's implementation actually does guarantee both order of execution and predictable short circuiting at execution time. That doesn't prevent this error, though, because in this case the error is occurring at compile time, not execution time. As you mentioned, Paul, this is the result of compile-time constant folding: the error occurs when the optimizer tries to evaluate "LOG10(@input)" at compile time to replace it with a constant.

It’s not entirely clear to me whether compile-time simplifications like this one are expected to be blocked so that they don't effectively circumvent CASE's short circuiting behavior. But FWIW to me it does seem like a bug, and I already filed an internal workitem requesting that someone on the QO team investigate it. (But don't let that dissuade you from filing a Connect bug if you feel strongly that this should be fixed -- most of the time community-submitted bugs get more weight than an equivalent request filed by someone internal at MS...)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search