Fetch Join generates incorrect SQL joins for the same entity type

[PiotrKlecha]

Using a Fetch Join mapping for an entity that contains references to entities of the same type results in incorrect joins in SQL query.

For an entity:

public class Entity
{
	public virtual ulong? EntityId { get; set; }
	public virtual string EntityNumber { get; set; }
	public virtual Entity ReferencedEntity { get; set; }
	public virtual Entity AdditionalEntity { get; set; }
	public virtual Entity SourceEntity { get; set; }
}

and mapping:

mapper.Class<Entity>(m =>
{
	m.Id(c => c.EntityId, id =>
	{
		id.Generator(Generators.Native);
	});
	m.Property(c => c.EntityNumber);
	m.ManyToOne(c => c.ReferencedEntity, p =>
	{
		p.Column("ReferencedEntityId");
		p.Fetch(FetchKind.Join);
	});
	m.ManyToOne(c => c.AdditionalEntity, p =>
	{
		p.Column("AdditionalEntityId");
		p.Fetch(FetchKind.Join);
	});
	m.ManyToOne(c => c.SourceEntity, p =>
	{
		p.Column("SourceEntityId");
		p.Fetch(FetchKind.Join);
	});
});

running the following query:

session
	.QueryOver<Entity>()
	.Where(e => e.EntityId == 1)
	.List();

results in an incorrect SQL:

    SELECT
        this_.EntityId as entityid1_0_3_,
        this_.EntityNumber as entitynumber2_0_3_,
        this_.ReferencedEntityId as referencedentityid3_0_3_,
        this_.AdditionalEntityId as additionalentityid4_0_3_,
        this_.SourceEntityId as sourceentityid5_0_3_,
        entity2_.EntityId as entityid1_0_0_,
        entity2_.EntityNumber as entitynumber2_0_0_,
        entity2_.ReferencedEntityId as referencedentityid3_0_0_,
        entity2_.AdditionalEntityId as additionalentityid4_0_0_,
        entity2_.SourceEntityId as sourceentityid5_0_0_,
        entity3_.EntityId as entityid1_0_1_,
        entity3_.EntityNumber as entitynumber2_0_1_,
        entity3_.ReferencedEntityId as referencedentityid3_0_1_,
        entity3_.AdditionalEntityId as additionalentityid4_0_1_,
        entity3_.SourceEntityId as sourceentityid5_0_1_,
        entity4_.EntityId as entityid1_0_2_,
        entity4_.EntityNumber as entitynumber2_0_2_,
        entity4_.ReferencedEntityId as referencedentityid3_0_2_,
        entity4_.AdditionalEntityId as additionalentityid4_0_2_,
        entity4_.SourceEntityId as sourceentityid5_0_2_ 
    FROM
        Entity this_ 
    left outer join
        Entity entity2_ 
            on this_.ReferencedEntityId=entity2_.EntityId 
    left outer join
        Entity entity3_ 
            on entity2_.AdditionalEntityId=entity3_.EntityId 
    left outer join
        Entity entity4_ 
            on entity3_.SourceEntityId=entity4_.EntityId 
    WHERE
        this_.EntityId = :p0;

entity3_ is joined to entity2_ and entity4_ is joined to entity3_ while both should be joined to this_ like entity2_.

Explicitly fetching referenced entities:

session
	.QueryOver<Entity>()
	.Where(e => e.EntityId == 1)
	.Fetch(SelectMode.Fetch,
		e => e.ReferencedEntity,
		e => e.AdditionalEntity,
		e => e.SourceEntity)
	.List();

