Issue Details (XML | Word | Printable)

Key: CORE-4488
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Pavel Zotov
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

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

Created: 08/Jul/14 06:40 PM   Updated: 23/Sep/15 11:27 AM
Component/s: None
Affects Version/s: None
Fix Version/s: 3.0 Beta 1

QA Status: Done successfully
Test Details: See also: %FB_HOME%\doc\sql.extensions\README.cursor_variables.txt


 Description  « Hide
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 ?


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 08/Jul/14 06:54 PM
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.

Pavel Zotov added a comment - 08/Jul/14 07:39 PM
> 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

Adriano dos Santos Fernandes made changes - 11/Jul/14 03:25 PM
Field Original Value New Value
Assignee Adriano dos Santos Fernandes [ asfernandes ]
Adriano dos Santos Fernandes made changes - 21/Jul/14 02:38 AM
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 3.0 Beta 1 [ 10332 ]
Resolution Fixed [ 1 ]
Pavel Zotov made changes - 29/May/15 04:47 PM
Status Resolved [ 5 ] Resolved [ 5 ]
Test Details See also: %FB_HOME%\doc\sql.extensions\README.cursor_variables.txt
QA Status Done successfully
Pavel Cisar made changes - 23/Sep/15 11:27 AM
Status Resolved [ 5 ] Closed [ 6 ]