Thanks for the explanation.

So this means we have unspecified behavior for procedures with side-effects. It would be nice if I could flag a procedure as having side effects (similar to postgres -- i don't remember how they call it), so that the engine would know to cache its results instead of executing it an unspecified number of times. I can think of other optimizations that could rely on such flag. Although I admit it's now easy to work around this with GTEs.

--- In firebird-support@yahoogroups.com, Dmitry Yemanov <dimitr@...> wrote:
>
> woodsmailbox wrote:
> >
> > 1) I want to know if there's any guarantee that the procedure is executed only once when its parameters are not correlated with any fields from joined tables.
>
> Generally, this is ensured by the optimizer which tends to start the
> inner join order from a procedure. This rule won't be followed though if
> you use: "<table> left join <procedure>".
>
> > 2) What is the semantics for joining with correlated parameters? It seems to be working only with left join.
>
> Because of (1), correlated inner joins cannot work with procedures
> properly, so it's recommended to use left joins instead. This is a known
> issue to be addressed in future FB versions.
>
>
> Dmitry
>