adds correct joins to SQL but previous still remain (entity3_ and entity4_):

    SELECT
        this_.EntityId as entityid1_0_5_,
        this_.EntityNumber as entitynumber2_0_5_,
        this_.ReferencedEntityId as referencedentityid3_0_5_,
        this_.AdditionalEntityId as additionalentityid4_0_5_,
        this_.SourceEntityId as sourceentityid5_0_5_,
        entity2_.EntityId as entityid1_0_0_,
        entity2_.EntityNumber as entitynumber2_0_0_,
        entity2_.ReferencedEntityId as referencedentityid3_0_0_,
        entity2_.AdditionalEntityId as additionalentityid4_0_0_,
        entity2_.SourceEntityId as sourceentityid5_0_0_,
        entity3_.EntityId as entityid1_0_1_,
        entity3_.EntityNumber as entitynumber2_0_1_,
        entity3_.ReferencedEntityId as referencedentityid3_0_1_,
        entity3_.AdditionalEntityId as additionalentityid4_0_1_,
        entity3_.SourceEntityId as sourceentityid5_0_1_,
        entity4_.EntityId as entityid1_0_2_,
        entity4_.EntityNumber as entitynumber2_0_2_,
        entity4_.ReferencedEntityId as referencedentityid3_0_2_,
        entity4_.AdditionalEntityId as additionalentityid4_0_2_,
        entity4_.SourceEntityId as sourceentityid5_0_2_,
        entity5_.EntityId as entityid1_0_3_,
        entity5_.EntityNumber as entitynumber2_0_3_,
        entity5_.ReferencedEntityId as referencedentityid3_0_3_,
        entity5_.AdditionalEntityId as additionalentityid4_0_3_,
        entity5_.SourceEntityId as sourceentityid5_0_3_,
        entity6_.EntityId as entityid1_0_4_,
        entity6_.EntityNumber as entitynumber2_0_4_,
        entity6_.ReferencedEntityId as referencedentityid3_0_4_,
        entity6_.AdditionalEntityId as additionalentityid4_0_4_,
        entity6_.SourceEntityId as sourceentityid5_0_4_ 
    FROM
        Entity this_ 
    left outer join
        Entity entity2_ 
            on this_.ReferencedEntityId=entity2_.EntityId 
    left outer join
        Entity entity3_ 
            on entity2_.AdditionalEntityId=entity3_.EntityId 
    left outer join
        Entity entity4_ 
            on entity3_.SourceEntityId=entity4_.EntityId 
    left outer join
        Entity entity5_ 
            on this_.AdditionalEntityId=entity5_.EntityId 
    left outer join
        Entity entity6_ 
            on this_.SourceEntityId=entity6_.EntityId 
    WHERE
        this_.EntityId = :p0;

[bahusoid]

Hm.. So basically if I understand correctly it loads tree that can be expressed with explicit fetches as:

session
	.QueryOver<Entity>()
	.Where(e => e.EntityId == 1)
	.Fetch(SelectMode.Fetch,
		e => e.ReferencedEntity,
		e => e.ReferencedEntity.AdditionalEntity,
		e => e.ReferencedEntity.AdditionalEntity.SourceEntity)

[bahusoid]

Will those joins go away with explicit skip? I mean:

session
	.QueryOver<Entity>()
	.Where(e => e.EntityId == 1)
	//.Fetch(SelectMode.Fetch,...) // Your fetches here
	.Fetch(SelectMode.Skip, 
		e => e.ReferencedEntity.AdditionalEntity,
		e => e.ReferencedEntity.AdditionalEntity.SourceEntity)

[PiotrKlecha]

Hm.. So basically if I understand correctly it loads tree that can be expressed with explicit fetches as:

session
	.QueryOver<Entity>()
	.Where(e => e.EntityId == 1)
	.Fetch(SelectMode.Fetch,
		e => e.ReferencedEntity,
		e => e.ReferencedEntity.AdditionalEntity,
		e => e.ReferencedEntity.AdditionalEntity.SourceEntity)

Yes, that's exactly what happens.

Will those joins go away with explicit skip? I mean:

session
	.QueryOver<Entity>()
	.Where(e => e.EntityId == 1)
	//.Fetch(SelectMode.Fetch,...) // Your fetches here
	.Fetch(SelectMode.Skip, 
		e => e.ReferencedEntity.AdditionalEntity,
		e => e.ReferencedEntity.AdditionalEntity.SourceEntity)

No, with explicit skip like this (nested properties) the resulting SQL is the same as before. However, if the first property is skipped:

session
	.QueryOver<Entity>()
	.Where(e => e.EntityId == 1)
	.Fetch(SelectMode.Skip,
		e => e.ReferencedEntity)
	.List();

