Bug No 8447: A For SELECT . . . SELECT in a Stored Procedure drops IBServer

Abstract: Bug No 8447: A For SELECT . . . SELECT in a Stored Procedure drops IBServer

Problem: When doing a: SELECT * FROM SP_DRWATSON in ISQL or WISQL on the database DRWATSON.GDB, an access violation occurs that drops IBServer.exe. Solution: Note: This is fixed in version v5.0 The stored procedure and the tables being used are shown below. On the line: WHERE sl.gid_ls = 4 /* choose a value that returns multiple */ /* records */ if another value is chosen that does not return multiple records, then query runs fine. If the join on the inner select statement is eliminated then the stored procedure runs fine. If a similar query is constructed on the tables in the Examples.gdb database then the error -811: "multiple rows in singleton select" occurs. InterBase should return this error instead of crashing. CREATE PROCEDURE sp_drwatson RETURNS (DEV1 integer ) AS DECLARE VARIABLE SL_GID_ST integer; BEGIN FOR SELECT sl.gid_st FROM sl_sign_link sl WHERE sl.gid_ls = 4 /* choose a value that returns */ /* multiple records */ INTO :SL_GID_ST DO BEGIN SELECT st.gid_st FROM st_sign_term st, dev WHERE st.gid_st = :SL_GID_ST AND dev.gid_st = st.gid_st INTO :DEV1; SUSPEND; END END The structures for the tables are: SHOW TABLE sl_sign_link sl GID_LS (T_INTERNAL_ID) INTEGER Not Null SLC (T_2_NUMBER) SMALLINT Not Null GID_ST (T_INTERNAL_ID) INTEGER Nullable SDL (T_SIGNALLING_DATA_LINK) VARCHAR(31) Not Null CONSTRAINT PK_SL_SIGN_LINK: Primary key (GID_LS, SLC) SHOW TABLE st_sign_term st GID_ST (T_INTERNAL_ID) INTEGER Not Null GID_NODE (T_INTERNAL_ID) INTEGER Not Null CONSTRAINT PK_ST_SIGN_TERM: Primary key (GID_ST) SHOW TABLE dev GID_NODE (T_INTERNAL_ID) INTEGER Not Null ETC_NO (T_ETC_NO) SMALLINT Not Null TSLOT (T_TIME_SLOT) SMALLINT Not Null GID_ST (T_INTERNAL_ID) INTEGER Nullable CONSTRAINT PK_DEV: Primary key (GID_NODE, ETC_NO, TSLOT)
-->