Conditional Evaluation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Conditional Evaluation

Author

Topic

paulmrice
Starting Member

3 Posts

Posted - 2010-01-26 : 11:53:00

I'm getting some strange behavior that I'm hoping one of you gurus can explain or perhaps help me find an alternate syntax. The queyr below is a similar query to one that is being run in our software.

SELECT * FROM DocumentIndex
WHERE IndexTypeID = 100011
and
(
((IndexTypeID = 100011) AND (dbo.udf_CheckIndexMatch ('9','9') > 0 ))
OR
((IndexTypeID = 100023) AND 1/0 = 0)
)

When I run this query, I get
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Obviously in our software we are not using the division by zero but it demonstrates the problem. Based on the first condition of the WHERE clause this query should only be processing records with an IndexTypeID = 100011. So why is it still processing the 1/0 when based on the rest of the WHERE clause, we know that IndexTypeID does not equal 100023.

I'm assuming that there is some order in which the query engine is processing each part of this query so it must be performing the OR section prior to doing the initial filter for IndexTYpeID = 100011. How can I force the query to do this filter first?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 11:56:36

you need to fix the bit in where condition which causes the division by zero may be by using case... when

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 11:57:09

I can't remember the name for the explanation for this =- some buzzword, or other, but SQL Server must make a Query Plan that can be reused by other queries that come later ... and it will assume that both sides of the OR are equally valid, and thus will attempt to find a suitable query plan that satisfies "all comers"

At least that's what I think happens ...

paulmrice
Starting Member

3 Posts

Posted - 2010-01-26 : 12:25:15

I'm trying out the "CASE ... WHEN " now, seems a reasonable solution. Interesting note though, if I modify the query to this, it evaluates correctly:

DECLARE @Value INT
SELECT @Value = 1

SELECT * FROM DocumentIndex
WHERE IndexTypeID = 100011
and
(
((IndexTypeID = 100011) AND (dbo.udf_CheckIndexMatch ('9','9') > 0 ))
OR
((IndexTypeID = 100023) AND @Value/0 = 0)
)

My guess is the optimizer decides when it's 1/0 that it's easiest to go ahead and process that first. But when it has to go look up a value, then it evaluate the condition from left to right.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 12:31:14

quote:
Originally posted by paulmrice

I'm trying out the "CASE ... WHEN " now, seems a reasonable solution. Interesting note though, if I modify the query to this, it evaluates correctly:

DECLARE @Value INT
SELECT @Value = 1

SELECT * FROM DocumentIndex
WHERE IndexTypeID = 100011
and
(
((IndexTypeID = 100011) AND (dbo.udf_CheckIndexMatch ('9','9') > 0 ))
OR
((IndexTypeID = 100023) AND @Value/0 = 0)
)

My guess is the optimizer decides when it's 1/0 that it's easiest to go ahead and process that first. But when it has to go look up a value, then it evaluate the condition from left to right.

still not a good way. why does 0 come hardcoded in denominator? isnt there an actual expression?

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 12:34:43

Yup ... there are issue surrounding "Parameter Sniffing" that the query optimiser does that means that I expect that could happen.

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 12:39:03

"isnt there an actual expression?"

Good point.

It could evaluate to a CASE statement : CASE WHEN 0 = 0 THEN NULL ELSE @value / 0 END

e.g. if the value was 5 then it would evaluate to:

CASE WHEN 5 = 0 THEN NULL ELSE @value / 5 END

but even so I think that the (unreachable) ELSE with the divide-by-zero might get you into trouble. I think the thing doing the evaluation is going to have to detect that zero is the denominator and take evasive action ...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-01-26 : 14:00:17

I'm not sure if this adds to the actual discussion since I'm not entirely sure what you are trying to accomplish. But, perhaps, this is a case where you need to program defensively and use a NULLIF???

SELECT * FROM DocumentIndex
WHERE IndexTypeID = 100011 
and
(
((IndexTypeID = 100011) AND (dbo.udf_CheckIndexMatch ('9','9') > 0 ))
OR 
((IndexTypeID = 100023) AND 1 / NULLIF(0, 0) = 0)
)

or maybe

SELECT * FROM DocumentIndex
WHERE IndexTypeID = 100011 
and
(
((IndexTypeID = 100011) AND (dbo.udf_CheckIndexMatch ('9','9') > 0 ))
OR 
((IndexTypeID = 100023) AND COALESCE(1 / NULLIF(0, 0), 0) = 0)
)

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 14:22:12

NullIf() is a good idea, I like that - particularly for "Should never happen, FAIL if it happens". I've got CASE statements in my code to guard against will-never-happen divide by zero errors, Nullif() would do as good a job, and with less intrusion to Query Plan I reckon.

paulmrice
Starting Member

3 Posts

Posted - 2010-01-26 : 14:55:43

I thank you guys for the help and apologize for the confusion. The Divide by Zero itself was not the error that I was trying to prevent, it was merely a placeholder in the code which would throw an error if it was executed. In my original statement I attempted to make that point with "we are not using the division by zero but it demonstrates the problem" but I know those words get lost once you go through this many replies.

In my true scenario, replace the 1 /0 with a function call. Inside the function we pass in VARCHAR(100) values and compare them based on a data type and perform conversions. i.e., if we were intending to do a date conversion, we passed in the string value, converted it to a datetime and then compared it against datetime. In my example above, values with IndexTypeID = 100011 were not date values, but IndexTypeID = 100023 were. However, because the query was running the second part of this clause "((IndexTypeID = 100023) AND dbo.IndexValueCompare(@DataFormat, IndexValue) = 0)" non-date values were attempting to convert to datetime and thus I was failing. Again, based on my other part of the WHERE clause "WHERE IndexTypeID = 100011" I was not expecting that second part to be evaluated. Just thought the divide by zero example would be easier to follow and point out the confusion about how it was continuing to evaluate even after the first condition should have failed.

Since then, I have completely overhauled my function to handle this situation and I seem to be able to work around the problem. Thanks again for all the assistance!

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 16:25:06

Well I've benefited from learning that I can use NullIf() as a test for divide-by-zero instead of using a more costly CASE statement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources