At 01:00 AM 30/11/2007, you wrote:

>I use FB 2.0 and in a SP I get strange results. It really seems a FB bug:
>
>
>select :acumunits + units,
>(:acumunits*:acumprice+price*units)/(:acumunits+units)
> from inventorytable
> where inventoryid=:inventoryid
> into :acumunits, :acumprice;
>
>
>I am using :acumunits in both the SELECT and INTO clause, this seems
>to confuse Firebird.
>
>The calculation "(:acumunits*:acumprice+price*units)/(:acumunits+units)"
>is being executed after the INTO :acumunits part, therefore, acumunits
>already contains the "old" value.
>
>In the other hand, if I use
>
>
>select :acumunits + units,
>(:acumunits*:acumprice+price*units)/(:acumunits+units)
> from inventorytable
> where inventoryid=:inventoryid
> into :tmp_acumunits, :tmp_acumprice;
>
>everything works fine.
>
>
>I have been speaking with Martijn Tonies, from Upscene, and he has
>confirmed this problem also.

I don't see it as a problem (or a bug). A variable's value is whatever was last assigned to it. I've been writing SP's for years and years and I've *never* assumed that variables in PSQL behave differently to variables in other strictly-typed languages...I mean, it never occurred to me that anyone would assume otherwise until last night, when Martijn caught me on ICQ just as I was heading for bed, already half asleep...

>I have solved it using temporal variables,

No, you "solved" it by using variables correctly.

>but I wanted to report this possible bug.

If it behaved as you think it ought to, it *would* be a bug.

Suppose you have a SP that you defined as follows:

create procedure blah (arg1 integer, arg2 varchar(20) as
.....

At the beginning of execution, as long as the data types are correct, the engine applies the value of the first parameter to be (for our example) Arg1's value and the value in the second parameter to be Arg2's value.

That settled, the values in those arguments are assigned to local variables, in our example, :Arg1 and :Arg2. When we use those variables in PSQL language statements, we omit the colon-prefix. When we use those variables in SQL statements, we apply the colon-prefix. (More recent versions of Firebird do not even enforce this rule, which I *do* consider to be a bad mistake, if not an actual bug...it is unfortunate that people do strange things like using the same identifiers for variables that they use for the db table columns that will be mixed with them....but that's a fight for another day...)

The important thing is NOT to confuse parameters with variables externally (as developers using Borland tools often do) and not to expect that variables *inside* a PSQL module will behave differently if they happen to be the ones that the engine has created from the input arguments (and has created for the output arguments also).

1. A variable's value changes as soon as you assign a new value to it AND it won't change if you don't assign a new value to it.

2. The SELECT....INTO construct is an assignment statement that takes an *ordered list* of values and assigns them one-by-one to an ordered list of plain old variables. So, if you have a statement that is extracting a row like

select cola, colb, colc from aTable
...
into :cola, :colb, :colc

then cola is assigned first and colc is assigned last. And you're really going to get into a muddle if you do

select cola, colb, colc from aTable
...
into :colb, :colc, :cola

(I hope you can see that...)

Now, apply this to your "problem"
Assume
acumunits = 0;
acumprice = 0;
select :acumunits + units,
(:acumunits*:acumprice+price*units)/(:acumunits+units)
from inventorytable
where inventoryid=:inventoryid
into :acumunits, :acumprice;

The values to be passed into the set of variables in the INTO clause does not happen by somehow dumping one set straight into the other. The assignments are one-by-one in order.

So --

The first assignment that happens is:

acumunits (variable in the INTO clause) = acumunits (variable, value 0) + units (value from field units in the SELECT array), i.e. the variable acumunits now has the value of the field units and the value 0 is gone.

The second assignment

acumprice=(acumunits*acumprice+price*units)/(acumunits+units)

uses the current value of the variable acumunits when it calculates the expression for the value into acumprice.

./heLen