> 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?
>
> This:
>
> 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
> )
> 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.

I believe you are incorrectly using CTE instead of simple calculated column, as in:


select
i.*,
(
select
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)
where
igc.issue_id = i.issue_id
and ia."USER" = 'a'
order by ia.occasion desc
) as occasion
from issues i


Sean