Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Wrong results of FOR SELECT <L> FROM <T> AS CURSOR <C> and table <T> is modified inside cursor's begin...end block [CORE4488] #4808

Closed
firebird-automations opened this issue Jul 8, 2014 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

> 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

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 1 [ 10332 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

Test Details: See also: %FB_HOME%\doc\sql.extensions\README.cursor_variables.txt

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants