--- In firebird-support@yahoogroups.com, Adriano dos Santos Fernandes
<adrianosf@...> wrote:

>
> Adriano dos Santos Fernandes escreveu:
> > woodsmailbox escreveu:
> >> OK, sorry for posting directly to the tracker (CORE-2166).
> >> I only did because I consider this a bug. I was explained that
> >> this is correct behavior, yet I still get inconsistent results,
> >> even in the context of what was explained to me.
> >> ----------------------------------------------------------
> >> test case:
> >>
> >> create table t (c integer primary key);
> >> insert into t values (1);
> >> insert into t values (2);
> >> insert into t values (3);
> >>
> >> execute block returns (r integer) as
> >> declare variable p integer = 1234;
> >> begin
> >> for select c, c from t where :p = 1234 into p, r do suspend;
> >> end
> >>
> >> This gets only the first row (i.e. when p was 1234).
> >> This is supposed to be correct behavior.
> >>
> >> Ok, I have a few questions since I'm a little confused:
> >>
> >> 1) is the exact behavior documented somewhere?
> >>
> >> 2) the premise that the query is reevaluated does not hold for the
> >> following test case, i.e. I never get the row where c = 3 with the
> >> following block:
> >>
> >> execute block returns (r integer) as
> >> declare variable p integer = 1;
> >> begin
> >> for select c from t where c in (1, 2, :p) into r do
> >> begin
> >> p = 3;
> >> suspend;
> >> end
> >> end
> >>
> >> ... as far as can infer from those few tests, it seems that the where
> >> clause do get reevaluated, but data is not re-queried to fulfill the
> >> new condition.
> >>
> Also note that if you had where field = :p (and :p changes) you are
> going to have different results depending if an index is used or not...
>
> >> So my question is: what is the exact rule that does such behavior

follow?

> >>
> > When the variable is read, it's your current value used. So perhaps,
> > more appropriate would be a warning or an error, but not use the

first

> > value IMHO.
> >
> And that's seems a reason for warning/error from programming language
> POV. Don't know what the standard say about it.
>
>
> Adriano
>

I agree. If that would have been the case (throwing an error) I
wouldn't have lost all this time scratching my head about what's going
on.

I doubt the standard states anything about the "for select" construct,
so I guess it's up to the fb community to "design" this or whatever
your policy.

AFAIK the particularities of this behavior are not documented, please
correct me/redirect me to doc. if I'm wrong. You say it's expected
behavior, but where it is documented?

My PPOV is that instead of throwing an error, the query should not be
re-evaluated at all.

Arg #1: The "for select" construct is claimed in the docs to be the
closest thing to the SQL standard cursors which are not present in
firebird, so I guess people also expect it to behave close to it, i.e.
you can't reevaluate a cursor while fetching from it, it doesn't make
any sense.

Arg #2: it allows me to reuse the variables inside the loop without
worrying about side effects to the loop itself.