woodsmailbox wrote:

>
> The question was: What happens in a "for select" loop when, inside the
> loop, you change something affecting the results of the select, aka
> variables, data in tables, anything that could, on a next evaluation
> of the select, change its outcome (i.e. more/less rows and/or
> different values in those rows).
>
> My opinion based on some tests + practice is that the semantics of the
> "for select" are not well defined in this case, and they depend on
> various implementation aspects that make the code unpredictable and
> unportable.

As for the data in the underlying tables, AFAIK the standard allows
three kinds of behaviour, depending on whether the cursor has been
declared as sensitive, insensitive or asensitive. In FB, it always
depends on the chosen plan, so it implements asensitive cursors. And
this semantics doesn't differ between FOR SELECT and regular SELECT.

However, I'm not sure about variables. As for me, re-evaluation of
conditions like ":VAR = 1234" is perfectly logical and it implies
sensitivity. But in this case, evaluation of conditions like "FIELD =
:VAR" depends on the query plan, i.e. it's asensitive. So, we have
inconsistent behavior of different predicates. And here we have a
difference with a regular SELECT which is insensitive for its input
parameters (mostly because there's no way for you to change them after
the cursor is open).

Perhaps we could offer truly insensitive cursors some day, but I
wouldn't hold my breath.


Dmitry