Re: IB 2007 SP2 left outer join bug

Arno Brinkman wrote:

> I think you should add an entry in QC with your reproducable script.

	I agree.

-- 
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
  Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Please read and follow Borland's rules for the user of their 

Hi,

> I want to post my experiance here of what seems to be a bug introduced in
2007 SP2 for discussion and to later maybe 
> place a report with quality central.
>
> We have many queries that use left outer joins that up until the
instalation of SP2 were wroking fine.  After 
> installing SP2 however, we got incorrect result sets back from some them.

> For testing I adjusted one of our queries to look something like this:
>
> SELECT T1.ID, T2.T1_ID
>  FROM T1
>    LEFT OUTER JOIN T2 ON (T2.T1_ID = T1.ID) AND (T2.SESSION_ID = 1)
>
> I got the result set that I expected.
> eg:
> 1, 1
> 2, NULL
> 3, NULL
> 4, 4
>
> but it I added the WHERE (T2.T1_ID IS NULL) clause again I got
>
> 1, NULL
> 2, NULL
> 3, NULL
> 4, NULL

That's definitly wrong.

I think you should add an entry in QC with your reproducable script.


Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database with many powerful SQL features:
  http://www.firebirdsql.org
  http://www.firebirdsql.info

General database developer support:
  http://www.databasedevelopmentforum.com

Support list for Interbase and Firebird users:
  firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep:
  news://newsgroups.firebirdsql.info

Hello all,

I want to post my experiance here of what seems to be a bug introduced in 
2007 SP2 for discussion and to later maybe place a report with quality 
central.

We have many queries that use left outer joins that up until the instalation 
of SP2 were wroking fine.  After installing SP2 however, we got incorrect 
result sets back from some them.

We mainly use left outer joins to find the difference between two sets of 
data.  For example:

SELECT T1.ID
  FROM T1
    LEFT OUTER JOIN T2 ON (T2.T1_ID = T1.ID) AND (T2.SESSION_ID = 1)
  WHERE (T2.T1_ID IS NULL)

(Note: See end of this post for more details on table structure and data for 
all examples)

In pre SP2 this would give us all records from T1 that where not already 
present in T2 for that particular session id.  Post SP2, we get everything 
from T1.

For testing I adjusted one of our queries to look something like this:

SELECT T1.ID, T2.T1_ID
  FROM T1
    LEFT OUTER JOIN T2 ON (T2.T1_ID = T1.ID) AND (T2.SESSION_ID = 1)

I got the result set that I expected.
eg:
1, 1
2, NULL
3, NULL
4, 4

but it I added the WHERE (T2.T1_ID IS NULL) clause again I got

1, NULL
2, NULL
3, NULL
4, NULL

where as I was expecting

2, NULL
3, NULL

In looking at the release notes of SP2 I see that much work has been done 
with outer joins.  Now I'm left wondering, have we been actualy exploiting a 
long standing bug in InterBase, or have we found a new one?

Any thoughts would be much appreciated.  For now were have removed SP2 on 
our server.

Cheers,

Nick Barrett

BTW, I'm not sure if this is related to QC#43874, but since things work 
correctly pre SP2 and not post, I'm guessing it is not.


Example table structures *******************************************

CREATE TABLE SESSION (
    ID  INTEGER NOT NULL);

CREATE TABLE T1 (
    ID  INTEGER NOT NULL);

CREATE TABLE T2 (
    ID          INTEGER NOT NULL,
    T1_ID       INTEGER NOT NULL,
    SESSION_ID  INTEGER NOT NULL);

ALTER TABLE SESSION ADD CONSTRAINT P_SESSION_ID PRIMARY KEY (ID);
ALTER TABLE T1 ADD CONSTRAINT P_T1_ID PRIMARY KEY (ID);
ALTER TABLE T2 ADD CONSTRAINT P_T2_ID PRIMARY KEY (ID);

ALTER TABLE T2 ADD CONSTRAINT FK_SESSION_T2 FOREIGN KEY (SESSION_ID) 
REFERENCES SESSION (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE T2 ADD CONSTRAINT FK_T1_T2 FOREIGN KEY (T1_ID) REFERENCES T1 
(ID) ON DELETE CASCADE ON UPDATE CASCADE;

Example data *******************************************

INSERT INTO SESSION (ID) VALUES (1);

INSERT INTO T1 (ID) VALUES (1);
INSERT INTO T1 (ID) VALUES (2);
INSERT INTO T1 (ID) VALUES (3);
INSERT INTO T1 (ID) VALUES (4);

INSERT INTO T2 (ID, T1_ID, SESSION_ID) VALUES (1, 1, 1);
INSERT INTO T2 (ID, T1_ID, SESSION_ID) VALUES (2, 4, 1);

Done. http://qc.codegear.com/wc/qcmain.aspx?d=49895

Thank you for both of your inputs.  As I have taught myself SQL I sometimes 
wonder if the code I write is good or not.

Cheers,

Nick Barrett

"Craig Stuntz [TeamB]" <craig_stuntz@nospam.please [a.k.a.
acm.org]> wrote 
in message news:46b09342$1@newsgroups.borland.com...
> Arno Brinkman wrote:
>
>> I think you should add an entry in QC with your reproducable script.
>
> I agree.
>
> -- 
> Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
>  Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
> Please read and follow Borland's rules for the user of their
>  server: http://support.borland.com/entry.jspa?externalID=293 

Nick,

Thank you very much for taking the time to report this issue in QC and 
for providing a concise test case demonstrating it.

The bug is now fixed, and we have released updated InterBase 2007 SP2 
binaries. Please download the fixes from 
http://www.codegear.com/downloads/regusers/interbase

You will notice that the newly updated SP2 version is now 8.1.0.257

Thanks once again.

Best wishes,
Sriram

Nick Barrett wrote:
> Done. http://qc.codegear.com/wc/qcmain.aspx?d=49895
> 
> Thank you for both of your inputs.  As I have taught myself SQL I 
> sometimes wonder if the code I write is good or not.
> 
> Cheers,
> 
> Nick Barrett
> 
> "Craig Stuntz [TeamB]" <craig_stuntz@nospam.please [a.k.a.
acm.org]> 
> wrote in message news:46b09342$1@newsgroups.borland.com...
>> Arno Brinkman wrote:
>>
>>> I think you should add an entry in QC with your reproducable
script.
>>
>> I agree.
>>
>> -- 
>> Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
>>  Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
>> Please read and follow Borland's rules for the user of their
>>  server: http://support.borland.com/entry.jspa?externalID=293