Issue Details (XML | Word | Printable)

Key: CORE-1320
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Adam Gardner
Votes: 0
Watchers: 2
Operations

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

Changing the number of type of parameters in stored procedure does not check dependencies

Created: 14/Jun/07 07:26 AM   Updated: 04/Nov/09 01:08 PM
Component/s: None
Affects Version/s: 1.5.4, 2.0.1, 2.1.3
Fix Version/s: None

Environment: XP SP2 Classic Server 1.5.4 and 2.0.1


 Description  « Hide
When you have a stored procedure that utilises another stored procedure, Firebird allows you to modify the parameters of the internal stored procedure without complaint. This leads to both a situation where backups will not correctly restore, and the outer procedure encountering a runtime exception.

Script to duplicate in iSQL
(WARNING, this will cause the backup of the database to fail, so don't perform this on databases containing important data)
----

SET TERM ^ ;
CREATE PROCEDURE SP_HAS_FIVEPARAMS
(
  VPARAM1 INTEGER,
  VPARAM2 INTEGER,
  VPARAM3 INTEGER,
  VPARAM4 INTEGER,
  VPARAM5 INTEGER
)
RETURNS
(
  OUTPUTPARAM INTEGER
)
AS
BEGIN
  OUTPUTPARAM = :VPARAM1 + :VPARAM2 + :VPARAM3 + :VPARAM4 + :VPARAM5;
  SUSPEND;
END
^

CREATE PROCEDURE SP_OUTER_PROC
RETURNS
(
  OUTERPARAM INTEGER
)
AS
BEGIN
  SELECT OUTPUTPARAM
    FROM SP_HAS_FIVEPARAMS(1,2,3,4,5)
    INTO :OUTERPARAM;
  SUSPEND;
END
 ^
SET TERM ;
^

COMMIT;

/* calling SP_OUTER_PROC returns 15 here as expected */
SELECT * from SP_OUTER_PROC;

/* Now we modify SP_HAS_FIVEPARAMS. IMO, this should fail because of the dependency in SP_OUTER_PROC */

SET TERM ^ ;
ALTER PROCEDURE SP_HAS_FIVEPARAMS
(
  VPARAM1 INTEGER,
  VPARAM2 INTEGER,
  VPARAM4 INTEGER,
  VPARAM5 INTEGER
)
RETURNS
(
  OUTPUTPARAM INTEGER
)
AS
BEGIN
  OUTPUTPARAM = :VPARAM1 + :VPARAM2 + :VPARAM4 + :VPARAM5;
  SUSPEND;
END
^

SET TERM ; ^
COMMIT;

/* In Firebird 2, you need to close and relaunch iSQL here so it does not cache the old SP_HAS_FIVEPARAMS. */
/* The following DML now raises an exception */

SELECT * from SP_OUTER_PROC;

/* We can live with the exception, however the exception also prevents any backup taken from this point on from restoring correctly. Views, Stored Procedures and Triggers are not restored. A data pump is required to completely restore the backup. */

---

Let me know if you need anything else.

Adam


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Fergus McDonald added a comment - 16/May/08 01:33 PM - edited
This problem can result in the calling procedure operating incorrectly with no exception being reported. For example:

SET TERM ^ ;
CREATE DATABASE 'mltest'^
COMMIT^
connect 'mltest'^
CREATE PROCEDURE PSUB (test char(15)) returns (test2 char(15)) as begin test2 = test; suspend; end^
CREATE PROCEDURE PCALLER returns (test3 varchar(30)) as begin select test2 || 'x' from psub('abc') into :test3; suspend; end^
COMMIT^
ALTER PROCEDURE PSUB (test varchar(13)) returns (test2 varchar(13)) as begin test2 = test; suspend; end^
COMMIT^
set heading off^
select test3 from pcaller^
ALTER PROCEDURE PCALLER returns (test3 varchar(30)) as begin select test2 || 'x' from psub('abc') into :test3; suspend; end^
COMMIT^
select test3 from pcaller^


Results in the following output:

c x

abcx

As you can see, the PCALLER proc returns a different result after it is recompiled without changes, but no exceptions are produced at any point.

Philippe Makowski added a comment - 04/Nov/09 01:08 PM
I raise the severity of this bug
I confirm it under 2.1.3
and it is really a problem since it lead to unrestorable backup
we really should better check dependency between SP.
in support list there is another test case similar ([firebird-support] update parameter list of a stored procedure
Date : Tue, 3 Nov 2009 11:18:41 +0000 (UTC) )