Temporal Validity and "wrong" results10 Jan 2017
Temporal validity is a new feature in Oracle 12c. It makes querying effective date ranges much simpler. For more details refer to the Oracle documentation.
This blog post explains in which situation one gets “wrong” or at least unexpected results when using temporal validity.
To demonstrate the situation let’s create a table with a time dimension and some records:
So if you want to get the records from table T1 which are effective as of the day after tomorrow you can query it with the following select:
The returned result is as expected
So what happens if you use a nonexistent time dimension:
You get an error, which is also the expected behaviour. But what happens when you delete the only existing time dimension on the table and query the table again.
Oracle successfully executes the query and simply ignores the temporal validity clause. If someone accidentally drops your time dimension you won’t notice but get wrong results.
In my opinion the correct behaviour in this situation would be returning an error, as it is the case when you have an already existing time dimension on the table but use a non-existing one in the query. I already filed a bug in MOS to ask whether this is a bug or a design decision and I will keep this post updated on the outcome.
This bug has finally been fixed in version 19.1. Oracle now correctly reports
ORA-55603: invalid flashback archive or valid time period command when there is no time dimension on the table.