Subject Re: [ib-support] Join, SP and ORDER BY: strange behaviour
Author Nando Dessena
I wrote

> for some reason I have to code an inner join like this:
> select A.*
> from ATABLE A join ASTOREDPROC(<parameters>) SP
> order by A.AFIELD
> Let ASTOREDPROC be a stored procedure that simply performs for select
> KEYFIELD from ATABLE into ... do.
> The resultset is NOT ordered by A.AFIELD if AFIELD is indexed, unless it
> is KEYFIELD (I have 4 indexes on ATABLE: one on KEYFIELD and three on
> three other fields, one field each: the order by won't work on those
> three only).
> The problem goes away if I:
> - request a descending sort, or
> - use a left join (SP left join table, which in my case is equivalent),
> or
> - remove/deactivate the index, or
> - remove the join with the SP
> As a side note, if I use a left join (which is what I am planning to do
> if no better solution arises), the plan shown says IB is not using the
> existing index for sorting (IB knows better than me anyway, I suppose).
> I am using IB5.6.
> Sounds like a bug of some kind.
> Any thoughs?

Hasn't anyone anything to say about it? Should I try to generate a
reproducible test case with the latest FB build and optionally submit a
bug report?