Left outer join returns incorrect result set


#1

select tjoin2.rnum, tjoin2.rnum as rnumt2, tjoin3.rnum as rnumt3 from tjoin2 left outer join tjoin3 on tjoin2.c2 <> tjoin3.c2 and tjoin2.c1=tjoin3.c1

rnumt3 column should be 0,0,1, null and not null, null, null, 0

create table TJOIN2 ( RNUM integer not null , C1 integer, C2 varchar(2));
insert into TJOIN2 ( RNUM, C1, C2) values ( 0, 10, ‘BB’);
insert into TJOIN2 ( RNUM, C1, C2) values ( 1, 15, ‘DD’);
insert into TJOIN2 ( RNUM, C1, C2) values ( 2, NULL, ‘EE’);
insert into TJOIN2 ( RNUM, C1, C2) values ( 3, 10, ‘FF’);

create table TJOIN3 ( RNUM integer not null , C1 integer, C2 varchar(2));
insert into TJOIN3 ( RNUM, C1, C2) values ( 0, 10, ‘XX’);
insert into TJOIN3 ( RNUM, C1, C2) values ( 1, 15, ‘YY’);

image


#2

Hi @mainstreet439,

Thanks for reporting this. There are some known issues in our outer join implementation that we already have a fix for (likely to go out in a release in the next month or so). I’ll check with the product team and see if if the patch fixes your particular issue.


#3

Hi @mainstreet439 it was actually something simpler and was fixed in master yesterday. It should be in a release in the coming weeks.


#4

The fix is now in the 3.4 release as well.