Understanding T-SQL Expression Short-Circuiting

Question

Post reply

Understanding T-SQL Expression Short-Circuiting

Viewing 15 posts - 31 through 45 (of 60 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

CirquedeSQLeil (12/31/2010)
Well done Gianluca

Thank you, Jason.

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

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?

This is the output of my tests on other RDBMSs. I tested Oracle 11gR2, DB2/400 V5R4M0, Firebird 1.5 and PostgreSQL 8.3. I couldn't find a MySQL instance, I was sure we had one, but I couldn't find it.

I executed the following queries to discover the behaviour of the DB engine:

-- Does the engine short-circuit?

SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 1 OR 1 = 1/0;

-- Does the engine detect contraddictions?

SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 1/0 AND 1 = 0;

I didn't have the time to test other particular things, but this is a good starting point.

Here's the results:

DB2 Oracle Firebird PostgreSQL

------------------------ ---- ------ -------- ----------

Short-circuit no yes yes yes

Contraddiction detection no yes yes no

Just a few words on Oracle's contraddiction detection: under some circumstances, the contraddiction is not detected at all. This doesn't surprise me, as Oracle's optimizer is full loaded of bugs (more features, more things that could go wrong). In particular, when the statement text is VERY big and contains a whole lot of literals, the optimizer goes quirks and tries to produce a plan (tries for several minutes...) even if the statement contains a contraddiction. This could mean that the contraddiction detection is applied as one of the last rules, that could make sense since it also checks for constraints contraddiction.

-- Gianluca Sartori

tpbury Grasshopper Points: 20 More actions · Answer 3

Thanks for that - I liked the way you cohesively demonstrated the point at hand. It requires engaging one's brain, but that is why I subscribed to the group!

Thanks again

TAP

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 4

Gianluca Sartori (12/31/2010)
I couldn't find a MySQL instance, I was sure we had one, but I couldn't find it.
I executed the following queries to discover the behaviour of the DB engine:
-- Does the engine short-circuit?
SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 1 OR 1 = 1/0;
-- Does the engine detect contraddictions?
SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 1/0 AND 1 = 0;

Hello Gianluca. First, thanks for your answer about how to update the state of the DB via a CLR Function.

Now, I do have a MySQL instance to test with so I tried this on version 5.0.91 running on Linux and neither statement failed. The first produced rows and the second produced no rows as expected.

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 5

Excellent article Gian.. very precise and elegant...

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

Solomon Rutzky (12/31/2010)
Hello Gianluca. First, thanks for your answer about how to update the state of the DB via a CLR Function.
Now, I do have a MySQL instance to test with so I tried this on version 5.0.91 running on Linux and neither statement failed. The first produced rows and the second produced no rows as expected.

Excellent! Thanks.

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

ColdCoffee (1/1/2011)
Excellent article Gian.. very precise and elegant...

Thank you, Mr. Coffee.

-- Gianluca Sartori

gabriele.acconcia Grasshopper Points: 18 More actions · Answer 8

Interesting article.

What I'll put in my bag is "never rely on short-circuiting in t-sql"!

I would only sign out that I got an error when I tried to execute the following sample from the article:

DECLARE @a int = 1

DECLARE @b-2 int = 0

IF 1/0 = 1 AND @a = @b-2

SELECT 'True'AS result

ELSE

SELECT 'False'AS result

The error was "Cannot assign a default value to a local variable".

I used sqlexpress 2005, and I changed the code this way:

DECLARE @a int

DECLARE @b-2 int

SET @a = 1

SET @b-2 = 0

IF 1/0 = 1 AND @a = @b-2

SELECT 'True'AS result

ELSE

SELECT 'False'AS result

Best regards

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 9

gabriele.acconcia (1/4/2011)
I would only sign out that I got an error when I tried to execute the following sample from the article:
DECLARE @a int = 1
DECLARE @b-2 int = 0

Hello. That syntax actually started in SQL Server 2008. So it makes sense that it would error in SQL Server 2005.

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Bill Kline-270970 SSC Eights! Points: 915 More actions · Answer 10

Great article. It should be read by anyone who codes T-SQL.

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 11

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 SSCertifiable Points: 6877 More actions · Answer 12

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 Valued Member Points: 58 More actions · Answer 13

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/[/url].

spaghettidba SSC Guru Points: 105732 More actions · Answer 14

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/[/url].

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

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

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
SQLkiwi blog
@SQL_Kiwi