Re: SQL puzzle

Do NOT make any assumption on the order Oracle uses to evaluate two Boolean expressions within a brace pair; always make sure you don't end up with an /0 operation when the optimizer chooses to evaluate the Booleans in 'reverse' order. The database doesn't ignore the CH1B_MEDIAN
> 0 clause, it uses an evaluation order you didn't expect.

Try using the nvl() function: CH1I_MEAN / NVL( CH1B_MEDIAN, 1 ).

As for your problem with statements 3 and 4: as far as I know, nested Boolean expressions are rewritten before they are evaluated. Rewriting an 'OR' instead of an 'AND' apparently gives an other resulting order :).

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/
Received on Tue Jan 23 2001 - 14:47:34 CST