Milliseconds are lost after date to tz conversion on 19c

Hi,

yesterday, I found (maybe) a small bug in 19c. Whenever I deal with timestamp with time zones, I always learn something new. so, the request is as this:

there is a DATE column which should be converted into a timestamp with tz but if it is null then result should be SYSTIMESTAMP. it is pretty straight. here is the output in 19c (19.25 to be specific):

so the first row everything is correct. column D is not empty and data is converted into “timestamp with time zone” but for the second row, the with d is null, we see systimestmap output on second column (NEW_DATA) but if you compare it with actual systimestamp, you can see that MILLISECONDs are missing! they are all zero. Actually it is rounded up because actual systimestamp 09:52:59.666055 but after NVL function, result is 09:59:00.00000.

so what caused this? NVL, FROM_TZ or TO_TIMESTAMP?

first, Let’s replace NVL with COALESCE:

huh! this works. Now we have milliseconds for null date value. so problem is NVL ?

Let’s also add a “TIMESTAMP” column and remove TO_TIMESTAMP function as well (still using NVL):

as you see, unlike date conversion, “nvl(from_tz(t, ‘+02:00’), systimestamp )” returns “correct” data! we can see milliseconds if we don’t need to_timestamp conversion. so is it to_timestamp? not clear.

but since NVL also causes the difference, this means return type of functions and actual data types are different:

so, the type of timestamp column in the table is 180 but to_timestamp function’s type is 187. there are many different examples of this kind of staff on dates/timestamps etc.

But why did I said that this could be a “bug” ? because it works as it is expected on 23ai:

milliseconds are there. so I assume this was a bug in 19c.

wish you healthy, happy days.