Re: Any Ideas

Larry,

     Bug at play is very likely (change of execution plan) but even though I presume, since you return something that you provide (24548) that this query is assumed to be an existence check? Then, why don't you write it

SELECT shift.shiftid
FROM shift
WHERE shift.shiftid = 24548

     AND exists (select null
                       FROM job, lkshiftmachinestage, operation 
                       WHERE job.actualstartdt <= shift.todt 
                         AND job.actualenddt >= shift.fromdt 
                         AND lkshiftmachinestage.machinestageid =
operation.machinestageid 
                        AND job.jobid = operation.jobid 
                        AND lkshiftmachinestage.shiftid = shift.shiftid)
 

I suspect that the DISTINCT erroneously brings the optimizer to rewrite the query in a way which involves a NOT IN with a subquery that returns a NULL value (perhaps that the distribution of data makes sense of inverting the inequality conditions), which can never be true since you never know what a NULL is.

HTH Stéphane Faroult

Wolfson Larry - lwolfs wrote:
> We have the query below running in a 9.2.0.6 DB
> We put the query into a 9208 instance and the Distinct works, looks as
> if there's a bug at play
>
> SELECT shift.shiftid
> FROM job, shift, lkshiftmachinestage, operation
> WHERE job.actualstartdt <= shift.todt
> AND job.actualenddt >= shift.fromdt
> AND lkshiftmachinestage.machinestageid =
> operation.machinestageid
> AND job.jobid = operation.jobid
> AND lkshiftmachinestage.shiftid = shift.shiftid
> AND shift.shiftid = 24548
>
> And it returns 255 identical numbers. When I put in a distinct
>
> SELECT DISTINCT shift.shiftid
> FROM job, shift, lkshiftmachinestage, operation
> WHERE job.actualstartdt <= shift.todt
> AND job.actualenddt >= shift.fromdt
> AND lkshiftmachinestage.machinestageid =
> operation.machinestageid
> AND job.jobid = operation.jobid
> AND lkshiftmachinestage.shiftid = shift.shiftid
> AND shift.shiftid = 24548
>
> I get "no rows found". Even tried making it a subquery
>
> select distinct *
>
> from
>
> (
>
> SELECT shift.shiftid
>
> FROM job, shift, lkshiftmachinestage, operation
>
> WHERE job.actualstartdt <= shift.todt
>
> AND job.actualenddt >= shift.fromdt
>
> AND lkshiftmachinestage.machinestageid =
> operation.machinestageid
>
> AND job.jobid = operation.jobid
>
> AND lkshiftmachinestage.shiftid = shift.shiftid
>
> AND shift.shiftid = 24548
>
> )
> And still get nothing :-S
>
>
> TIA
> Larry
>

--
http://www.freelists.org/webpage/oracle-l