[Firebird-devel] [FB-Tracker] Created: (CORE-4483) Changed data not visible in WHEN-section if exception occured inside SP that has been called from this code

[Pavel Z. (JIRA) <tr...@fi...>]

Changed data not visible in WHEN-section if exception occured inside SP that has been called from this code
-----------------------------------------------------------------------------------------------------------

                 Key: CORE-4483
                 URL: http://tracker.firebirdsql.org/browse/CORE-4483
             Project: Firebird Core
          Issue Type: Bug
    Affects Versions: 3.0 Alpha 2
            Reporter: Pavel Zotov


1. DDL:
======
create or alter procedure dlock(a_id int) as begin end;
recreate table tlog(id int);
recreate table dlist(id int primary key);
commit;
insert into dlist values(1);
commit;
set term ^;
create or alter procedure dlock(a_id int) as
    declare v_id int;
begin
    update dlist set id = id where id = :a_id;
    --select id from dlist where id = :a_id for update with lock into v_id;
end
^set term ;^
commit;

2. Create THREE databases on:
1) LI-V2.5.3.26744 (yes, FB 2.5)
2) LI-T3.0.0.31202
3) LI-T3.0.0.31208
- and apply this DDL on them.

3. Create the following test (named e.g. 'catch.sql'):

show version;
rollback;
set transaction no wait;
set term ^;
execute block as
begin
    delete from tlog;
    insert into tlog(id) values(1);
    rdb$set_context('USER_TRANSACTION', 'CNT_IN_MAIN_CODE',(select count(*) from tlog));

    --update dlist set id = id order by id rows 1;
    execute procedure dlock(1);

when any do
    begin
        rdb$set_context('USER_TRANSACTION','CNT_IN_WHEN_SECTION',(select count(*) from tlog));
        exception;
    end
end
^ set term ;^
set list on;
select 
   rdb$get_context('USER_TRANSACTION','CNT_IN_MAIN_CODE') as CNT_IN_MAIN_CODE
  ,rdb$get_context('USER_TRANSACTION','CNT_IN_WHEN_SECTION') as CNT_IN_WHEN_SECTION 
from rdb$database;
rollback;

The column CNT_IN_WHEN_SECTION  will contain the number of rows in the table TLOG which should be seen inside WHEN-section.
Obviously, it should be equal to 1.

4. Open three times a couple of ISQL sessions (for each of server versions listed above) and do:

4.1) session #1: 
SQL> select id from dlist; -- only to ensure that at least one row exists;
SQL> update dlist set id = id;

4.2) session #2:
SQL> in catch.sql;

I have the following results in session #2 (error "update conflicts" message was skipped):

1.LI-V2.5.3.26744:
==============
CNT_IN_MAIN_CODE                1
CNT_IN_WHEN_SECTION             0

2. LI-T3.0.0.31202:
===============
CNT_IN_MAIN_CODE                1
CNT_IN_WHEN_SECTION             1

3. LI-T3.0.0.31208:
===============
CNT_IN_MAIN_CODE                1
CNT_IN_WHEN_SECTION             0

The result in LI-T3.0.0.31202 seems to me solely correct.
Something was broken after this build #31202 ?

PS.

No problem found if we change in DML script ("catch.sql"):

    execute procedure dlock(1);

to:

    update dlist set id = id order by id rows 1;

(i.e. this trouble is somehow related to call of SP)

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