Recursive SQL giving ORA-01790

Question

Using Oracle 11g release 2, the following query gives an ORA-01790: expression must have same datatype as corresponding expression:

with intervals(time_interval) AS
 (select trunc(systimestamp)
    from dual
  union all
  select (time_interval + numtodsinterval(10, 'Minute'))
    from intervals
   where time_interval < systimestamp)
select time_interval from intervals;

The error suggests that the datatype of both subqueries of the UNION ALL are returning different datatypes.

Even if I cast to TIMESTAMP in each of the subqueries, then I get the same error.

What am I missing?

EDIT: I'm not looking for a CONNECT BY replacement.

Answer 1

I have no idea about the type mismatch, but here is an alternative method to accomplish what I think you want (which works in 10gr2):

select base_time + numtodsinterval( 10*(level-1), 'Minute')
from (select trunc(systimestamp) base_time from dual)
connect by base_time + numtodsinterval( 10*(level-1), 'Minute') < systimestamp

Answer 2

In my opinion, "Recursive Subquery Factoring" is broken in 11g R2 for queries with date or timestamp column.

with test(X) as
(
  select to_date('2010-01-01','YYYY-MM-DD') from dual
  union all (
    select (X + 1) from test where X <= to_date('2010-01-10','YYYY-MM-DD') 
  )
)
select * from test;

ORA-01790

use a cast to convert the datatype:

with test(X) as
(
  select cast(to_date('2010-01-01','YYYY-MM-DD') as date) from dual
  union all (
    select (X + 1) from test where X <= to_date('2010-01-10','YYYY-MM-DD') 
  )
)
select * from test;

X
-------------------
2010-01-01 00:00:00

1 row selected

Casting a date into a date is helping, but where are the other results?

It gets even better...

Try it with another start date:

with test(X) as
(
  select cast(to_date('2007-01-01','YYYY-MM-DD') as DATE) from dual
  union all (
    select (X + 1) from test where X <= to_date('2011-01-11','YYYY-MM-DD') 
  )
)
select * from test 
where rownum < 10; -- important!

X
-------------------
2007-01-01 00:00:00
2006-12-31 00:00:00
2006-12-30 00:00:00
2006-12-29 00:00:00
2006-12-28 00:00:00
2006-12-27 00:00:00
2006-12-26 00:00:00
2006-12-25 00:00:00
2006-12-24 00:00:00

9 rows selected

Counting backwards? Why?

Test conducted with:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production