|Subject||RE: [firebird-support] 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 inI believe you are incorrectly using CTE instead of simple calculated column, as 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 =
> 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
> select *
> 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.
FIRST 1 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)
igc.issue_id = i.issue_id
and ia."USER" = 'a'
order by ia.occasion desc
) as occasion
from issues i