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

By: Borland Staff

Abstract: Resolved in Interbase 5.0

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)





Server Response from: ETNASC01