|
| Joining stored procs in a query gives different results in IB 2007 and IB 7.5 |
 |
Thu, 7 Feb 2008 23:17:04 +0200 |
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
* * * * *
|
| Post Reply
|
| Re: Joining stored procs in a query gives different results in IB |
 |
Fri, 08 Feb 2008 08:45:26 -080 |
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
>
>
> * * * * *
>
|
| Post Reply
|
| Re: Joining stored procs in a query gives different results in IB 2007 and IB 7.5 |
 |
Fri, 8 Feb 2008 19:52:25 +0200 |
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
>>
>>
>> * * * * *
>>
|
| Post Reply
|
| Re: Joining stored procs in a query gives different results in IB 2007 and IB 7.5 |
 |
Tue, 12 Feb 2008 10:42:56 +020 |
Should I expect any comments on this in either QC or here?
As I see it this is a rather serious change in behaviour (a bug) and it is
actually one of the first times that we have to go through our code and
change something that has worked perfectly earlier but stops working when
the customer site is upgraded to IB 2007 SP2.
Regards,
Kjell
"Kjell Ljungqvist" <kjell@REMOVETHIS.iki.fi> wrote in message
news:47ac96dc@newsgroups.borland.com...
> 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
>>>
>>>
>>> * * * * *
>>>
>
|
| Post Reply
|
|
|