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 On Sun, Oct 26, 2008 at 6:09 PM, Thomas Mueller <[EMAIL PROTECTED]> wrote: > 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 > > > > On Fri, Oct 24, 2008 at 7:25 AM, Wes Clark <[EMAIL PROTECTED]> wrote: >> >> 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; >> >> >> > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
