Called procedure parameter changes during execution without assignment when deleting from updatable view in Firebird 5 #8794
Description
Hello,
When executing the following script in Firebird 5.0.3 via isql.exe on Windows Server 2019 x64 (intermittently we saw this happening on Firebird 3.0.12 and Firebird 4.0.5, but when filing this bug report we could not reproduce it):
SET SQL DIALECT 3;
CREATE DATABASE 'test.fdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET NONE COLLATION NONE;
CREATE TABLE TABLE1 (
COL1 INTEGER NOT NULL,
COL2 INTEGER,
COL3 VARCHAR(30),
COL4 VARCHAR(50),
COL5 VARCHAR(50)
);
CREATE VIEW VIEW1(
COL1,
COL2,
COL3,
COL4,
COL5)
AS
select * from TABLE1;
INSERT INTO TABLE1 (COL1, COL2, COL3, COL4, COL5)
VALUES (1, 1, 'placeholder', 'placeholder', 'placeholder');
COMMIT WORK;
SET TERM ^ ;
CREATE TRIGGER VIEW1_TRIGGER FOR VIEW1
ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 32767
as
declare variable a integer;
begin
a = 1;
end^
SET TERM ; ^
SET TERM ^ ;
CREATE PROCEDURE PROCEDURE1 (
PARAM_COL2 INTEGER,
PARAM_COL3 VARCHAR(30))
RETURNS (
DEBUG_OUT VARCHAR(110))
AS
declare variable SELECT_RES1 integer;
declare variable SELECT_RES2 integer;
begin
DEBUG_OUT = 'select'
|| ', params: PARAM_COL2: "' || coalesce(:PARAM_COL2, 'null')
|| '", PARAM_COL3: "' || coalesce(:PARAM_COL3, 'null') || '"';
suspend;
select first(1) COL2
from VIEW1 -- use TABLE1 for correct output
where (COL2 = :PARAM_COL2) and
(COL3 = :PARAM_COL3)
into :SELECT_RES1;
DEBUG_OUT = 'select, result: ' || RPAD(coalesce(:SELECT_RES1, 'null'), 4)
|| ', params: PARAM_COL2: "' || coalesce(:PARAM_COL2, 'null')
|| '", PARAM_COL3: "' || coalesce(:PARAM_COL3, 'null') || '"';
suspend;
DEBUG_OUT = 'delete'
|| ', params: PARAM_COL2: "' || coalesce(:PARAM_COL2, 'null')
|| '", PARAM_COL3: "' || coalesce(:PARAM_COL3, 'null') || '"';
suspend;
delete from VIEW1 -- use TABLE1 for correct output
where (COL2 = :PARAM_COL2) and
(COL3 = :PARAM_COL3);
DEBUG_OUT = 'delete, result: ' || RPAD(row_count, 4)
|| ', params: PARAM_COL2: "' || coalesce(:PARAM_COL2, 'null')
|| '", PARAM_COL3: "' || coalesce(:PARAM_COL3, 'null') || '"';
suspend;
DEBUG_OUT = 'select'
|| ', params: PARAM_COL2: "' || coalesce(:PARAM_COL2, 'null')
|| '", PARAM_COL3: "' || coalesce(:PARAM_COL3, 'null') || '"';
suspend;
select first(1) COL2
from VIEW1 -- use TABLE1 for correct output
where (COL2 = :PARAM_COL2) and
(COL3 = :PARAM_COL3)
into :SELECT_RES2;
DEBUG_OUT = 'select, result: ' || RPAD(coalesce(:SELECT_RES2, 'null'), 4)
|| ', params: PARAM_COL2: "' || coalesce(:PARAM_COL2, 'null')
|| '", PARAM_COL3: "' || coalesce(:PARAM_COL3, 'null') || '"';
suspend;
end^
SET TERM ; ^
select * from procedure1(1, 'placeholder');
commit;
--drop database;we get the following output:
select, params: PARAM_COL2: "1", PARAM_COL3: "placeholder"
select, result: 1 , params: PARAM_COL2: "1", PARAM_COL3: "placeholder"
delete, params: PARAM_COL2: "1", PARAM_COL3: "placeholder"
delete, result: 0 , params: PARAM_COL2: "0", PARAM_COL3: "placeholder" <-- this row, PARAM_COL2 is changed
select, params: PARAM_COL2: "0", PARAM_COL3: "placeholder"
select, result: null, params: PARAM_COL2: "0", PARAM_COL3: "placeholder"
The problem is that there is no assignment to the PARAM_COL2 parameter and its value changes from 1 to 0 after the delete statement, which we think should not happen.
If the three select/delete statements in the procedure use directly the table TABLE1 instead of the updateable view VIEW1, we get the output we expect (PARAM_COL2 does not change after the delete statement):
select, params: PARAM_COL2: "1", PARAM_COL3: "placeholder"
select, result: 1 , params: PARAM_COL2: "1", PARAM_COL3: "placeholder"
delete, params: PARAM_COL2: "1", PARAM_COL3: "placeholder"
delete, result: 1 , params: PARAM_COL2: "1", PARAM_COL3: "placeholder" <-- this row, PARAM_COL2 is NOT changed
select, params: PARAM_COL2: "1", PARAM_COL3: "placeholder"
select, result: null, params: PARAM_COL2: "1", PARAM_COL3: "placeholder"
Regards,
Hristo Stefanov