Issue Details (XML | Word | Printable)

Key: CORE-2032
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Poul Dige
Votes: 0
Watchers: 2
Operations

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

Stored procedure recursively called by calculated field fails after reconnect

Created: 04/Aug/08 06:01 AM   Updated: 14/Mar/12 04:24 PM
Component/s: Engine
Affects Version/s: 2.1.0, 2.1.1
Fix Version/s: None

Environment: Windows 2003 Server, FB2.1.0 released version and FB2.1.1 released version, both server+embedded


 Description  « Hide
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 ; ^




 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.