the resulting SQL is:

    SELECT
        this_.EntityId as entityid1_0_3_,
        this_.EntityNumber as entitynumber2_0_3_,
        this_.ReferencedEntityId as referencedentityid3_0_3_,
        this_.AdditionalEntityId as additionalentityid4_0_3_,
        this_.SourceEntityId as sourceentityid5_0_3_,
        entity2_.EntityId as entityid1_0_0_,
        entity2_.EntityNumber as entitynumber2_0_0_,
        entity2_.ReferencedEntityId as referencedentityid3_0_0_,
        entity2_.AdditionalEntityId as additionalentityid4_0_0_,
        entity2_.SourceEntityId as sourceentityid5_0_0_,
        entity3_.EntityId as entityid1_0_1_,
        entity3_.EntityNumber as entitynumber2_0_1_,
        entity3_.ReferencedEntityId as referencedentityid3_0_1_,
        entity3_.AdditionalEntityId as additionalentityid4_0_1_,
        entity3_.SourceEntityId as sourceentityid5_0_1_,
        entity4_.EntityId as entityid1_0_2_,
        entity4_.EntityNumber as entitynumber2_0_2_,
        entity4_.ReferencedEntityId as referencedentityid3_0_2_,
        entity4_.AdditionalEntityId as additionalentityid4_0_2_,
        entity4_.SourceEntityId as sourceentityid5_0_2_ 
    FROM
        Entity this_ 
    left outer join
        Entity entity2_ 
            on this_.AdditionalEntityId=entity2_.EntityId 
    left outer join
        Entity entity3_ 
            on entity2_.ReferencedEntityId=entity3_.EntityId 
    left outer join
        Entity entity4_ 
            on entity3_.SourceEntityId=entity4_.EntityId 
    WHERE
        this_.EntityId = :p0;

So entity3_ and entity4_ are still joined incorrectly, but now aliases for entity2_ and entity3_ are switched - entity2_ is AdditionalEntity and entity3_ is ReferencedEntity.

[hazzik]

@PiotrKlecha @bahusoid has it regressed?

[bahusoid]

@hazzik I can't code/debug right now so I can't really say. But it doesn't look like recent regression to me. Also I believe the same behavior should be for session.Get<Entity>(1)

[PiotrKlecha]

@hazzik I noticed this after an upgrade from version 4.0.3, so I can't say if it's a recent regression.

@hazzik I can't code/debug right now so I can't really say. But it doesn't look like recent regression to me. Also I believe the same behavior should be for session.Get<Entity>(1)

That's correct, results are the same when using session.Get, as well as Criteria API (session.CreateCriteria).

However, LINQ Provider behaves differently. A query:

session
	.Query<Entity>()
	.Where(e => e.EntityId == 1)
	.ToList();

generates:

    select
        entity0_.EntityId as entityid1_0_,
        entity0_.EntityNumber as entitynumber2_0_,
        entity0_.ReferencedEntityId as referencedentityid3_0_,
        entity0_.AdditionalEntityId as additionalentityid4_0_,
        entity0_.SourceEntityId as sourceentityid5_0_ 
    from
        Entity entity0_ 
    where
        entity0_.EntityId=:p0;

It seems wrong as well, since referenced entities are not eagerly loaded, but I'm not sure if this is related.

[bahusoid]

I suspect it's due to some kind of circular reference detection logic. I suspect NHibernate code visits entity associations subproperties first (like entity -> entity.FirstAssociation -> entity.FirstAssociation.SubAssocation1.. -> entity.SecondAssociation ->...) and that could lead to this behaviour. So in your case our logic fetches firstly visited associations:
e.ReferencedEntity -> e.ReferencedEntity.AdditionalEntity -> e.ReferencedEntity.AdditionalEntity.SourceEntity
And all others associations ( e.AdditionalEntity, e.SourceEntity, ...) are considered as duplicates and skipped. Looks like we need some improvements in this area.

Are you sure it's working properly in 4.0.3? I believe it should work as you expected with "max_fetch_depth" setting set to 1. Otherwise I don't see how 4.0.3 can handle it any better. Can it be the case?

[PiotrKlecha]

Indeed - setting max_fetch_depth to 1 solves the issue and joins in SQL query are correct.

I also confirm that it's the same in 4.0.3, so it's not something very recent.

[deAtog]

I dug into this issue to see if it was similar to a case I was having an issue with, and it seems they are indeed related. When generating the query, NHibernate iterates over each property of the root type and recursively adds any associations with fetch="join" specified. Due to the recursion, an association is only added once at the deepest level which explains the invalid SQL generated for the example provided. If this check were removed, the example provided would recursively loop forever on the first many-to-one association. In my opinion it would be much better to change the default max_fetch_depth setting from -1 to a reasonable value and provide a mechanism to override it on a per-query basis.