I'm working on sql that looks for rows in a table where the rows 'last_run' date 'frequency' (in minutes), is greater than the current date/time. I've noticed that there appears to be an upper bound for date comparisons Oracle can make sense of.

For example this query;

with tests as 
    (
    select
        'TEST 1'  as code,
        99999999 as frequency,
        sysdate as last_run
    from dual
        union
    select
        'TEST 2'  as code,
        99999999999 as frequency,
        sysdate as last_run
    from dual
    )
select
    p.*,
    (p.last_run   p.frequency / 24 / 60 ) as next_run
from tests p
where (p.last_run   p.frequency / 24 / 60 < sysdate or p.last_run is null)

I would expect this query to return null but instead it returns;

I can solve the problem by setting frequency = null and my other code will recognize that the row need not be considered, but it seems strange to me that Oracle can't recognize that the year 4455 > 2021.

Is there some maximum conceivable date in Oracle that I'm unaware of?

I'm running this in Oracle SQL Developer Version 18.2.0.183 and Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production.

CodePudding user response：

There is a maximum date in Oracle, it is 9999-12-31 23:59:59 in YYYY-MM-DD HH24:MI:SS format. Here is a screenshot of the Oracle Documentation: Here is the Oracle Documentation which talks about the valid date values (LINK)

The problem is that you are adding ~190,258 years with your second query. Likely overflowing the buffer many times over. It just so happened that you ended up back at the value you did.

CodePudding user response：

it seems strange to me that Oracle can't recognize that the year 4455 > 2021

It can. The problem is that your year isn't 4455; it's -4455. See this db<>fiddle, showing the result (in a different timezone) with default DD-MON-RR format, your output format, and ISO format with the year sign included (S format element).

With your frequency of 99999999999 the value you are adding to the current date is 69444444 days, which is (very roughly) 190128 years - clearly that's going to put you well past the maximum date of 9999-12-31; and indeed with a different value like 9999999999 (one less digit), which is 6944444 days or roughly 19012 years, you get an error - also shown in that db<>fiddle.

The issue seems to be how Oracle manipulates its internal representation when it does the calculation; in adding that large value it appears that the year - which is stored in two bytes - is overflowing and wrapping.

Using the type-13 version, 190128 2021 = 192149, which is (256 * 750) 149. 750 doesn't fit in one byte, so you get the modulus, which is 238. That would make the first two bytes of the calculated date come out as 149,238. That actually corresponds to year -4459:

select dump(date '-4459-01-01') from dual;

Typ=13 Len=8: 149,238,1,1,0,0,0,0

which is close enough to demonstrate that's what's happening - given that the calculation is outside the expected range and it's probably trying to do invalid leap day calculations in there somewhere. The point, though, is that the generated, wrapped, value represents a valid year in that internal notation.

With the lower value, 19012 2021 = 20133, which is (256 * 82) 41. Now there is no wrapping, so the first two bytes of the calculated date come out as 41,82. That is now not a valid year, so Oracle knows to throw the ORA-01841 exception.

So, you need to limit the frequency value to a number that won't ever go past 9999-12-31, or test it at run time against 9999-12-31 minus the current date - and if it's too big, ignore it. That's if you want what appears to be a magic number at all.