W dniu 2015-06-23 10:38:25 użytkownik brucedickin...@wp.pl [firebird-support]
<firebird-support@yahoogroups.com> napisał:
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 INTEGE R) 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.
Hi,
it looks to me like a bug
the same is for simple proc and empty table
SET TERM ^ ;
ALTER 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
PART ='';
SUSPEND;
END
^
SET TERM ; ^
and select
SELECT A.MO, A.PA, G.PARAM_VALUE
FROM
(
SELECT 0 AS MO, PAR.PART 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
regards,
Karol Bieniaszewski
