For what it's worth, this error does not occur on version 1.0.62, but does occur on 1.0.75.
On Mon, Oct 27, 2008 at 1:25 PM, Weston Clark <[EMAIL PROTECTED]> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
