Right outer join becomes left outer join and retrieves wrong result

[GoogleCodeExporter]

What steps will reproduce the problem?

DROP TABLE IF EXISTS tableOne;
CREATE TABLE tableOne (
  colOne varchar(10) PRIMARY KEY
);

DROP TABLE IF EXISTS tableTwo;
CREATE TABLE tableTwo(
  colTwo varchar(10),
  FOREIGN KEY (colTwo) REFERENCES tableOne(colOne)
);

DROP TABLE IF EXISTS tableThree;
CREATE TABLE tableThree(
  colThree varchar(10)
);

INSERT INTO tableOne (colOne) VALUES ('first');
INSERT INTO tableTwo (colTwo) VALUES ('first');
INSERT INTO tableThree (colThree) VALUES ('first');
INSERT INTO tableThree (colThree) VALUES ('second');
COMMIT;

SELECT *
FROM tableOne
INNER JOIN tableTwo ON colTwo = tableOne.colOne
RIGHT OUTER JOIN tableThree ON colThree = tableOne.colOne;

What is the expected output? What do you see instead?

Expected: the SELECT statement selects all rows from tableOne ("first")
plus all rows from tableThree ("second"). Something like that:

COLTHREE COLONE COLTWO
-------- ------ ------
first    first  first
second   <null> <null>

Got: only the row from tableOne ("first") is selected:

COLTHREE COLONE COLTWO  
-------- ------ ------
first    first  first

What version of the product are you using? On what operating system, file
system, and virtual machine?

H2 1.1.118
Windows XP SP3
NTFS
Sun JRE 1.6.0-16

Do you know a workaround?

no.

How important/urgent is the problem for you?

Very Urgent.

In your view, is this a defect or a feature request?

A defect. MS SQL Server for instance selects two rows.

Please provide any additional information below.

EXPLAIN PLAN shows, that the optimizer turns the RIGHT OUTER JOIN into a
LEFT OUTER JOIN by changing the order:

SELECT TABLETHREE.COLTHREE, TABLEONE.COLONE, TABLETWO.COLTWO
FROM PUBLIC.TABLETHREE /* PUBLIC.TABLETHREE_TABLE_SCAN */
LEFT OUTER JOIN PUBLIC.TABLEONE /* PUBLIC.PRIMARY_KEY_F9: COLONE = COLTHREE
*/ ON COLTHREE = TABLEONE.COLONE
INNER JOIN PUBLIC.TABLETWO /* PUBLIC.CONSTRAINT_INDEX_F: COLTWO =
TABLEONE.COLONE */ ON ((COLTWO = TABLEONE.COLONE) AND (COLTHREE =
TABLEONE.COLONE)) AND (COLTWO = COLTHREE)

But IMHO it misses the fact that tableThree is unrelated to tableTwo so
that the last join criterium ("ON ((COLTWO = TABLEONE.COLONE) AND (COLTHREE
= TABLEONE.COLONE)) AND (COLTWO = COLTHREE)") is wrong.

Original issue reported on code.google.com by dmoeb...@gmx.net on 3 Dec 2009 at 9:54

[GoogleCodeExporter]

Note: the bug still exists in 1.2.124.

Original comment by dmoeb...@gmx.net on 3 Dec 2009 at 10:05

[GoogleCodeExporter]

I can change the join algorithm so that your test case works. However,
it will still not be fully correct. I do already know about the problem:

http://www.h2database.com/html/faq.html#known_bugs
"Some problems have been found with right outer join. Internally, it is 
converted to
left outer join, which does not always produce the same results as other 
databases
when used in combination with other joins."

The test case that will still be incorrect is:

create table a(a int);
insert into a values(1);
insert into a values(2);
create table b(b int);
insert into b values(1);
create table c(c int);
insert into c values(1);
insert into c values(2);
insert into c values(3);
select * from a inner join b on a=b right outer join c on c=a;
select * from c left outer join (a inner join b on b=a) on c=a;
select * from c left outer join a on c=a inner join b on b=a;
drop table a;
drop table b;
drop table c;

I hope I can fix that soon, but it will require more changes.

Original comment by thomas.t...@gmail.com on 4 Dec 2009 at 11:09

• Changed state: Accepted

[GoogleCodeExporter]

Your test case now works in version 1.2.125, however this issue is not 
completely
solved. I will leave the issue open.

Original comment by thomas.t...@gmail.com on 6 Dec 2009 at 6:50

[GoogleCodeExporter]

Original comment by thomas.t...@gmail.com on 21 Mar 2010 at 11:42

[GoogleCodeExporter]

This is implemented in version 1.2.138, but not enabled by default (because 
it's a bigger change). To test it, enable the system property h2.nestedJoins. 
This property will be enabled by default in version 1.3.x

Original comment by thomas.t...@gmail.com on 28 Jun 2010 at 4:53

• Changed state: Started

[GoogleCodeExporter]

I'm setting the issue to 'fixed', even if nested joins are not yet enabled by 
default. Please add a comment if it still doesn't work for you (or doesn't work 
as expected) even when the system property is enabled.

Original comment by thomas.t...@gmail.com on 28 Jun 2010 at 6:51

• Changed state: Fixed