We have our own Hibernate-like object-relational query generator, so the queries can get complex. We don't use views.
On Mon, Oct 27, 2008 at 11:23 AM, Thomas Mueller < [EMAIL PROTECTED]> wrote: > > 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 -~----------~----~----~----~------~----~------~--~---
