woodsmailbox wrote:
Sometime ago, I posted a question about what I consider to be a bug in fb, or at least unspecified behavior that I couldn't find documented anywhere, and nobody could give me a clear answer. So I feel obliged to resurrect it. 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. What do you think?
Dmitry Yemanov answers:
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.