The following seems to be a bug. The execution returns rows 1,2. It
should return 1,1. In fact, if I run the code within a stored procedure
alone (not in a trigger), the loop doesn't overwrite the value of y
(works well).

create table test (a integer)
@
create table debug1 (a integer)
@
create trigger test_1 after insert on test referencing new as ins for
each row mode db2sql
begin atomic
declare x smallint;
declare y smallint;

set y = ins.a;
for loop as
select y from sysibm.sysdummy1
union
select 2 as y from sysibm.sysdummy1
do
set x = loop.y;
insert into debug1 values (y);
end for;
end
@
insert into test values (1)
@
select * from debug1
@
drop trigger test_1
@
drop table test
@
drop table debug1
@

A
-----------
1
2 --> WRONG!

Thank you
(Serge, are you there?) :))

I've forotten:
DB2/LINUX
SQL08020

Gustavo Randich wrote:[color=blue]
> The following seems to be a bug. The execution returns rows 1,2. It
> should return 1,1. In fact, if I run the code within a stored procedure
> alone (not in a trigger), the loop doesn't overwrite the value of y
> (works well).[/color]
Well.. there obviously is an inconsistency here. But I claim the trigger
is right, making the procedure wrong.

Let me beef up the example a tad by labeling the comppund as "LOCAL" for
educational purposes.[color=blue]
>
> create table test (a integer)
> @
> create table debug1 (a integer)
> @
> create trigger test_1 after insert on test referencing new as ins for
> each row mode db2sql[/color]
local: begin atomic[color=blue]
> declare x smallint;
> declare y smallint;
>
> set y = ins.a;
> for loop as
> select y from sysibm.sysdummy1
> union
> select 2 as y from sysibm.sysdummy1
> do
> set x = loop.y;
> insert into debug1 values (y);
> end for;
> end
> @
> insert into test values (1)
> @
> select * from debug1
> @
> drop trigger test_1
> @
> drop table test
> @
> drop table debug1
> @
>
> A
> -----------
> 1
> 2 --> WRONG![/color]
Let's go back to old fashioned varibale tables:
INS.A = 1
LOCAL.Y = INS.A = 1
LOCAL.X IS NULL
1. row:
LOOP.Y = LOCAL.Y = 1
LOCAL.Y = 1 (no change)
LOCAL.X = LOOP.Y = 1
2. row:
LOOP.Y = 2
LOCAL.X = LOOP.Y = 2

The magic question is: Who is Y?
There is a hierarchy here for scoping:
1. INS
2. LOCAL
3. LOOP

Note that "AS SELECT Y FROM.." is in the scope of LOCAL
(it produces LOOP)
while "INSERT.. VALUES(Y)" is in the scope LOOP.
So you insert LOOP.Y which clearly is (1), (2).

Now, when I first read this note I thought you are running into an known
APAR which deals with an inconsistency on FOR processing.
Let's replace your query with:
AS SELECT y FROM T WHERE x=z DO
The body of the FOR loop updates x which poses soem interesting
question: Does this affect the ongoing processing of the FOR loop?
The answer in triggers is (wrongly): Yes. In procedures it's (rightly): No.
If that's what you _really_ run into (assuming you presented a
simplified exampel here) teh work around is to "back up" the changed
variable and refer to it in the AS SELECT ..... DO.
If this is what you run into, please call support.[color=blue]
>
> Thank you
> (Serge, are you there?) :))
>[/color]
No I'm not. I'm in Vienna at the DB2 conference. :-)

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

OK, I'll make a backup of the variable before the loop and use it in
the SELECT.

But, since I'm making an auto-translator of Informix to DB2 code, and I
can't leave this complex scenario to programmers (the directive is to
automate detection of possible failures), I've put a warning in the
output of the translator when the following condition occurs:

1. You are in a trigger
2. You are a loop SELECT
3. The SELECT has UNION
4. You are using a variable in the SELECT-list

Am I right?