Re: Bug in IB 7.5.1 on multiple filters in LEFT JOIN

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


-- 

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 

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 ??

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

-- 

"Bill Todd" <no@no.com> a écrit dans le message de
news:461261c3@newsgroups.borland.com...
> 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
>

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