I found very strange problem using NHibernate ver. 2.1.2
I have the follow mapping between 3 objects - let say they are called Object1, Object2 and Object3: 1. in Object 1 there is a property of type Object2 <many-to-one name="Object2" column="DEFINITION_ID"/> 1. in Object 2 there is a collection property of type Object3 <bag name="Object3" table="MI_CLASS_DEFINITION_LINK" inverse="false" cascade="none" lazy="true"> <key column="DEFINITION_ID"/> <many-to-many column="CLASS_ID" class="SomeNameSpace.Object3, SomeNameSpace"/> </bag> So I'm trying to run two very similar NHibernate queries. The only difference between them is a left join and the result is very different: HQL Query1: Select count(e) from Object1 e where (exists (FROM e.Object2.Object3 AS p WHERE p.Name like 'xxx%' AND p.Object='yyy')) SQL Result1: select count(materiallo0_.ID) as col_0_0_ from OBJECT1 materiallo0_ where (exists (select materialcl3_.ID from OBJECT2 materialde1_, OBJECT2_OBJECT3_LINK materialcl2_, OBJECT3 materialcl3_ where materiallo0_.DEFINITION_ID=materialde1_.ID and materialde1_.ID=materialcl2_.DEFINITION_ID and materialcl2_.CLASS_ID=materialcl3_.ID and (materialcl3_.NAME like 'xxx%') and materialcl3_.OBJECT='yyy')) HQL Query2: Select count(e) from MaterialLot e left join e.Object2.Object3 m0 where (exists (FROM e.Object2.Object3 AS p WHERE p.Name like 'xxx%' AND p.Object='yyy')) AND m0.Object = 'yyy' SQL Result2: select count(materiallo0_.ID) as col_0_0_ from OBJECT1 materiallo0_ left outer join OBJECT2 materialde1_ on materiallo0_.DEFINITION_ID=materialde1_.ID left outer join OBJECT2_OBJECT3_LINK materialcl2_ on materialde1_.ID=materialcl2_.DEFINITION_ID left outer join OBJECT3 materialcl3_ on materialcl2_.CLASS_ID=materialcl3_.ID where (exists (select materialde1_.ID from OBJECT2 materialde1_ where (materialde1_.NAME like 'xxx%') and materialde1_.OBJECT='yyy')) and materialcl3_.OBJECT='yyy' The first SQL Result is ok. There's nothing strange. But the second one is confusing. You could check that in the "exist" section, NHibernate is selecting Object2 instead of linking and selecting Object3. Up to my logic, I was expecting Object3 and I have no idea how the left outer join changed the big picture. If anyone has an explanation, Please help. Thanks. -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/nhusers. For more options, visit https://groups.google.com/d/optout.
