SQL query returns different results when index is dropped

Question

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?

Answer 1

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)))