Query Produces Different Result Sets Using Index Or Full Table Scan (Query Uses OR EXPANSION) (Doc ID 2432326.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

• Query of the following form gets wrong results depending on the last predicate:
  
  SELECT Count(*) FROM tab1 T
  WHERE templateid IN ( 4452356, .............. ,4523303 )
  AND ((((T.starttime BETWEEN To_date('20140211 00:00:00','yyyymmdd hh24:mi:ss') AND To_date('20140213 00:00:00', 'yyyymmdd hh24:mi:ss'))
  OR (T.durationplanned > 1440 AND T.endtime >= To_date('20140212 00:00:00','yyyymmdd hh24:mi:ss'))) AND (T.plannedstate = 2))
  ........
  OR ( T.durationplanned > 1440 AND T.endtime >= To_date('20140129 00:00:00','yyyymmdd hh24:mi:ss'))) AND ( T.plannedstate = 1))
  OR ( T.plannedstate < 1 ))
•  When the last predicate is OR ( T.plannedstate < 1 ) correct results.

  When the last predicate is ( T.plannedstate = 0 ), wrong results (fewer rows are returned).

  The value for T.PLANNEDSTATE is either 0 or 2, and therefore the 2 queries should both return the same result set.

Changes

No changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.