Wrong results of FOR SELECT <L> FROM <T> AS CURSOR <C> and table <T> is
modified inside cursor's begin...end block
------------------------------------------------------------------------------------------------------------------
Key: CORE-4488
URL: http://tracker.firebirdsql.org/browse/CORE-4488
Project: Firebird Core
Issue Type: Bug
Reporter: Pavel Zotov
LI-T3.0.0.31208
DDL:
====
recreate table sss(id int, x int, y int, z int); commit;
recreate table ttt(id int, x int, y int, z int); commit;
insert into sss values(1, 10, 100, 1000);
insert into sss values(2, 20, 200, 2000);
insert into sss values(3, 30, 300, 3000);
commit;
Sample-1:
=========
set term ^;
execute block
as
begin
for
select
id,
x as x,
y as y,
z as z
from sss s
as cursor cs
do begin
delete from sss where current of cs; -- yes, we DELETE record BEFORE insert
it's values because now they all are stored in cursor 'CS' variables
insert into ttt values( cs.id, cs.x, cs.y, cs.z );
end
end
^ set term ;^
select * from sss;
select * from ttt;
rollback;
Result-1:
=======
ID X Y Z
=== ============ ============
1 10 100 1000
2 20 200 2000
3 30 300 3000
So, it's Ok.
Sample-2:
=========
set term ^;
execute block
as
begin
for
select
id,
x+1 as x,
y+2 as y,
z+3 as z
from sss s
as cursor cs
do begin
delete from sss where current of cs;
insert into ttt values( cs.id, cs.x, cs.y, cs.z );
end
end
^ set term ;^
select * from sss;
select * from ttt;
rollback;
Result-2:
========
ID X Y Z
== ============ ============
1 <null> <null> <null>
2 <null> <null> <null>
3 <null> <null> <null>
Why all columns which were defined as *expressions* are NULL in target table ?
Sample-3:
========
select 'sss, init' msg, s.* from sss s;
set term ^;
execute block
as
begin
for
select
id,
x as x,
y as y,
z+1 as z
from sss s
as cursor cs
do begin
update sss set y=x, z=y, x=z where current of cs;
insert into ttt values( cs.id, cs.x, cs.y, cs.z );
end
end
^ set term ;^
select 'sss, new:' msg, s.* from sss s;
select 'ttt, new:' msg, t.* from ttt t;
rollback;
Result:
======
SQL> in cur3;
MSG ID X Y Z
========= ============ ============
sss, init 1 10 100 1000
sss, init 2 20 200 2000
sss, init 3 30 300 3000
MSG ID X Y Z
========= ============ ============
sss, new: 1 1000 10 100
sss, new: 2 2000 20 200
sss, new: 3 3000 30 300
MSG ID X Y Z
========= ============ ============
ttt, new: 1 1000 10 101
ttt, new: 2 2000 20 201
ttt, new: 3 3000 30 301
Why values in TTT table have been affected by changes in table SSS after cursor
has read them ?
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Open source business process management suite built on Java and Eclipse
Turn processes into business applications with Bonita BPM Community Edition
Quickly connect people, data, and systems into organized workflows
Winner of BOSSIE, CODIE, OW2 and Gartner awards
http://p.sf.net/sfu/Bonitasoft
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
