IB 2007 SP2 left outer join bug

[Nick Barrett]

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);

[@remove_meabvisie.nl Arno Brinkman]

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...@yahoogroups.com

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

[Craig Stuntz [TeamB]]

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 Barrett]

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_...@nospam.please [a.k.a. acm.org]> wrote
in message news:46b09342$1...@newsgroups.borland.com...

[Sriram Balasubramanian]

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