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


Division by zero on a SQL Instance while a different Instance run correctly


Division by zero on a SQL Instance while a different Instance run correctly

Author
Message
clearbystander
clearbystander
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 20
Hi

I run the same query against two DB, one is the publisher and other is the subscriber.
The two DB should be identical but the subscriber raise the "Divide By Zero" error while the publisher return the correct result.
Of course.... NONE OF THE RETURNED ROWS HAVE A ZERO in the relevant fields!!!
[w00t]
This is the query

SELECT
CASE
WHEN
BAL_IN.FK_CURR = 'EUR' then 1.0 -- If the currency is EURO the exchange rate is one
ELSE -- Otherwise calculate it.
BAL_IN.LEDGER_BAL_ORG_AMT/ BAL_IN.AC_FUNC_AMT -- HERE RAISE THE DIVIDE BY ZERO ERROR
END AS Exchange_Rate ,

CASE
WHEN
BAL_IN.FK_CURR = 'EUR' THEN DET_IN.DTL_AMT
ELSE
DET_IN.DTL_AMT/(BAL_IN.LEDGER_BAL_ORG_AMT/BAL_IN.AC_FUNC_AMT) -- This statement is similar but DO NOT Raise the Error(!!?)
END AS Exchanged_amount,

BAL_IN.AC_FUNC_AMT ,
BAL_IN.LEDGER_BAL_ORG_AMT,
DET_IN.DTL_AMT,

FROM
SICSNTPC.AC_LEDGER_DETAIL AS DET_IN ,
SICSNTPC.AC_LEDGER_BALANCE AS BAL_IN
WHERE
Where conditions......

I've found a kind of bypass using this statement
WHEN (ABS(BAL_IN.AC_FUNC_AMT)) <> 0 then ABS(BAL_IN.LEDGER_BAL_ORG_AMT)/ ABS(BAL_IN.AC_FUNC_AMT)

The problem has appeared lately but I can't imagine the origin of that.
Someone has ideas of what could be the reason of this disconcerting behaviour ?

Many thanks for the help
[Smile]

Giuliano

PS: Copied from a post on Replication forum because the subject is more related to the programming
John Rees
John Rees
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 539
A possible reason for this error is that the division expressions are being evaluated for some rows before those rows are excluded by the WHERE clause. The same problem is often observed when CASTing datatypes, and using a WHERE clause to filter only those rows where the CAST would succeed.

This can happen because the optimiser is free to evaluate your SELECT expressions either before or after filtering by the WHERE clause. Even if the data in your two databases is the same, other factors (indexing, server resources...) may affect the optimser.

Your solution of rewriting the expression is probably the best way, since it is tricky (impossible??) to be sure what the optimiser will do.

More info:

T-SQL functions do no imply a certain order of execution
On SQL Server boolean operator short-circuit
clearbystander
clearbystander
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 20
Hi John

I think you got the target! :-)

A possible reason for this error is that the division expressions are being evaluated

for some rows before those rows are excluded by the WHERE clause.


I've searched in the table if there are rows where this field is Zero and, surprise surprise, there are some!!
There should not be zero values there but there are !! Angry

A curiosity : In my case this strange behaviour helps to find a potential data issue but speaking in general term
shouldn't it be considered an error of the DB Engine ?

Many thanks for the kind help
:-)
John Rees
John Rees
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 539
Hi

A curiosity : In my case this strange behaviour helps to find a potential data issue but speaking in general term shouldn't it be considered an error of the DB Engine ?


That's exactly what I thought when I first came across this a few years ago! However, SQL is designed as a declarative language so that the statements you write only really define what data you want to manipulate, but intentionally do not define HOW the database should do it.

This has huge advantages in terms of optimisation, but means that you cannot make any assumptions about the order of execution of the logic of your statements.

This 'undefined behaviour' even goes so far that you cannot rely on an expression like
(a = b) and (c = d)
being evaluated left to right and short-circuited. If you must define the order of evaluation, the normal approach is using CASE (as you did)

SELECT * FROM t
WHERE (a<>0) and (b/a=2) -- Might fail with divide by zero

-- could be rewritten as

SELECT * FROM t
WHERE (CASE WHEN a<>0 THEN b/a ELSE null END) = 2



Best description of this I've found is in the Short Circuit article by Itzik Ben-Gan (whose stuff is always worth reading).
clearbystander
clearbystander
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 20
Hi John

I've read the article you mentioned and it helps to clarify the matter..... really the author talks about the order of the evaluation of the CASE statement and never mention that the CASE in the SELECT is evaluated before the WHERE condition is applied.

But, as told before, to me is sufficient that some of the denominators are zero to indicate that the data are not correct, so I will correct the data anyway.

Many thanks for your precious help.
Have a nice day!

:-)
odepriester
odepriester
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 21
btw, wouldn't it be better writing your example query more like :

SELECT * FROM t
WHERE (a<>0) and (b= 2 * a)


?

It will avoid the "CASE" term
John Rees
John Rees
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 539
odepriester (9/6/2013)
btw, wouldn't it be better writing your example query more like :

SELECT * FROM t
WHERE (a<>0) and (b= 2 * a)


?

It will avoid the "CASE" term


Sometimes a rewrite like that would work, but if 'a=2' and 'b=5' are integers, then

b/a = 2
but
b <> 2*a
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