Strange behavior of systimestamp and sysdate

user_2097191 0
Viewed 321

Today I encountered a situation which I can not explain and I hope you can.

It boils down to this:

Let say function sleep() return number; loops for 10 seconds and then returns 1.

A sql query like

SELECT systimestamp s1, sleep(), systimestamp s2 from dual;

Will result in two identical values for s1 and s2. So this is somewhat optimized.

A PLSQL-Block with

a_timestamp := systimestamp + 5sec;
IF systimestamp < a_timestamp and sleep() = 1 and systimestamp > a_timestamp THEN 
  [...] 
END IF;

will evaluate to true, because the expression get evaluated from left to right and because of the sleep() the second systimestamp is 10sec greater than the first and a_timestamp lies between both. The +5sec syntax is pseudo code, but bear with me. So here the systimestamp is not optimized.

But now it gets funky:

IF systimestamp between systimestamp and systimestamp THEN [...]

Is always false, but

IF systimestamp + 0 between systimestamp + 0 and systimestamp + 0 THEN [...]

Is always true.

Why? I am confused...

This happens with sysdate as well

oracle18c plsql oracle
2 Answers

If you add a number to a timestamp, you do not get a timestamp...you get a date. Thus you have lopped off the fractional seconds part, and thus comparisons that look like they should be equal might not be

SQL> select systimestamp, systimestamp+0 from dual;

SYSTIMESTAMP                                                                SYSTIMESTAMP+0
--------------------------------------------------------------------------- -------------------
18-JUN-20 11.57.28.350000 AM +08:00                                         18/06/2020 11:57:28

SQL> select * from dual where systimestamp > sysdate;

D
-
X

If you want to add days etc to a timestamp, use an INTERVAL data type not a NUMBER.

user_73672930

When you do:

if systimestamp between systimestamp and systimestamp then

the non-deterministic systimestamp call is just being evaluated three times, and getting very slightly different results each time.

You can see the same effect with

if systimestamp >= systimestamp then

which also always returns false.

Except, it's not quite always. If the server is fast enough and/or the platform it's on has low-enough precision for timestamp fractional seconds (i.e. on Windows, which I believe still limits the precision to milliseconds) then all of those calls could still get the same value some or most of the time.

Things are a bit different in SQL; as an equivalent:

select *
from dual
where systimestamp between systimestamp and systimestamp;

will always return a row, so the condition is always true. That is explicitly mentioned in the documentation:

All of the datetime functions that return current system datetime information, such as SYSDATE, SYSTIMESTAMP, CURRENT_TIMESTAMP, and so forth, are evaluated once for each SQL statement, regardless how many times they are referenced in that statement.

There is no such restriction/optimisation (depending on how you look at it) in PL/SQL. When you use between it does say:

The value of the expression x BETWEEN a AND b is defined to be the same as the value of the expression (x>=a) AND (x<=b) . The expression x will only be evaluated once.

and the SQL reference also mentions that:

In SQL, it is possible that expr1 will be evaluated more than once. If the BETWEEN expression appears in PL/SQL, expr1 is guaranteed to be evaluated only once.

but it says nothing about skipping evaluation of a and b (or expr2 or expr3) even for system datetime functions in PL/SQL.

So all three expressions in your between will be evaluated, it will make three separate calls to systimestamp, and they will all (usually) get slightly different results. You effectively end up with:

if initial_time between initial_time + 1 microsecond and initial_time + 2 microseconds then

or to put it another way

if (initial_time >= initial_time + 1 microsecond) and (initial_time <= initial_time + 2 microseconds) then

While (initial_time <= initial_time + 2 microseconds) is always going to be true, (initial_time >= initial_time + 1 microsecond) has to be false - unless the interval between the first and third evaluations is actually zero for that platform/server/invocation. When it is zero the condition evaluates to true; the rest of the time, when there is any measurable delay, it will evaluate to false.

Your other examples all manipulate the timestamp in way that removes the fractional seconds, by turning some or all of the results into dates, as @Connor showed (and I alluded to in comments). Those aren't really relevant to your core question of why if systimestamp between systimestamp and systimestamp then is (usually) false.

user_2663040
Related