Unsupported outer join condition - simplified duplication path

[GoogleCodeExporter]

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
create table IF NOT EXISTS TABLE1 (PRIMARYKEY INTEGER, FOREIGNKEY INTEGER);
create table IF NOT EXISTS TABLE2 (PRIMARYKEY INTEGER, FOREIGNKEY INTEGER);
create table IF NOT EXISTS TABLE3 (PRIMARYKEY INTEGER, FOREIGNKEY INTEGER);
create table IF NOT EXISTS TABLE4 (PRIMARYKEY INTEGER, FOREIGNKEY INTEGER);
insert into TABLE1 values (1,1);
insert into TABLE1 values (2,2);
insert into TABLE1 values (3,3);
insert into TABLE2 values (1,1);
insert into TABLE2 values (2,2);
insert into TABLE3 values (1,1);
insert into TABLE3 values (3,3);
insert into TABLE4 values (1,1);
insert into TABLE4 values (2,2);
insert into TABLE4 values (3,3);
insert into TABLE4 values (4,4);

SELECT DISTINCT 
 TABLE1.PRIMARYKEY AS COL0
 ,TABLE2.PRIMARYKEY AS COL1
 ,TABLE3.PRIMARYKEY AS COL2
FROM
  TABLE1 
   RIGHT OUTER JOIN TABLE3 ON ( TABLE1.FOREIGNKEY = TABLE3.PRIMARYKEY )
   RIGHT OUTER JOIN TABLE2 ON ( TABLE2.FOREIGNKEY = TABLE1.PRIMARYKEY )

What is the expected output? 
(On MySQL, MSSQL 2000, Postgresql):

C0L0   C0L1   C0L2
|1     |1     |1     |
|null  |2     |null  |

What do you see instead?
[Error Code: 90136, SQL State: 90136]  Unsupported outer join condition:
"(TABLE2.FOREIGNKEY = TABLE1.PRIMARYKEY)";

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

H2 version: h2-1.2.131.jar
OS: Windows 7 64bit
File System: NTFS
VM:
java version "1.6.0_13"
Java(TM) SE Runtime Environment (build 1.6.0_13-b03)
Java HotSpot(TM) Client VM (build 11.3-b02, mixed mode, sharing)

Do you know a workaround?
No. Nested joins also fail

How important/urgent is the problem for you?
Urgent as it prevents us from using H2 for our test cases and for our
metadata layer.

In your view, is this a defect or a feature request?
Since other databases produce the correct result and H2 is unable to
process the query, I believe this is a defect.

Please provide any additional information below.
I have a series of test cases around the four tables that are listed in
that script above regarding outer joins. 

Note that an earlier version of H2 (built 8/2/2007) was able to execute the
query, but returned invalid results (only 1 row and not two). Also
interesting to note that HSQLDB is able to process the query, but produces
invalid results:
C0L0   C0L1   C0L2
|1     |1     |1     |

Finally interesting to note - changing the "RIGHT OUTER JOIN" to "LEFT
OUTER JOIN" allows the query to run, but of course produces incorrect data.

Here is the nested-joins version of the SQL that also fails:
SELECT DISTINCT 
 TABLE1.PRIMARYKEY AS COL0
 ,TABLE2.PRIMARYKEY AS COL1
 ,TABLE3.PRIMARYKEY AS COL2
FROM TABLE2 LEFT OUTER JOIN 
     ( 
      TABLE1 RIGHT OUTER JOIN TABLE3 
      ON ( TABLE1.FOREIGNKEY = TABLE3.PRIMARYKEY )
     ) 
     ON ( TABLE2.FOREIGNKEY = TABLE1.PRIMARYKEY )

Original issue reported on code.google.com by mbOrches...@gmail.com on 11 Mar 2010 at 7:25

[GoogleCodeExporter]

It's a known problem, it is related to issue 145. It is also documented here:
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."

This will be a bigger change. I hope I soon have time to implement the required 
changes.

Original comment by thomas.t...@gmail.com on 20 Mar 2010 at 8:17

• Changed state: Accepted

[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:52

• Changed state: Fixed

[GoogleCodeExporter]

Thank-you so much. I'll re-test.

Original comment by mbOrches...@gmail.com on 28 Jul 2010 at 3:07

[GoogleCodeExporter]

I had this issue in 1.2. I downloaded and tested 1.3, and it worked. Thanks for 
fixing!

Original comment by jacobgod...@gmail.com on 27 May 2011 at 1:57