🚀 go-pugleaf

For InterBase 2007, do you have SP2?

Kjell Ljungqvist wrote:
> 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
> 
> 
> * * * * *
> 
>