Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»»

Understanding T-SQL Expression Short-Circuiting Expand / Collapse
Author
Message
Posted Tuesday, January 4, 2011 10:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:26 PM
Points: 235, Visits: 720
Great article. It should be read by anyone who codes T-SQL.
Post #1042576
Posted Wednesday, January 5, 2011 6:57 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
Excellent, really interesting article!

____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1043021
Posted Thursday, January 6, 2011 6:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 4:55 AM
Points: 323, Visits: 2,198
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.
Post #1043697
Posted Thursday, March 3, 2011 12:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 2:29 PM
Points: 6, 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/.
Post #1072858
Posted Thursday, March 3, 2011 2:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:31 AM
Points: 5,014, Visits: 10,514
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1072939
Posted Thursday, March 3, 2011 10:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:21 PM
Points: 11,192, Visits: 11,090
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1073068
Posted Thursday, March 3, 2011 10:16 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:21 PM
Points: 11,192, Visits: 11,090
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1073070
Posted Friday, March 4, 2011 1:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:31 AM
Points: 5,014, Visits: 10,514
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1073136
Posted Friday, March 4, 2011 1:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 2:29 PM
Points: 6, Visits: 21
Agree it's an edge case & not representative of typical CASE behavior. I just updated my post to clarify that.
Post #1073563
Posted Monday, March 7, 2011 9:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 2:29 PM
Points: 6, 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...)
Post #1074235
« Prev Topic | Next Topic »

Add to briefcase «««34567»»»

Permissions Expand / Collapse