Watch, Follow, &
Connect with Us

QualityCentral

Public Report
Report From: InterBase/Server    [ Add a report in this area ]  
Report #:  106713   Status: Open
left join does not show all records
Project:  InterBase Build #:  10.0.4.590
Version:    10.0 Submitted By:   Marc Huber
Report Type:  Crash / Data loss / Total failure Date Reported:  6/27/2012 7:01:10 AM
Severity:    Serious / Highly visible problem Last Updated: 9/28/2012 9:04:38 AM
Platform:    64 Bit Internal Tracking #:  
Resolution: None (Resolution Comments) Resolved in Build: : None
Duplicate of:  None
Voting and Rating
Overall Rating: No Ratings Yet
0.00 out of 5
Total Votes: 5
Description
A left join combined with an inner join does in some cases not show all records.

The bug can also be reproduced with former versions (for example tested with IB2007).

Steps to Reproduce:
CREATE TABLE "TABLE1"
(
"PK" INTEGER,
"TABLE_FK" VARCHAR(8),
"FK" INTEGER,
"FK_TABLE2" INTEGER
);

CREATE TABLE "TABLE2"
(
"PK" INTEGER
);

CREATE TABLE "TABLE3"
(
"PK" INTEGER
);

insert into table1 values (1, 'table3', 100, 10);
insert into table1 values (2, 'table99', 9999, 10);
insert into table2 values (10);
insert into table3 values (100);

The following statements show all records (2) as expected:

select *
from table1
left outer join table3 on table1.table_FK = 'table3' and table1.fk = table3.pk

select *
from table1
left outer join table3 on table1.table_FK = 'table3' and table1.fk = table3.pk
join table2 on table1.fk_table2 = table2.pk

But if I change the position of the inner join clause (like in the following statement), I get only one record.

select *
from table1
join table2 on table1.fk_table2 = table2.pk
left outer join table3 on table1.table_FK = 'table3' and table1.fk = table3.pk
Workarounds
None
Attachment
None
Comments

None

Server Response from: ETNACODE01

Copyright© 1994 - 2013 Embarcadero Technologies, Inc. All rights reserved.