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.
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.

>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...

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