Issue Details (XML | Word | Printable)

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

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

EXECUTE PROCEDURE's RETURNING_VALUES and EXECUTE STATEMENT's INTO does not check validity of assignments targets leading to bugcheck

Created: 29/May/15 08:11 PM   Updated: 24/Jul/15 02:58 PM
Component/s: Engine
Affects Version/s: 2.5.0, 2.5.1, 2.5.2, 2.5.2 Update 1, 2.5.3, 2.5.3 Update 1, 2.5.4, 2.5.5
Fix Version/s: 3.0 Beta 2

QA Status: Done successfully
Test Details:
Since WI-T3.0.0.31846 all such attempts must lead to:
Statement failed, SQLSTATE = 42000
attempted update of read-only column
(before: internal Firebird consistency check (EVL_assign_to: invalid operation (229), file: evl.cpp line: 205)).


 Description  « Hide
Original discussion (in russian) can be found here: http://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=1158905&msg=17704102

Test:
====

create or alter procedure sp_test(a_x int) as begin end;
recreate table test(id int primary key, x int, y int);

insert into test values(1, 10, 11);
commit;

set term ^;
create or alter procedure sp_test(a_x int) returns(o_y int) as
begin
    o_y = 2 * a_x;
    suspend;
end
^
commit
^

execute block returns(old_y int, new_y int) as
begin
  for
      select x, y from test
      as cursor ce
  do begin
      old_y = ce.y;
      execute procedure sp_test(ce.x) returning_values(ce.y);
      new_y = ce.y;
      suspend;
  end
end
^
rollback
^

Result in WI-T3.0.0.31845: message "internal Firebird consistency check (EVL_assign_to: invalid operation (229), file: evl.cpp line: 205)" appeares in firebird.log

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 29/May/15 09:32 PM
Given that a direct assignment to CE.Y raises the expected "cannot assign to a read-only column" error, it would be interesting to test e.g. RETURNING_VALUES (OLD.Y) in after-triggers.

Pavel Zotov added a comment - 30/May/15 07:13 AM - edited
> it would be interesting to test e.g. RETURNING_VALUES (OLD.Y) in after-triggers.

Test-2:
######

create or alter procedure sp_test(a_x int) as begin end;
recreate table test(id int primary key, x int, y int);

insert into test values(1, 10, 11);

---------------------
select 'initial state' msg, t.* from test t;
---------------------
commit;

set term ^;
create or alter procedure sp_test(a_x int) returns(o_y int) as
begin
    o_y = a_x * a_x;
    suspend;
end
^
commit
^
create or alter trigger test_aud for test active after insert or update or delete as
begin
    if (not inserting) then
        execute procedure sp_test(old.x) returning_values(old.y);
    else
        execute procedure sp_test(new.x) returning_values(new.y);
end
^
set term ;^
commit;

---------------------

update test set x = 47 where id = 1
returning 'update-returning:' as msg, old.x as old_x, new.x as new_x, old.y as old_y, new.y as new_y;

select 'after update' msg, t.* from test t;

---------------------

insert into test(id, x) values(2, 19)
returning 'insert-returning:' as msg, x as new_x, y as new_y;

select 'after insert' msg, t.* from test t where id=2;


Output:
######

MSG ID X Y
============= ============ ============ ============
initial state 1 10 11


MSG OLD_X NEW_X OLD_Y NEW_Y
================= ============ ============ ============ ============
update-returning: 10 47 100 11


MSG ID X Y
============= ============ ============ ============
after update 1 47 11


MSG NEW_X NEW_Y
================= ============ ============
insert-returning: 19 361


MSG ID X Y
============= ============ ============ ============
after insert 2 19 <null>

Pavel Zotov added a comment - 24/Jul/15 02:54 PM
Can this fix be backported to 2.5.x ?
Because this:
===
    create or alter procedure sp_test(a_x int) as begin end;
    recreate table test(id int primary key, x bigint, y bigint);
    commit;

    set term ^;
    create or alter procedure sp_test(a_x int) returns(o_y int) as
    begin
        o_y = 7 * a_x;
        suspend;
    end
    ^
    commit
    ^
    create or alter trigger test_aud2 for test active
             AFTER ------------------------------------------------- <<< ::::: NB :::::
             insert or update or delete as
    begin
        if (not inserting) then
            begin
                execute statement ('execute procedure sp_test( ? )') ( 2 * old.x ) into old.x;
                execute statement ('execute procedure sp_test( ? )') ( 2 * old.x ) into old.y;
            end
        else
            begin
                execute statement ('execute procedure sp_test( ? )') ( 2 * new.x ) into new.x;
                execute statement ('execute procedure sp_test( ? )') ( 2 * new.x ) into new.y;
            end
    
        rdb$set_context( 'USER_SESSION', 'X_TRG_AIUD2', iif( inserting, new.x, old.x ) );
        rdb$set_context( 'USER_SESSION', 'Y_TRG_AIUD2', iif( inserting, new.y, old.y ) );
    end
    ^
    set term ;^
    commit;
===

-- does not produce error in WI-V2.5.5.26916 (expected: "Statement failed, SQLSTATE = 42000 \ attempted update of read-only column")