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