Temporal Validity and "wrong" results

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:

create table t1 (startdate date, enddate date); 
alter table t1 add period for t1period (startdate, enddate); 

insert into t1 values(null, date'2016-01-10'); 
insert into t1 values(date'2016-01-08', null); 
insert into t1 values(date'2016-01-08', date'2016-01-10'); 

commit;

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:

select * from t1 as of period for t1period date'2016-01-11';

The returned result is as expected

STARTDATE           ENDDATE
------------------- -------------------
2016-01-08 00:00:00

So what happens if you use a nonexistent time dimension:

select * from t1 as of period for t2period sysdate+2;
              *
ERROR at line 1:
ORA-55603: invalid flashback archive or valid time period command

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.

alter table t1 drop (period for t1period);

select * from t1 as of period for t2period sysdate+2;

STARTDATE           ENDDATE
------------------- -------------------
                    2016-01-10 00:00:00
2016-01-08 00:00:00
2016-01-08 00:00:00 2016-01-10 00:00:00

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.

Update 2019-01-17

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.