🚀 go-pugleaf

Yes. The results below are with IB 2007 SP2.

/Kjell


"Quinn Wildman (CodeGear Developer Support)" <qwildman@nospam.codegear.com> 
wrote in message news:47ac873d$1@newsgroups.borland.com...
> 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
>>
>>
>> * * * * *
>>