|Subject||Cannot join CTEs (using indices) containing FIRST/ROWS/OFFSET|
As soon as i add the ROWS clause to a CTE FB cannot use indices available in the CTEs tables.
Is this a bug, or as designed?
with low as (
select igc.issue_id, ia.occasion
from issues_groups igc
left join issues_groups igb on (igb.issue_significant_id = igc.issue_significant_id)
left join issues_addressed ia on (ia.issue_id = igb.issue_id)
where ia."USER" = 'a'
--order by ia.occasion desc
--fetch first 1 row only
from issues i
left join low l on (l.issue_id = i.issue_id)
issues_groups, issues_addressed: Indexed reads
remove the comments and issues_groups will have non-indexed reads for the join l.issue_id to i.issue_id.