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
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 ]