[All]
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)