I have the following recursive CTE which splits each element coming from base per month:

with
    base (id, start_date, end_date) as (
        select 1, date '2022-01-15', date '2022-03-15' from dual
        union
        select 2, date '2022-09-15', date '2022-12-31' from dual
        union
        select 3, date '2023-09-15', date '2023-09-25' from dual
    ),
    split (id, start_date, end_date) as (
        select base.id, base.start_date, least(last_day(base.start_date), base.end_date) from base
        union all
        select base.id, split.end_date   1, least(last_day(split.end_date   1), base.end_date) from base join split on base.id = split.id and split.end_date < base.end_date
    )
select * from split order by id, start_date, end_date;

It works on Oracle and gives the following result:

The two following stop conditions work correctly:

• ... from base join split on base.id = split.id and split.end_date < base.end_date
• ... from base, split where base.id = split.id and split.end_date < base.end_date

The following one fails with the message ORA-32044: cycle detected while executing recursive WITH query:

• ... from base join split on base.id = split.id where split.end_date < base.end_date

I fail to understand how the last one is different from the two others.

CodePudding user response：

It looks like a bug as all your queries should result in identical explain plans.

However, you can rewrite the recursive sub-query without the join (and using a SEARCH clause so you may not have to re-order the query later):

WITH split (id, start_date, month_end, end_date) AS (
  SELECT id,
         start_date,
         LEAST(
           ADD_MONTHS(TRUNC(start_date, 'MM'), 1) - INTERVAL '1' SECOND,
           end_date
         ),
         end_date
  FROM   base
UNION ALL
  SELECT id,
         month_end   INTERVAL '1' SECOND,
         LEAST(
           ADD_MONTHS(month_end, 1),
           end_date
         ),
         end_date
  FROM   split
  WHERE  month_end < end_date
) SEARCH DEPTH FIRST BY id, start_date SET order_id
SELECT id,
       start_date,
       month_end AS end_date
FROM   split;

Note: if you want to just use values at midnight rather than the entire month then use INTERVAL '1' DAY rather than 1 second.

Which, for the sample data:

CREATE TABLE base (id, start_date, end_date) as 
  select 1, date '2022-01-15', date '2022-04-15' from dual union all
  select 2, date '2022-09-15', date '2022-12-31' from dual union all
  select 3, date '2023-09-15', date '2023-09-25' from dual;

Outputs:

ID	START_DATE	END_DATE
1	2022-01-15T00:00:00Z	2022-01-31T23:59:59Z
1	2022-02-01T00:00:00Z	2022-02-28T23:59:59Z
1	2022-03-01T00:00:00Z	2022-03-31T23:59:59Z
1	2022-04-01T00:00:00Z	2022-04-15T00:00:00Z
2	2022-09-15T00:00:00Z	2022-09-30T23:59:59Z
2	2022-10-01T00:00:00Z	2022-10-31T23:59:59Z
2	2022-11-01T00:00:00Z	2022-11-30T23:59:59Z
2	2022-12-01T00:00:00Z	2022-12-31T00:00:00Z
3	2023-09-15T00:00:00Z	2023-09-25T00:00:00Z

fiddle

CodePudding user response：

It's because WHERE and ON conditions are not evaluated at the same level: when the condition is in the ON clause it's limiting the rows concerned by the JOIN, where it's in the WHERE it's filtering the results after the JOIN has been applied, and since a recursive CTE see all rows selected up to now...