Re: SQL puzzle

From: <derf23456_at_my-deja.com>
Date: Fri, 26 Jan 2001 15:15:55 GMT
Message-ID: <94s4av$q14$1@nnrp1.deja.com>

As an Oracle consultant we had here explained, for a similar situation (a tuning question):

The SQL statement is parsed and pushed onto a stack. When the optimizer evaluates the code, it pulls the code back off the stack (which will be from end to start).

This might expaain why (it seems) ver. 1 works and ver. 2 doesn't (assuming that the code is also executed off the stack).

Even if this isn't the CORRECT reason, it sure sounds good (IMHO:-).

In article <94krmc$hoo$1_at_nnrp1.deja.com>,   Alex Filonov <afilonov_at_pro-ns.net> wrote:
> 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/
>

Sent via Deja.com
http://www.deja.com/ Received on Fri Jan 26 2001 - 09:15:55 CST