Subject Re: [firebird-support] Strange behavior in stored procedure
Author Helen Borrie
At 07:25 PM 1/11/2006, Martijn wrote:
> It's not the exact same query if you run it as a DSQL query. DSQL
> > isn't in a FOR loop.
> >
> > >Does anyone have any idea as to why this could happen? All input and
> > >arguments match exactly to the table specs and even if they didn't I
> > >expect this to fail on all cases rather than just on some cases.
> > The problem here is that there is only one row output from SELECT
> > FIRST so your FOR loop is going to return zilch whenever the output
> > has multiple rows.
>Why? Can you explain that?

No, not really. But there's an intrinsic problem with the logic of
putting that SELECT FIRST 1 inside a FOR loop. Theoretically, if the
set from which the FIRST 1 is being taken is not a singleton, there
will be leftovers that the logic can't deal with. So, according to
my intuition, if it's giving "weird" results in some cases, it must
be to do with what is being output to the intermediate set.

Really the point to make here is that SELECT FIRST is misused inside
a loop. The same query returning the same result ad infinitum, with
no endpoint.