Bug in IB 7.5.1 on multiple filters in LEFT JOIN

[Stéphane DORIGO]

Hello,

I used IB 7.5.1.162 on Windows 2003 server SP1.
The version of the client library (gds32.dll) is ok with the version of the
server.

So, I think there is a bug in IB when doing multiple filters in LEFT JOIN.
You can reproduce it with this simple script:

SET NAMES ISO8859_1;
SET SQL DIALECT 3;
CREATE DATABASE "ServATYS:E:\Temp\DBClients\IBBug.GDB" PAGE_SIZE 1024
DEFAULT CHARACTER SET ISO8859_1;
COMMIT;

CREATE TABLE T1
(
Id INTEGER NOT NULL,
Id_T2 INTEGER NOT NULL,
Id_T3 INTEGER NOT NULL,
CONSTRAINT P_T1_Id PRIMARY KEY (Id)
);
COMMIT;

CREATE TABLE T2
(
Id INTEGER NOT NULL,
CONSTRAINT P_T2_Id PRIMARY KEY (Id)
);
COMMIT;

CREATE TABLE T3
(
Id INTEGER NOT NULL,
CONSTRAINT P_T3_Id PRIMARY KEY (Id)
);
COMMIT;

INSERT INTO T2 (Id) VALUES (1);
INSERT INTO T2 (Id) VALUES (2);
INSERT INTO T2 (Id) VALUES (3);

INSERT INTO T3 (Id) VALUES (1);
INSERT INTO T3 (Id) VALUES (2);
INSERT INTO T3 (Id) VALUES (3);

INSERT INTO T1 (Id,Id_T2,Id_T3) VALUES (1,1,1);
INSERT INTO T1 (Id,Id_T2,Id_T3) VALUES (2,1,2);
INSERT INTO T1 (Id,Id_T2,Id_T3) VALUES (3,3,2);

COMMIT;

Now, in IBConsole, try this 3 queries:

1) SELECT
T1.Id, T2.Id, T3.Id
FROM
T1
LEFT JOIN T2 ON T2.Id = T1.Id_T2
LEFT JOIN T3 ON T3.Id = T1.Id_T3
WHERE
T1.Id = 1

The result is:
1 1 1
It's Ok.

2) SELECT
T1.Id, T2.Id, T3.Id
FROM
T1
LEFT JOIN T2 ON T2.Id = T1.Id_T2
LEFT JOIN T3 ON T3.Id = T1.Id_T3 AND T3.Id <> 1
WHERE
T1.Id = 1

The result is:
1 1 <Null>
It's Ok.

3) SELECT
T1.Id, T2.Id, T3.Id
FROM
T1
LEFT JOIN T2 ON T2.Id = T1.Id_T2
LEFT JOIN T3 ON T3.Id = T1.Id_T3 AND T3.Id > 1
WHERE
T1.Id = 1

The result is:
NOTHING !!

It would be :
1 1 <Null>
Like the second query...

Big bug ??

[Bill Todd]

Stiphane DORIGO wrote:

> SELECT
> T1.Id, T2.Id, T3.Id
> FROM
> T1
> LEFT JOIN T2 ON T2.Id = T1.Id_T2
> LEFT JOIN T3 ON T3.Id = T1.Id_T3 AND T3.Id > 1
> WHERE
> T1.Id = 1

What do you get if you change the query to:

SELECT
T1.Id, T2.Id, T3.Id
FROM
T1
LEFT JOIN T2 ON T2.Id = T1.Id_T2
LEFT JOIN T3 ON T3.Id = T1.Id_T3
WHERE

T1.Id = 1 AND T3.ID > 1

--
Bill Todd (TeamB)

[Wayne Niddery [TeamB]]

Stéphane DORIGO wrote:
>
> I used IB 7.5.1.162 on Windows 2003 server SP1.
>

> So, I think there is a bug in IB when doing multiple filters in LEFT
> JOIN. You can reproduce it with this simple script:
>

> 3) SELECT
> T1.Id, T2.Id, T3.Id
> FROM
> T1
> LEFT JOIN T2 ON T2.Id = T1.Id_T2
> LEFT JOIN T3 ON T3.Id = T1.Id_T3 AND T3.Id > 1
> WHERE
> T1.Id = 1
>
> The result is:
> NOTHING !!
>
> It would be :
> 1 1 <Null>
> Like the second query...

I agree this is a bug, and I can reproduce it in IB2007. Because it is a
LEFT join, it most definitely should still return the row, but with null for
the T3 field. The same problems result testing for T3.Id = 2 or < 1, etc and
with or without your Where clause.

Can you please enter this, with your sample tables/ queries, into QC?

--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"It is error alone which needs the support of government. Truth can
stand by itself." - Thomas Jefferson

[Stéphane DORIGO]

"Bill Todd" <n...@no.com> a écrit dans le message de
news:4612...@newsgroups.borland.com...

this query works fine but it's not the same result... ;-)

[Stéphane DORIGO]

> I agree this is a bug, and I can reproduce it in IB2007. Because it is a
> LEFT join, it most definitely should still return the row, but with null
for
> the T3 field. The same problems result testing for T3.Id = 2 or < 1, etc
and
> with or without your Where clause.
>
> Can you please enter this, with your sample tables/ queries, into QC?
>

It's Ok.

[Bill Todd]

As Wayne said, you need to enter a bug report in Quality Central so
this can get fixed.

--
Bill Todd (TeamB)