Oracle comparing day to second interval to an integer

Question

I am wondering what is going on under the hood of oracle when I am trying to compare day to second interval with a integer.

Example below.

SET SERVEROUTPUT ON;
DECLARE
    v_date1 TIMESTAMP := current_timestamp ;
    v_date2 TIMESTAMP := current_timestamp - 150;
BEGIN
    -- Wrong way. But what is happening here?
    IF v_date1 - v_date2 < 2 THEN
        DBMS_OUTPUT.PUT_LINE('YES - why?');
    ELSE
        DBMS_OUTPUT.PUT_LINE('NO');
    END IF;
    -- Correct way
    IF v_date1 < v_date2 + 2 THEN
        DBMS_OUTPUT.PUT_LINE('YES');
    ELSE
        DBMS_OUTPUT.PUT_LINE('NO - works as expected');
    END IF;
    -- Another correct way
    IF v_date1 - v_date2 < INTERVAL '2' DAY THEN
        DBMS_OUTPUT.PUT_LINE('YES');
    ELSE
        DBMS_OUTPUT.PUT_LINE('NO - works as expected as well');
    END IF;
END;

Can somebody explain to me why first IF is evaluated to true?

Answer 1

What you're seeing seems to be a bug, or at least undocumented behaviour. So unfortunately I think you'd need to raise a service request with Oracle to get an explanation. Only they can peer under the hood and see if it's doing what they expect or is something they hadn't thought of.

---

Breaking down some of the steps, not just for your specific question:

v_date1 TIMESTAMP := current_timestamp ;

casts current_timestamp, which is is the time in your session time zone, to a plain timestamp - losing the time zone information.

v_date2 TIMESTAMP := current_timestamp - 150;

has two steps; current_timestamp - 150 gives you a date, losing the fractional seconds and time zone information, and then casts that back to a timestamp. It would be better to use - interval '150' day, but I realise you're just setting up some dummy data here.

IF v_date1 - v_date2 < 2 THEN

is also multiple stages: v_date1 - v_date2 gives you an interval, e.g. +150 00:00:00.975444. (Notice that is isn't exactly 150 days, partly because of the small elapsed time between the two current_dtimestamp calls, but much larger than you'd expect from just that difference - because of the precision loss from the earlier bounce through the date data type.)

Then the comparison itself is where it gets interesting. It looks like an implicit data conversion is happening, but you're trying to compare an interval with a number, and there is no implicit conversion that allows that; and in plain SQL trying to do the same comparison errors:

select case when current_timestamp - (current_timestamp - 150) < 2 then 'yes' else 'no' end from dual;

ORA-00932: inconsistent datatypes: expected INTERVAL DAY TO SECOND got NUMBER

So it appears PL/SQL is doing some other implicit conversion, but it isn't obvious what, and it doesn't seem to be documented or mentioned on MoS. It doesn't appear to be a string comparison, or raw, or conversion of that 2 to either interval type, or conversion of the interval to a number, or the interval or either timestamp in the calculation to dates...

There doesn't seem to be any numeric value that makes that comparison return false - whether the left-hand side is a calculation (which could potentially be being rewritten to something more like your second version) or fixed interval variable. That makes it seem more like a bug than intentional.

One last thing from your original code:

-- Correct way
IF v_date1 < v_date2 + 2 THEN

This isn't really quite correct either, though it might seem picky; v_date + 2 gives you a date rather than a timestamp, so if you hadn't already lost the fractional seconds precision earlier (with the - 150) then you would at this point. And now you're comparing a timestamp with a date, which involves more implicit conversion. You could change the numeric 2 to a 2-day interval, but that would be logically the same as your final (correct) comparison.