Hello guys,

today I've stumbled on some strange (in my opinion) behaviour.

Firstly, create these two structures:

CREATE GLOBAL TEMPORARY TABLE GLB_CS_TEMP
(
  ID_MON_OBJECT INTEGER NOT NULL,
  ID_PARAM INTEGER NOT NULL,
  MEASUREMENT_DATE TIMESTAMP,
  PARAM_VALUE VARCHAR(64) DEFAULT '',
  CONSTRAINT PK_GLB_CS_TEMP PRIMARY KEY (ID_MON_OBJECT,ID_PARAM)
);

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON GLB_CS_TEMP TO  SYSDBA WITH GRANT OPTION;

SET TERM ^ ;
CREATE PROCEDURE PROC_SPLIT_STRING (
    P_STRING VARCHAR(32000),
    P_SPLITTER CHAR(1) )
RETURNS (
    PART VARCHAR(32000) )
AS
DECLARE VARIABLE LASTPOS INTEGER;
DECLARE VARIABLE NEXTPOS INTEGER;
BEGIN
  P_STRING = :P_STRING || :P_SPLITTER;
  LASTPOS = 1;
  NEXTPOS = POSITION(:P_SPLITTER, :P_STRING, LASTPOS);
  IF (LASTPOS = NEXTPOS) THEN
  BEGIN
    PART = SUBSTRING(:P_STRING FROM :LASTPOS FOR :NEXTPOS - :LASTPOS); 
    SUSPEND;
    LASTPOS = :NEXTPOS + 1;
    NEXTPOS = POSITION(:P_SPLITTER, :P_STRING, LASTPOS);   
  END
  WHILE (:NEXTPOS > 1) do
  BEGIN
    PART = SUBSTRING(:P_STRING FROM :LASTPOS FOR :NEXTPOS - :LASTPOS);
    LASTPOS = :NEXTPOS + 1;
    NEXTPOS = POSITION(:P_SPLITTER, :P_STRING, LASTPOS);   
    SUSPEND;
  END
END
^
SET TERM ; ^

GRANT EXECUTE
 ON PROCEDURE PROC_SPLIT_STRING TO  SYSDBA;


Then run this query:

    SELECT A.MO, A.PA, G.PARAM_VALUE
    FROM
      (
        SELECT 0 AS MO, CAST(PAR.PART AS INTEGER) AS PA
        FROM PROC_SPLIT_STRING('', ',') PAR
        WHERE PAR.PART <> ''
         
      ) A
      ,
        GLB_CS_TEMP G
      WHERE
        A.MO=G.ID_MON_OBJECT AND A.PA = G.ID_PARAM

You should get an error: conversion error from string ""

Now change the WHERE condition and instead A.PA = G.ID_PARAM write A.PA = COALESCE(G.ID_PARAM,0)
and now it works!?

I do not understand why it is not working at the first place, GLB_CS_TEMP is empty, and the result from subquery A is also empty. Very strange, should I report this as a bug or is there an explanation for this?

Firebird version 2.5.4.26856

Thank you.