--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 05:47 AM 5/11/2008, you wrote:
> >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.
>
> Logically it is correct behaviour: but WHERE <value> = [constant],

while accepted as syntax, does not make much sense as a predicate. As
you observed, it is only going to be true in the first turn of the
loop. With your data it will never be true again.

>
> Logically, the "as designed" WHERE aFieldName = :search_parameter is

going to return an output row for each match of aFieldName through the
loop.

>
> >Ok, I have a few questions since I'm a little confused:
> >
> >1) is the exact behavior documented somewhere?
>
> The INTO <variable> subclause is a regular assignment of a value to

a variable.

I never implied otherwise about the INTO clause, in fact, just replace
it with regular assignment inside the loop, and you'll get the same
results. The issue doesn't touch the INTO clause.

> The logically proper use of the WHERE clause can be found in any SQL

primer. Applying it in a PSQL construct doesn't change how WHERE
behaves.

I gladly accept your RTF-SQL-M invitation, yet I think the issue might
be a little beyond that.

>
> >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
>
> This is not similar to the first query. However, you are not

getting a result here because the IN() clause cannot take a variable
argument. That's SQL for you...

This has nothing to do with the IN() clause not accepting "variable
argument" whatever that is, just replace the clause with a logically
equivalent construct like c = 1 or c = 2 or c = :p, and you'll get the
same results, i.e. you never get the row where c = 3, although p gets
set to 3 before getting to that row.

>
> For the forthcoming v.2.5 you could manufacture such a statement

inside your loop, using EXECUTE STATEMENT, if you really must. There
are some examples in the v.2.5 release notes. If you're not
interested in downloading the Alpha package, you can go to
http://firebirdsql.org/download/prerelease/rlsnotes and grab just the
notes.

>
>
> >... 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.
> >
> >So my question is: what is the exact rule that does such behavior

follow?

>
> RuleS. SQL rules in SQL + regular assignment rules in programming

constructs.

>
> This can be tricky to grokk if the difference between a parameter

and a variable is not clear in your mind. And *that* is fuzzy to a
lot of people, especially those coming from a Delphi background, since
Delphi's conventions for parameters encourage you (wrongly) to think
that parameters in DSQL are the same thing as local variables in PSQL.
(And yes, I do labour this point in "The Book", pedantic old git that
I am!)

>
> Under SQL rules, the structure of a query is defined by the input

list, the WHERE predicate[s] and any sorting criteria (ORDER BY, GROUP
BY). A conventional SELECT statement with a WHERE clause like WHERE
<column> = :var is not structurally changed by applying a new value to
:var. The latest value of :var is applied at each turn of the loop,
behaving like your conventional DSQL parameterised statement.

>
> If you turn the WHERE clause around, to WHERE :var = <column>, then

in a loop the underlying structure changes each time :var gets a new
value. The previously prepared statement no longer applies because
the :var value in that syntax is (in SQL terms) a constant, which
makes it NOT a parameterised statement. Hence, each turn of the loop
creates a SELECT statement with a different structure to its
predecessor. This unconventional usage of the WHERE clause will
return *something* if you replace your search argument with one that
conforms to the SQL rules. It won't achieve what you think you ought
to be able to do here but it *will* engender a new statement at each
turn of the loop.

>
> WHERE <column> = :var
>
> doesn't change the structure. SQL parameter rules apply and the

value of the *local variable* :var is applied to a prepared statement.

>
> WHERE :var = <column>
>
> does change the structure because, at each execution of the SELECT,

the value of the local variable :var is a constant under SQL rules.
Changing the value of the constant gives a different structure to the
SELECT statement itself each time it is executed.

>
> ./heLen
>

Ok, suppose I do dig the difference between parametrizing the
structure of the query and parametrizing a value.

In the case of changing the structure of the query from inside the
loop, the query must be re-executed and looping should re-start from
the first row, since you are working on a completely different result
set so the notion of "current row" is lost. Nevermind the fact that
this doesn't happen (at least not in 2.1.1) but I don't care really
since it's of little practical value to even allow such thing.

In the <column> = :var case (i.e. that in the IN() example), from what
I understand (and the tests seem to show) the query just re-applies
the WHERE clause to the next row in the loop. But you can easily
construct a query that must be re-evaluated everytime the parameter
changes even if the structure of the query doesn't change. For
instance, the query
for select c from t where c = :p into r group by c
must be re-evaluated for every change of :p that happens inside the
loop, just like in the :var = <constant> case.

So both the structure-changing and value-changing queries have similar
consequences, although from different theory.

I think (so this is only my personal point of view) that allowing
changing of parameters of the "for select" query from inside the loop
is a plain bug.

Arg #1: In the broadest sense of the argument, most programming
languages don't allow changing the state of the iteration from inside
the iteration, and we all know why.

Arg #2: The fact that the implications of it can lead to such pedantic
discussions as this one is proof alone that you're better of without it :)

My 2cents.