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

EXECUTE PROCEDURE's RETURNING_VALUES and EXECUTE STATEMENT's INTO does not check validity of assignments targets leading to bugcheck [CORE4819] #5116

Closed
firebird-automations opened this issue May 29, 2015 · 11 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

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

Commits: f86a15c FirebirdSQL/fbt-repository@06eb0ba FirebirdSQL/fbt-repository@5cd5e0e

====== 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)).

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

summary: Running "execute procedure sp_test(ce.x) returning_values(ce.y)", where 'ce' is CURSOR, leads to crash. => EXECUTE PROCEDURE's RETURNING_VALUES and EXECUTE STATEMENT's INTO does not check validity of assignments targets leading to bugcheck

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 2 [ 10586 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Version: 3.0 Beta 1 [ 10332 ]

Version: 3.0 Beta 2 [ 10586 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

Version: 2.5.4 [ 10585 ]

Version: 2.5.3 Update 1 [ 10650 ]

Version: 2.5.3 [ 10461 ]

Version: 2.5.2 Update 1 [ 10521 ]

Version: 2.5.2 [ 10450 ]

Version: 2.5.1 [ 10333 ]

Version: 2.5.0 [ 10221 ]

Version: 2.5.5 [ 10670 ]

Version: 3.0 Beta 1 [ 10332 ] =>

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