Re: SQL puzzle

Looks like there is one Oracle feature which is playing it's role here: Oracle parses statements from the end to the beginning. So when you have >0 comparison before division, it fails, when you have it before division, it works. I am not sure it's always so, though, so I wouldn't write sql statements counting on it. I'd use comparison like: decode(CH2BN_MEDIAN,0,CH2IN_MEAN/CH2BN_MEDIAN)>= 2. It will work in case you are using OR operator. In case of OR parsing is more complicated, Oracle doesn't use straitforward (or should I say backward) down-to-top parsing.

In article <94kmvb$p6l$1_at_nntp.Stanford.EDU>,   gail_at_alberich.Stanford.EDU (Gail Binkley) wrote:
> 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/
>

Sent via Deja.com
http://www.deja.com/ Received on Tue Jan 23 2001 - 15:05:26 CST