> > 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
> >output
> > > >arguments match exactly to the table specs and even if they didn't I
> >would
> > > >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.

Could be me, but that sounds like a bug or you're wrong.

SELECT [FIRST] returns a resultset, which is handled by the FOR loop.

Why this should "return the same result ad infinitum" is unclear to me. If
this would be the case, how would this behave differently from a normal
SELECT that returns a resultset? There IS an endpoint for a FIRST 1,
namely after a single iteration of the FOR loop.

It's not like the SELECT is executed for each FOR or anything.

Sorry, I still fail to see why this would be wrong (despite the lack of an
ORDER BY clause in the OPs post ;) ...

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com