Having a single recursive CTE while there are multiple CTEs in a query

Question

I am not sure if this has been asked before but I have searched across different forums and haven't found the exact answer. Should we always initialize the word RECURSIVE to the first CTE (even though it's not the recursive CTE) for the entire query to run?

Example Query:

-- initalising some data
with temp_cte1 as (
  select
  100 as id,
  'test' as name,
  30000 as salary,
  date('2011-10-28') as start_date,
  date('2011-11-30') as end_date
),

-- this cte is to get all the dates between the start and end dates
temp_cte2 as (
  select
    id,
    start_date as curr_day,
    end_date
  from temp_cte1
  union all
  select 
    id,
    DATE_ADD(curr_day, INTERVAL 1 DAY) as curr_day,
    end_date
  from temp_cte2
  where curr_day < end_date
),

-- this cte is filter out specific dates from tempcte2
temp_cte3 as (
  select
    id,
    count(*) as pay_days
  from temp_cte2
  where DAY(curr_day) in (1, 15)
  group by id
)

select 
  d.id,
  d.name,
  d.salary,
  d.start_date,
  d.end_date,
  f.pay_days
from temp_cte1 d
left join temp_cte3 f
on d.id = f.id

The above query throws the error that table temp_cte2 doesn't exist: Error Response: Error Code: 1146. Table 'final.temp_cte2' doesn't exist

-- initalising some data
with recursive temp_cte1 as (
  select
  100 as id,
  'test' as name,
  30000 as salary,
  date('2011-10-28') as start_date,
  date('2011-11-30') as end_date
),

-- this cte is to get all the dates between the start and end dates
temp_cte2 as (
  select
    id,
    start_date as curr_day,
    end_date
  from temp_cte1
  union all
  select 
    id,
    DATE_ADD(curr_day, INTERVAL 1 DAY) as curr_day,
    end_date
  from temp_cte2
  where curr_day < end_date
),

-- this cte is filter out specific dates from tempcte2
temp_cte3 as (
  select
    id,
    count(*) as pay_days
  from temp_cte2
  where DAY(curr_day) in (1, 15)
  group by id
)

select 
  d.id,
  d.name,
  d.salary,
  d.start_date,
  d.end_date,
  f.pay_days
from temp_cte1 d
left join temp_cte3 f
on d.id = f.id

But if I initialize with the keyword RECURSIVE in the starting CTE (even though that CTE isn't recursive), the query runs fine. Could someone explain why I should initialize a recursive CTE with the keyword RECURSIVE at the top?

I want to know why we have to use the keyword RECURSIVE at the first cte.

Answer 1

The keywords WITH and RECURSIVE are associated to all your CTEs, not just the first one in the list. RECURSIVE just means that there may be recursive logic within your CTEs, not that any one specific CTE (or all of them) are recursive