Unsupported outer join condition

[Wes Clark]

In one of our development branches, I moved in a newer version of H2,
and one of the JUnit tests started spitting out this error. I don't
think I've ever seen this error running version 1.0.62, but on any
later version that I've tried, included 1.0.79 which is marked as
stable, the same SQL gets the error about one of the join conditions
being illegal. The stack trace is

Error: Unsupported outer join condition: ((QROOT.ID =
CC_RELATEDACTIVITY_2.OWNERID) AND (QROOT.CLAIMID =
CC_ACTIVITY_3.CLAIMID)); SQL statement:
[SQL statement goes here] then a stack trace:
at org.h2.message.Message.getSQLException(Message.java:103)
at org.h2.message.Message.getSQLException(Message.java:114)
at org.h2.message.Message.getSQLException(Message.java:77)
at org.h2.command.dml.Select.preparePlan(Select.java:815)
at org.h2.command.dml.Select.prepare(Select.java:729)
at org.h2.command.Parser.prepareCommand(Parser.java:233)
at org.h2.engine.Session.prepareLocal(Session.java:285)
at org.h2.engine.Session.prepareCommand(Session.java:246)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:
1035)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:151)
at org.h2.server.web.WebThread.getResult(WebThread.java:1714)
at org.h2.server.web.WebThread.query(WebThread.java:1277)
at org.h2.server.web.WebThread.process(WebThread.java:444)
at org.h2.server.web.WebThread.processRequest(WebThread.java:186)
at org.h2.server.web.WebThread.process(WebThread.java:241)
at org.h2.server.web.WebThread.run(WebThread.java:196)

The query I'll attach as a file.

[Wes Clark]

File with query posted http://groups.google.com/group/h2-database/web/h2joinerror.txt.

[Wes Clark]

Database on which to run query: http://groups.google.com/group/h2-database/web/cccDatabase.zip

[Wes Clark]

Connect with jdbc:h2:file:/temp/h2problem/h2.1.0.79.ccc/ccc, or
wherever you put the files, then run
SELECT qRoot.ID col0 ,
qRoot.AssignmentStatus col1 ,
cc_claimcontact_1.ContactID col2 ,
cc_contact_5.LastName col3 ,
cc_contact_5.FirstName col4 ,
cc_contact_5.Suffix col5 ,
qRoot.AssignmentDate col6 ,
qRoot.AssignedUserID col7 ,
cc_contact_7.LastName col8 ,
cc_contact_7.FirstName col9 ,
cc_contact_7.Suffix col10 ,
cc_contact_7.Name col11 ,
cc_contact_7.Subtype col12 ,
qRoot.Escalated col13 ,
qRoot.Status col14 ,
cc_contact_8.LastName col15 ,
cc_contact_8.FirstName col16 ,
cc_contact_8.Suffix col17 ,
cc_contact_8.Name col18 ,
cc_contact_8.Subtype col19 ,
cc_contact_11.LastName col20 ,
cc_contact_11.FirstName col21 ,
cc_contact_11.Suffix col22 ,
qRoot.ExposureID col23 ,
cc_contact_13.LastName col24 ,
cc_contact_13.FirstName col25 ,
cc_contact_13.Suffix col26 ,
qRoot.AssignedQueueID col27 ,
qRoot.Mandatory col28 ,
qRoot.LastViewedDate col29 ,
qRoot.TargetDate col30 ,
cc_contact_15.LastName col31 ,
cc_contact_15.FirstName col32 ,
cc_contact_15.Suffix col33 ,
cc_contact_15.Name col34 ,
cc_contact_15.Subtype col35 ,
qRoot.ClaimID col36 ,
qRoot.Type col37 ,
cc_contact_16.LastName col38 ,
cc_contact_16.FirstName col39 ,
cc_contact_16.Suffix col40 ,
cc_contact_16.Name col41 ,
cc_contact_16.Subtype col42 ,
cc_claim_6.JurisdictionState col43,
qRoot.UpdateUserID col44 ,
cc_claim_6.InsuredDenormID col45 ,
cc_contact_16.Subtype col46 ,
qRoot.Subject col47 ,
cc_claim_6.LOBCode col48 ,
cc_contact_18.LastName col49 ,
cc_contact_18.FirstName col50 ,
cc_contact_18.Suffix col51 ,
qRoot.CloseDate col52 ,
qRoot.Priority col53 ,
cc_contact_20.LastName col54 ,
cc_contact_20.FirstName col55 ,
cc_contact_20.Suffix col56 ,
qRoot.CreateUserID col57 ,
cc_user_10.ContactID col58 ,
cc_user_17.ContactID col59 ,
qRoot.UpdateTime col60 ,
cc_exposure_14.ClaimOrder col61 ,
cc_exposure_14.ExposureType col62 ,
cc_exposure_14.LossParty col63 ,
cc_claim_21.LossType col64 ,
qRoot.ExternallyOwned col65 ,
cc_user_4.ContactID col66 ,
cc_user_12.ContactID col67 ,
cc_claim_6.ClaimNumber col68
FROM cc_activity qRoot
LEFT OUTER JOIN (cc_claimcontact cc_claimcontact_1
INNER JOIN (cc_contact cc_contact_8)
ON
cc_contact_8.ID=cc_claimcontact_1.ContactID )
ON cc_claimcontact_1.ID =qRoot.ClaimContactID
AND qRoot.ClaimID = cc_claimcontact_1.ClaimID
LEFT OUTER JOIN (cc_relatedactivity cc_relatedactivity_2
INNER JOIN (cc_activity cc_activity_3
LEFT OUTER JOIN (cc_user cc_user_4
INNER JOIN (cc_contact
cc_contact_5)
ON cc_contact_5.ID
=cc_user_4.ContactID
AND cc_contact_5.Subtype =
13 )
ON cc_user_4.ID
=cc_activity_3.AssignedUserID )
ON cc_activity_3.ID
=cc_relatedactivity_2.ForeignEntityID )
ON qRoot.ID
=cc_relatedactivity_2.OwnerID
AND qRoot.ClaimID =
cc_activity_3.ClaimID
INNER JOIN (cc_claim cc_claim_6
LEFT OUTER JOIN (cc_contact cc_contact_7)
ON cc_contact_7.ID=cc_claim_6.ClaimantDenormID
LEFT OUTER JOIN (cc_user cc_user_12
INNER JOIN (cc_contact cc_contact_13)
ON cc_contact_13.ID
=cc_user_12.ContactID
AND cc_contact_13.Subtype = 13 )
ON cc_user_12.ID
=cc_claim_6.AssignedUserID
LEFT OUTER JOIN (cc_contact cc_contact_16)
ON
cc_contact_16.ID=cc_claim_6.InsuredDenormID )
ON cc_claim_6.ID =qRoot.ClaimID
LEFT OUTER JOIN (cc_matter cc_matter_9
LEFT OUTER JOIN (cc_user cc_user_10
INNER JOIN (cc_contact cc_contact_11)
ON cc_contact_11.ID
=cc_user_10.ContactID
AND cc_contact_11.Subtype = 13 )
ON cc_user_10.ID
=cc_matter_9.AssignedUserID )
ON cc_matter_9.ID
=qRoot.MatterID
AND qRoot.ClaimID =
cc_matter_9.ClaimID
LEFT OUTER JOIN (cc_exposure cc_exposure_14
LEFT OUTER JOIN (cc_contact cc_contact_15)
ON
cc_contact_15.ID=cc_exposure_14.ClaimantDenormID
LEFT OUTER JOIN (cc_user cc_user_17
INNER JOIN (cc_contact cc_contact_18)
ON cc_contact_18.ID
=cc_user_17.ContactID
AND cc_contact_18.Subtype = 13 )
ON cc_user_17.ID
=cc_exposure_14.AssignedUserID
INNER JOIN (cc_claim cc_claim_21)
ON cc_claim_21.ID=cc_exposure_14.ClaimID )
ON cc_exposure_14.ID =qRoot.ExposureID
AND qRoot.ClaimID = cc_exposure_14.ClaimID
LEFT OUTER JOIN (cc_user cc_user_19
INNER JOIN (cc_contact cc_contact_20)
ON cc_contact_20.ID =cc_user_19.ContactID
AND cc_contact_20.Subtype = 13 )
ON cc_user_19.ID =qRoot.AssignedUserID
WHERE qRoot.Type = 0
AND qRoot.Status = 0
AND qRoot.Retired = 0
AND
(
(
qRoot.AssignmentStatus = 1
AND qRoot.AssignedUserID = 1
)
)
ORDER BY col30 ASC,
col68 ASC,
col47 ASC,
col0 ASC;

