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 -~----------~----~----~----~------~----~------~--~---
