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

Stored procedure recursively called by calculated field fails after reconnect [CORE2032] #2469

Closed
firebird-automations opened this issue Aug 4, 2008 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ] => Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE6351 [ CORE6351 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

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

resolution: Fixed [ 1 ]

Fix Version: 4.0.0 [ 10931 ]

Fix Version: 3.0.8 [ 10960 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

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