SQL puzzle

From: Gail Binkley <gail_at_alberich.Stanford.EDU>
Date: 23 Jan 2001 19:44:43 GMT
Message-ID: <94kmvb$p6l$1@nntp.Stanford.EDU>

Hi all,

We've stumbled across a SQL puzzle that I was hoping that someone from the news group could clarify. Basically, we don't understand why one query works and another doesn't.

Statement 1 works:

SELECT LOG_RAT2N_MEAN, SPOT
FROM RESULT
WHERE EXPTID = 918
and (

(CH2IN_MEAN/CH2BN_MEDIAN >= 2 AND CH2BN_MEDIAN > 0)

      AND


     (CH1I_MEAN/CH1B_MEDIAN >= 2 AND CH1B_MEDIAN > 0)

    )
and LOG_RAT2N_MEAN IS NOT NULL;

Statement 2 fails with a divison error:

SELECT LOG_RAT2N_MEAN, SPOT
FROM RESULT
WHERE EXPTID = 918
and (

(CH2BN_MEDIAN > 0 AND CH2IN_MEAN/CH2BN_MEDIAN >= 2)

      AND 


     (CH1B_MEDIAN > 0 AND CH1I_MEAN/CH1B_MEDIAN >= 2)

    )
and LOG_RAT2N_MEAN IS NOT NULL;

ERROR:
ORA-01476: divisor is equal to zero

The data is such that there are no 0 values for CH2BN_MEDIAN, but there are 0 values for CH1B_MEDIAN . It is as if the CH1B_MEDIAN > 0 clause in the query 2 is ignored. Both queries give the same explain plan:

QUERY_PLAN

---

SELECT STATEMENT Cost = 217
  2.1 TABLE ACCESS BY LOCAL INDEX ROWID RESULT      3.1 INDEX RANGE SCAN RESULT_EXPTID_FK_I NON-UNIQUE To further complicate this matter, if you change one of ANDs to an OR, we get the opposite result.

Statement 3 works:

SELECT LOG_RAT2N_MEAN, SPOT
FROM RESULT
WHERE EXPTID = 918
and (

(CH2BN_MEDIAN > 0 AND CH2IN_MEAN/CH2BN_MEDIAN >= 2)

      OR 


     (CH1B_MEDIAN > 0 AND CH1I_MEAN/CH1B_MEDIAN >= 2)

     )

and LOG_RAT2N_MEAN IS NOT NULL;

Statement 4 failed with ORA-01476 error again:

SELECT LOG_RAT2N_MEAN, SPOT
FROM RESULT
WHERE EXPTID = 918
and (

(CH2IN_MEAN/CH2BN_MEDIAN >= 2 AND CH2BN_MEDIAN > 0)

      OR 


     (CH1I_MEAN/CH1B_MEDIAN >= 2 AND CH1B_MEDIAN > 0)

     )

and LOG_RAT2N_MEAN IS NOT NULL;

Thanks for any help you might be able to shed on this puzzle.

Gail Binkley
Stanford University
gail_at_genome.stanford.edu

-- 
Department of Genetics        Phone: (650) 498-7145
School of Medicine              Fax: (650) 723-7016
Stanford University           Email: gail_at_genome.stanford.edu
Stanford, CA 94305-5120         URL: http://genome-www.stanford.edu/

Received on Tue Jan 23 2001 - 13:44:43 CST