[Thomas Mueller]

Hi,

I think I understand the problem now a bit better. I have a simple
test case that also fails on H2, but works on PostgreSQL, MySQL and
Apache Derby:

create table a(id int);
create table b(id int);
create table c(id int);
select * from a left outer join (b left outer join c on b.id = c.id)
on c.id = a.id;
drop table a;
drop table b;
drop table c;

It looks like H2 is confused because the definition of c is nested.

I don't know how to solve it yet however.

Regards,
Thomas

[Wes Clark]

It works fine on SQL Server 2005/2008 and Oracle 10.2.0.1.

On Oct 26, 10:09 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
> ... but works on PostgreSQL, MySQL and Apache Derby:

[Thomas Mueller]

Hi,

I found out what the problem is. Unfortunately it is not so simple to
solve, the nested outer joins need to be converted to subqueries. I
will add a feature request with high priority, and hopefully can soon
(but I'm not sure when) implement the missing functionality.

I'm wondering why the query is so complicated. Would it make sense to
use views? Is this a generated query, if yes how was is generated?

Regards,
Thomas

[Weston Clark]

We have our own Hibernate-like object-relational query generator, so the queries can get complex.  We don't use views.

[Weston Clark]

For what it's worth, this error does not occur on version 1.0.62, but does occur on 1.0.75.

[Thomas Mueller]

Hi,

> For what it's worth, this error does not occur on version 1.0.62, but does
> occur on 1.0.75.

I'm not sure if the result and the performance of version 1.0.62 is
correct in every case. As I wrote, it is a bug, and I will try to fix
it, but it is complicated to implement correctly.

Regards,
Thomas

[Wes Clark]

Please keep us posted on your progress. We want to take the newer
version for the Oracle-compatible handling of NULLs in an index.

On Oct 30, 10:44 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
>

[Wes Clark]

Since this error was showing up in a very limited number of cases, we
dug into our query generator and found this type outer join condition
referencing a table within an enclosed inner join, was being added to
the ON clause for perhaps the no-longer valid reason of helping Oracle
to choose an optimal query plan. It was so long ago, and the fact we
will be using Oracle 11 with this version of our product, that we
decided to remove the logic that was adding this restriction, and wait
and see what happens when we do performance testing on Oracle, which
is several months away. The queries compile fine on H2.

In short, our immediate need for a fix has gone away. We hope we
don't need to put this logic back in. If you come up with a fix for
H2 in the meantime, then this potential switch back will be painless.
If we never need to put it back in, and you never fix it, then we will
probably never care.