Submitted by: Poul Dige (tabulex)

Is related to CORE6351

After reconnect the follwing sceance fails to return correct result
After recompiling the stored procedure correct results are returned - until next reconnect!

Followning SQL:
select CALC_FIELD from ATABLE where ID = 0
should return 20 but returns 10 after a reconnect (until stored procedure is recompiled).

Script:

/******************************************************************************/
/* Generated by IBExpert 2008.05.08 03-08-2008 00:40:51 */
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES ISO8859_1;

CREATE DATABASE 'C:\strange_procedure.fdb'
USER 'SYSDBA' PASSWORD 'MASTERKEY'
PAGE_SIZE 4096
DEFAULT CHARACTER SET ISO8859_1;

SET TERM ^ ;

/******************************************************************************/
/* Stored Procedures */
/******************************************************************************/

CREATE OR ALTER PROCEDURE STRANGE_PROCEDURE (
RESULT_TO_RETURN FLOAT,
REFERENCE_ID INTEGER)
RETURNS (
RESULT FLOAT)
AS
BEGIN
SUSPEND;
END^

SET TERM ; ^

/******************************************************************************/
/* Tables */
/******************************************************************************/

CREATE TABLE ATABLE (
ID INTEGER NOT NULL,
RESULT_TO_RETURN FLOAT,
REFERENCE_ID INTEGER,
CALC_FIELD COMPUTED BY ((
select
RESULT
from
STRANGE_PROCEDURE(
ATABLE.RESULT_TO_RETURN,
ATABLE.REFERENCE_ID
)
))
);

INSERT INTO ATABLE (ID, RESULT_TO_RETURN, REFERENCE_ID) VALUES (1, 20, 0);
INSERT INTO ATABLE (ID, RESULT_TO_RETURN, REFERENCE_ID) VALUES (0, 10, NULL);

COMMIT WORK;

/******************************************************************************/
/* Stored Procedures */
/******************************************************************************/

SET TERM ^ ;

CREATE OR ALTER PROCEDURE STRANGE_PROCEDURE (
RESULT_TO_RETURN FLOAT,
REFERENCE_ID INTEGER)
RETURNS (
RESULT FLOAT)
AS
declare variable sum_of_referenced_items float;
begin
-- Initialize variables
RESULT = RESULT_TO_RETURN;
SUM_OF_REFERENCED_ITEMS = 0;

\-\- If reference is null then it
\-\- must be record ID = 0
if \(REFERENCE\_ID is null\) then
begin
    select sum\(T\.CALC\_FIELD\)
    from ATABLE T where T\.REFERENCE\_ID = 0
    into :SUM\_OF\_REFERENCED\_ITEMS;

    if \(SUM\_OF\_REFERENCED\_ITEMS \> :RESULT\)
    then RESULT = SUM\_OF\_REFERENCED\_ITEMS;
end

suspend;

end^

SET TERM ; ^

Commits: b6b6897 40e1b3c