NH-3813 - Eager fetch on key-many-to-one relation adds inner joins to the query

[nhibernate-bot]

Boris Drajer created an issue — 29th July 2015, 14:24:30:

If a class has a one-to-many reference to a second class that has key-many-to-one back reference to it, and I try to eager fetch this relation, inner joins are added to the query for all key-many-to-one references of the second class.

Mappings (shortened for brevity):

<class name="AttachmentType" table="AttachmentType" lazy="false">
    <id name="ID">
        <column name="ID" sql-type="Int32" not-null="true"/>
        <generator class="identity"/>
    </id>
    <bag name="RoleAttachmentTypes" lazy="true" inverse="true" cascade="all-delete-orphan">
        <key column="AttachmentTypeID"/>
        <one-to-many class="Role_AttachmentType"/>
    </bag>

    ... etc ...

</class>

<class name="Role*AttachmentType" table="Role*AttachmentType" lazy="false">
    <composite-id unsaved-value="any" >
        <key-many-to-one name="Role" column="RoleID" class="Role" />
        <key-many-to-one name="AttachmentType" column="AttachmentTypeID" class="AttachmentType"/>
    </composite-id>

    ... etc ...

 </class>

Now, a query of this type -

session.QueryOver<AttachmentType>()	
    .Fetch(at=>at.RoleAttachmentTypes).Eager
    .Left.JoinQueryOver(i => i.RoleAttachmentTypes)
    .TransformUsing(Transformers.DistinctRootEntity)
    .List();

• produces SQL like this:

SELECT <... cut for clarity ...>
FROM AttachmentType this_ 
left outer join Role_AttachmentType role_attac1_ on this_.ID=role_attac1_.AttachmentTypeID 
inner join Role role4_ on role_attac1_.RoleID=role4_.ID 
inner join AttachmentType attachment5* on role_attac1_.AttachmentTypeID=attachment5_.ID

Note the last two inner joins, IMO they shouldn't be there... I tried the same thing with NH 3.4.1 and there are no inner joins, so this seems to be new. If I change the second class not to have a composite key but an identity single key, the inner joins disappear.

---

Alexander Zaytsev added a comment — 29th July 2015, 21:46:02:

<~bdrajer> it would be nice, if you can bisect the exact commit which introduced this behavior

---

Boris Drajer added a comment — 30th July 2015, 17:38:25:

Sure, I'll do my best... Went back step by step as far as 4.0.0 Alpha 1, it still has this behaviour. Will be continuing my descent tomorrow :)

---

Boris Drajer added a comment — 3rd August 2015, 12:33:41:

Found it - if I'm not mistaken, the culprit is commit e531ad3:

#132

Once I add back the "&& !(type is EmbeddedComponentType)" condition that was removed, the inner join disappears (of course, the solution is probably not that simple).

I'm attaching a test case I used to track this down, add to the test project as/where/if appropriate... Note that the test is slower than necessary because the base clas creates the database schema even if no queries will actually be executed.

---

Alexander Zaytsev added a comment — 4th August 2015, 2:08:58:

Thanks for so much details, <~bdrajer>

Ok, this is not a bug, but a feature (bearing in mind that NH-2033 is specifically asked for these inner joins to be added). So, I lowered priority to trivial and changed type to improvement. The improvement will be to add these inner joins only when needed, and do not add them if not needed.

I would like to accept the pull request.

---

Boris Drajer added a comment — 4th August 2015, 8:45:32:

Alexander, thank you for your time.

I myself stumbled upon NH-2033 and had to employ workarounds several times, so that fix is more than welcome. I don't know enough on NHibernate inner logic and can't comment on how it is supposed to behave, but it should be noted that NH-2033 changed the way the query works in this case: the newly added inner join reduces the number of records returned and it doesn't initialize empty bags - both of which it did before. It seems to me that its intention was different than the one achieved:

First LEFT JOIN Association INNER JOIN First INNER JOIN Other

• is interpreted by SQL server as -

(First LEFT JOIN Association) INNER JOIN First INNER JOIN Other

• while the idea was to do this:

First LEFT JOIN (Association INNER JOIN First INNER JOIN Other)

If these parentheses could somehow be added to the join, that would seem to me the cleanest solution (no idea, though, if that is at all supported by NH... First of all, the joins become nested this way because the ON part now has to go after the last parenthesis).

Also note that this seems to happen only for non-lazy classes and probably properties (mapped with lazy=false), so a possible workaround for anyone having this problem (and the one I'm going to employ ;)) is to make them lazy.

---

Alexander Zaytsev added a comment — 7th August 2015, 4:39:59:

the newly added inner join reduces the number of records returned and it doesn't initialize empty bags - both of which it did before.

can you please add tests that demonstrate this behavior?

---

Boris Drajer added a comment — 7th August 2015, 10:23:30:

Got it: attachment file KeyManyToOneInnerJoinFetchFixture-v2.cs (if anyone can delete the other one, please do). Let me know if there's anything else I can do.