You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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( http://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( http://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( http://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 ?
Cursor references are not variables, they're not cached when reading. Instead, they represent the current state of the record. If it was updated "in place" (via "where current of"), then cursor references should return new values. So IMO the bug is actually in the first sample which should return NULLs as it did for expressions.
> Cursor references are not variables, they're not cached when reading. Instead, they represent the current state of the record. If it
> was updated "in place" (via "where current of"), then cursor references should return new values.
IMHO, this sentence should be added to README.cursor_variables.txt
Submitted by: @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( http://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( http://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( http://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 ?
Commits: a73629a 0478d4f FirebirdSQL/fbt-repository@f884a61 FirebirdSQL/fbt-repository@870cf2e
====== Test Details ======
See also: %FB_HOME%\doc\sql.extensions\README.cursor_variables.txt
The text was updated successfully, but these errors were encountered: