Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In SQL Server 2000 SP4, I have a query

SELECT <fields no aggregates>
  FROM PS_VED_VCH_TAO5 A  
    ,PS_BUS_UNIT_TBL_AP B  
    ,PS_RT_DFLT_VW C  
    WHERE   A.PROCESS_INSTANCE = 594170  
    AND A.VAT_CALC_AMT <> 0  
    AND A.VAT_ENTRD_AMT <> 0  
    AND A.VAT_ENTRD_AMT <> A.VAT_CALC_AMT  
    AND A.BUSINESS_UNIT = B.BUSINESS_UNIT  
    AND A.VCHR_SRC <> 'PROC'  
    AND B.VCHR_VAT_MSG_OPT = 'E'  
    AND A.VAT_CALC_TYPE = 'E'  
    AND A.VAT_RCRD_INPT_FLG = 'Y'  
    AND A.VAT_RCRD_OUTPT_FLG = 'N'  
    AND C.FROM_CUR = A.TXN_CURRENCY_CD  
    AND (
            (C.TO_CUR = A.VAT_TOL_CUR_CD AND C.RT_TYPE = A.VAT_TOL_RT_TYPE)  
            OR 
            (C.TO_CUR = A.TXN_CURRENCY_CD  AND C.RT_TYPE = A.RT_TYPE  AND A.VAT_TOL_CUR_CD = ' '  AND A.VAT_TOL_RT_TYPE = ' ')
        )  
        AND C.EFFDT = 
        (  
            SELECT MAX(I.EFFDT)  
                FROM PS_RT_DFLT_VW I  
                    WHERE I.FROM_CUR = C.FROM_CUR  
                        AND I.TO_CUR = C.TO_CUR  
                        AND I.RT_TYPE = C.RT_TYPE  
                        AND I.EFFDT <= A.INVOICE_DT
        )  
        AND 
        (
            ((A.VAT_TOL_AMT < (((((A.VAT_ENTRD_AMT - A.VAT_CALC_AMT)) / ( C.RATE_DIV))) * ( C.RATE_MULT)))  
            OR 
            (A.VAT_TOL_AMT < (((((((A.VAT_ENTRD_AMT - A.VAT_CALC_AMT)) / ( C.RATE_DIV))) * ( C.RATE_MULT))) * ( -1)))
        ) 
        AND 
        (
            (A.VAT_TOL_PCT < (((((A.VAT_ENTRD_AMT - A.VAT_CALC_AMT)) / ( A.VAT_CALC_AMT))) * ( 100)))  
            OR 
            (A.VAT_TOL_PCT < (((((((A.VAT_ENTRD_AMT - A.VAT_CALC_AMT)) / ( A.VAT_CALC_AMT))) * ( 100))) * ( 1)))
            )
        )

PS_BUS_UNIT_TBL_AP has a unique clustered index on it. If the index is present, the above query results in a division by zero in

(A.VAT_TOL_PCT < (((((((A.VAT_ENTRD_AMT - A.VAT_CALC_AMT)) / ( A.VAT_CALC_AMT))) * ( 100))) * ( 1)))

despite the fact that the query explicitly excludes rows where VAT_CALC_AMT is zero. If the index is dropped it doesn't ( and the query plan changes). Also, if the query is changed to add a bogus condition "A.OPERATORNAME <> 'Non existent user'", it also works normally.

is this a bug in SQL or is something else going on?

share|improve this question
3  
Long story short: don't rely on short-circuiting. Just because something is eliminated in a where clause doesn't mean the where clause is evaluated first. –  Aaron Bertrand Oct 30 '12 at 3:11
add comment

1 Answer 1

up vote 3 down vote accepted

When you create/drop the index, you also affect SQL Server Query Optimizer's plan created to service your query.

Have a look at this Connect item.

Basically, SQL Server is free to optimise the expressions in the SELECT clause prior to applying the WHERE conditions. Not very intuitive, but someone decided it should do so.

Normally, to avoid divide by zero errors, I throw a NULLIF in there which is sufficient to get past the error while not really affecting the result (since the WHERE clause actually prunes them).

e.g.

(A.VAT_TOL_PCT < (((((((A.VAT_ENTRD_AMT - A.VAT_CALC_AMT)) / ( NULLIF(A.VAT_CALC_AMT,0)))) * ( 100))) * ( 1)))
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.