1

I am getting some strange results using sysdate along with an OR clause in Oracle. If the sysdate check is first I do not get all of my rows, but if the sysdate check is second I do get all of my rows. Looking for any explanations as to why this is happening.

Below shows an abbreviated version of my query, followed by numerous other iterations that give correct or incorrect results.

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

Incorrect: Note sysdate clause is first

SQL> select count(*) from employee emp
 where
 (emp.TERM_DATE >= (sysdate-30)) OR (emp.TERM_DATE =
 TO_DATE('01/01/1700', 'MM/DD/YYYY'));
   COUNT(*)
 ----------
          5

Correct: Note sysdate clause is second. All I did was reverse the 2 clauses in the OR statement 

SQL> select count(*) from employee emp
where
(emp.TERM_DATE = TO_DATE('01/01/1700', 'MM/DD/YYYY')) OR (emp.TERM_DATE>=(sysdate-30));
  COUNT(*)
----------
      1506

Below are a number of other query strings 

select count(*) from cohp91.employee
where
(
--(TERM_DATE = TO_DATE('01/01/1700', 'MM/DD/YYYY') OR TERM_DATE >= sysdate-30) -- CORRECT
--TERM_DATE >= sysdate-30 OR TERM_DATE = TO_DATE('01/01/1700', 'MM/DD/YYYY') -- INCORRECT
--(select ADD_MONTHS(sysdate,-1) from dual) <= term_Date   OR TERM_DATE = TO_DATE('01/01/1700', 'MM/DD/YYYY')   -- CORRECT
-- (term_Date >= trunc(ADD_MONTHS(sysdate,-1)) OR TERM_DATE = TO_DATE('01/01/1700', 'MM/DD/YYYY') ) -- INCORRECT
-- term_Date >= (select sysdate-30 from dual) OR TERM_DATE = TO_DATE('01/01/1700', 'MM/DD/YYYY')  -- CORRECT
-- (TERM_DATE >= to_date(to_char(sysdate-30,'MM/DD/YYYY'),'MM/DD/YYYY')) OR TERM_DATE = TO_DATE('01/01/1700', 'MM/DD/YYYY') -- INCORRECT
)
;
Mat
  • 202,337
  • 40
  • 393
  • 406
user2452659
  • 11
  • 1
  • Cannot reproduce it. Can you post `desc employee` please. Also are you sure data has not changed between first and second query? – hol Jun 04 '13 at 19:09
  • Check the actual values using DUMP, you may have an invalid or corrupt date. For example, see [this](http://stackoverflow.com/questions/7030520/oracle-date-corruption-during-update/7032054#7032054) question. – Jon Heller Jun 04 '13 at 19:12
  • Thanks for the replies: Desc of term_date is TERM_DATE NOT NULL DATE – user2452659 Jun 05 '13 at 21:30
  • Here is a DUMP of 1 record with the '1'1'1700' date: sysdate second 13 1/1/1700 Typ=12 Len=7: 117 100 1 1 1 1 1" – user2452659 Jun 05 '13 at 21:32
  • Here is DDUMP of record with TERM_DATE >= sysdate - 30: sysdate first 384256 6/1/2013 Typ=12 Len=7: 120 113 6 1 1 1 1" (I haven't figured out how to add a return in the comment box with out it autosubmitting) – user2452659 Jun 05 '13 at 21:34
  • I did find "systimestamp-30" does give correct results. The odd thing is that I get correct results if the TERM_DATE = '01-JAN-1700' clause is first and incorrect results if the sysdate clause is first. The data is static. It really should not matter which comparison is first in an OR clause. – user2452659 Jun 05 '13 at 21:36
  • Those results look ok, I was wrong about corrupt dates. I'm not sure what is going on here. Would it be possible to create a fully reproducible test case that we could look at? – Jon Heller Jun 09 '13 at 23:04

0 Answers0