QualityCentral

Public Report

Report From: InterBase/Server/Internal [ Add a report in this area ]

Report #: 43874

Status: Closed

Bad query result on LEFT JOIN when multiple filters in the join

Project: InterBase

Build #: 7.5.1.162

Version: 8.0

Submitted By: Stephane DORIGO

Report Type: Basic functionality failure

Date Reported: 4/5/2007 5:10:16 AM

Severity: Serious / Highly visible problem

Last Updated: 4/24/2008 2:06:00 PM

Platform: 95, 98, 2000, NT, XP

Internal Tracking #: 251610

Resolution: Fixed (Resolution Comments)

Resolved in Build: : 9.0

Duplicate of: None

Voting and Rating

Overall Rating: No Ratings Yet

0.00 out of 5

Total Votes: 4

Description

IB (7.5.1 or 2007) returns a bad result in some queries whose have multiple filters in LEFT JOIN.

Steps to Reproduce:

Execute this script to create the example DB:

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

==> Bug.

Workarounds

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

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

---------

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

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

Attachment

None

Comments

None

Quality Central

Developer Tools

Database Tools