March 3, 2011 at 10:16 pm
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
SQLkiwi blog
@SQL_Kiwi
March 4, 2011 at 1:49 am
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
March 4, 2011 at 1:51 pm
Agree it's an edge case & not representative of typical CASE behavior. I just updated my post to clarify that.
March 7, 2011 at 9:08 am
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...)
March 7, 2011 at 10:30 am
Thanks for clarifying, Bart.
I filed a Connect item here:
Let's see what happens.
-- Gianluca Sartori
March 21, 2011 at 7:44 pm
Update to anyone following this: this compile-time exception to normal T-SQL CASE short circuiting[/url] is now scheduled to be fixed in an upcoming SQL release, thanks to Gianluca for filing the Connect bug. For now the problem behavior still exists in SQL2005 through SQL2008R2 -- and maybe SQL2000 -- so keep an eye out for it in existing releases as you use CASE for short-circuiting.
March 22, 2011 at 1:30 am
Thanks for the feedback, Bart.
It's nice to see a problem fixed in such a short time.
-- Gianluca Sartori
October 22, 2011 at 4:59 pm
Nice article and nice work on the floating-point exception bug.
Now, to give this dead horse the final whiplash, I was intrigued by the IN handling and modified the set (30,20,10,1) into (30,20,30,10,1). Sure enough, the IN expression was optimized down to 4 ordered OR subexpressions in the query plan like before. Distinct sort seems to be the easiest way to expand only distinct values in the set into OR expressions. It just happens to also sort the values as a benign side-effect.
June 21, 2013 at 2:58 am
I'm glad SSC republished your fine article, Gianluca.
Nice work, great descussions.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 21, 2013 at 3:02 am
Thanks a lot, Johan.
It's amazing how time passes: it was first published three years ago!
-- Gianluca Sartori
June 21, 2013 at 7:46 am
I must have missed it first time around too. Great article Gianluca, thanks!
June 21, 2013 at 7:47 am
Thanks for writing this very interesting article!
June 21, 2013 at 9:12 am
Very interesting article. I don't think that T-SQL's short-circuiting or lack thereof has ever caused a problem for me. Because most of my coding has been in some version of Visual Basic, which does not short-circuit expressions, if I know that the order of the expressions matters, I tend to write my expressions to avoid problems. For example:
-- IF epressionA AND expressionB THEN Statement1 ELSE Statement2
IF expressionA
BEGIN
IF expressionB
EXEC Statement1
END
ELSE
EXEC Statement2
However, when I am writing in C, I am confident about short-circuiting, so if I have a pointer p that may be NULL, I will happily write:
if (p != NULL && p->field1 == someValue)
June 21, 2013 at 12:31 pm
This was a fantastic article. It was well written, well researched, and useful. Thank you for providing it.
I do have one nitpick and one small thing to add though. You say that tautologies are saying the same thing twice. This is true of rhetorical tautologies. But this is at least highly misleading when talking about logical tautologies. Things like de Morgan's law and the law of the execluded middle carry significant information that is highly useful and while (arguably) each contain within them the same statement twice, the statements as a whole are significant and cannot be stated in any simpler form.
As for the thing to add, towards the end you imply that it is rarely worth explicitly concerning yourself with the short circuiting in SQL Server. I fully agree. But I want to emphasize that this is not true in many procedural languages. In Python I have had cases where I gained very significant speed ups by properly constructing the conditional to take advantage of short circuiting.
It was a really great article, thanks for providing it.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
June 24, 2013 at 1:35 am
Timothy, thanks for your feedback.
I must admit I phrased it with rhetorical tautologies in mind and you're 100% right in your correction.
-- Gianluca Sartori
Viewing 15 posts - 46 through 60 (of 60 total)
You must be logged in to reply to this topic. Login to reply