🚀 go-pugleaf

I entered this report into QC a few days ago (57839). No comments have been 
made there yet so I thought I would like to here from you guys here in the 
NG. Any comments on this? This change in behaviour between IB2007 and 
earlier versions of IB (tested with IB 7.5 and IB 5.6) has caused as a 
number of unpleasent experiences already.

When using "left join" to a stored proc in a select statement  with IB 2007 
the result set differs (see the NULL values in section 4 below) from the 
result set that IB 7.x and IB 5.6 gives. In the example given here it seems 
like only the last record in the dataset contains the correct information 
from the SP. Hence queries that give only one record back are correct in IB 
2007 too (section 5 below).

Regards,
Kjell

* * * * *

1. TABLE AND SP SETUP
-------------------------------------------
CREATE TABLE TEST_DATA (
    ID    INTEGER NOT NULL,
    QTY   INTEGER,
    QTY2  INTEGER
);

ALTER TABLE TEST_DATA ADD CONSTRAINT PK_TEST_DATA PRIMARY KEY (ID);

CREATE PROCEDURE IS_EQUAL(
    PLINK_ID INTEGER,
    PVALUE1 INTEGER,
    PVALUE2 INTEGER
        )
RETURNS (
    R_LINK_ID INTEGER,
    R_EQUAL CHAR(1)
)
AS
BEGIN
  R_LINK_ID=PLINK_ID;
  if (PVALUE1=PVALUE2) then
    R_EQUAL='T';
  else
    R_EQUAL='F';
suspend;
end


2. TESTDATA
-------------------------
INSERT INTO TEST_DATA (ID, QTY, QTY2 ) VALUES (1, 0, 0);
INSERT INTO TEST_DATA (ID, QTY, QTY2 ) VALUES (2, 1, 0);
INSERT INTO TEST_DATA (ID, QTY, QTY2 ) VALUES (3, 0, 1);


3. QUERY
-----------------
select * from  TEST_DATA T
LEFT JOIN IS_EQUAL(T.ID,T.QTY,T.QTY2) CD
ON CD.R_LINK_ID=T.ID


4. RESULTS
---------------------
IB 2007:
ID QTY QTY2 R_LINK_ID R_EQUAL
1 0 0 NULL NULL
2 1 0 NULL  NULL
3 0 1 3 F


IB 7.5
ID QTY QTY2 R_LINK_ID R_EQUAL
1 0 0 1 T
2 1 0 2 F
3 0 1 3 F


5. SINGLETON QUERY
--------------------------------------
select * from  TEST_DATA T
LEFT JOIN IS_EQUAL(T.ID,T.QTY,T.QTY2)CD ON CD.R_LINK_ID=T.ID
where T.ID=2

IB 2007:
ID QTY QTY2 R_LINK_ID R_EQUAL
2 1 0 2 F

IB 7.5
ID QTY QTY2 R_LINK_ID R_EQUAL
2 1 0 2 F


* * * * *