NH-3622 - Fetching in query causes incorrect/missing joins in subquery

[nhibernate-bot]

Anish Patel created an issue — 19th June 2014, 12:18:41:

Given this Data Model, where there are 3 joins between Tag and DisciplineType :

public class Tag {
    public virtual Guid Id { get; set; }
    public virtual string Name { get; set; }
    public virtual Equipment Equipment { get; set; }
}

public class Equipment {
    public virtual Guid Id { get; set; }
    public virtual string Name { get; set; }
    public virtual Discipline Discipline { get; set; }
}

public class Discipline {
    public virtual Guid Id { get; set; }
    public virtual string Name { get; set; }
    public virtual DisciplineType DisciplineType { get; set; }
}

public class DisciplineType {
    public virtual Guid Id { get; set; }
    public virtual string Name { get; set; }
}


public class TagMap : ClassMapping<Tag> {
    public TagMap() {
        this.Id(x => x.Id, mapper => mapper.Generator(Generators.Assigned));
        this.Property(x => x.Name, mapper => mapper.NotNullable(true));
        this.ManyToOne
            (x => x.Equipment,
             mapper => {
                 mapper.NotNullable(true);
                 mapper.Column("EquipmentId");
             });
    }
}

public class EquipmentMap : ClassMapping<Equipment> {
    public EquipmentMap() {
        this.Id(x => x.Id, mapper => mapper.Generator(Generators.Assigned));
        this.Property(x => x.Name, mapper => mapper.NotNullable(true));
        this.ManyToOne
            (x => x.Discipline,
             mapper => {
                 mapper.Column("DisciplineId");
                 mapper.NotNullable(false);
             });
    }
}

public class DisciplineMap : ClassMapping<Discipline> {
    public DisciplineMap() {
        this.Id(x => x.Id, mapper => mapper.Generator(Generators.Assigned));
        this.Property(x => x.Name, mapper => mapper.NotNullable(true));
        this.ManyToOne
            (x => x.DisciplineType,
             mapper =>
             {
                 mapper.Column("DisciplineTypeId");
                 mapper.NotNullable(false);
             });
    }
}

public class DisciplineTypeMap : ClassMapping<DisciplineType> {
    public DisciplineTypeMap() {
        this.Id(x => x.Id, mapper => mapper.Generator(Generators.Assigned));
        this.Property(x => x.Name, mapper => mapper.NotNullable(true));
    }
}

When I execute this Linq query:

using (var s = sessionFactory.OpenSession()) {
    using (var t = s.BeginTransaction()) {

        var x = s.Query<Tag>()
                 .Where(tag => tag.Equipment.Discipline.DisciplineType.Id == id)
                 .Select(tag => tag.Id);

        var y = s.Query<Tag>()
                 .Fetch(tag => tag.Equipment)
                 .ThenFetch(equipment => equipment.Discipline)
                 .ThenFetch(discipline => discipline.DisciplineType)
                 .Where(tag => x.Contains(tag.Id))
                 .ToList();
        
        t.Commit();
    }
}

I expect the generated SQL to be this:

SELECT tag0_.Id AS Id0_0_,
       equipment1_.Id AS Id1_1_,
       discipline2_.Id AS Id2_2_,
       discipline3_.Id AS Id3_3_,
       tag0_.Name AS Name0_0_,
       tag0_.EquipmentId AS Equipmen3_0_0_,
       equipment1_.Name AS Name1_1_,
       equipment1_.DisciplineId AS Discipli3_1_1_,
       discipline2_.Name AS Name2_2_,
       discipline2_.DisciplineTypeId AS Discipli3_2_2_,
       discipline3_.Name AS Name3_3_
FROM Tag tag0_
LEFT OUTER JOIN Equipment equipment1_ ON tag0_.EquipmentId=equipment1_.Id
LEFT OUTER JOIN Discipline discipline2_ ON equipment1_.DisciplineId=discipline2_.Id
LEFT OUTER JOIN DisciplineType discipline3_ ON discipline2_.DisciplineTypeId=discipline3_.Id
WHERE tag0_.Id IN
    ( SELECT tag4_.Id
     FROM Tag tag4_
     INNER JOIN Equipment equipment5_ ON tag4_.EquipmentId=equipment5_.Id
	 INNER JOIN Discipline discipline6_ ON equipment5_.DisciplineId=discipline6_.Id
     WHERE discipline6_.DisciplineTypeId= '143e44f1-13f3-49f4-a2f0-d92c4cdef911' );

However, this is the actual SQL generated:

SELECT tag0_.Id AS Id0_0_,
       equipment1_.Id AS Id1_1_,
       discipline2_.Id AS Id2_2_,
       discipline3_.Id AS Id3_3_,
       tag0_.Name AS Name0_0_,
       tag0_.EquipmentId AS Equipmen3_0_0_,
       equipment1_.Name AS Name1_1_,
       equipment1_.DisciplineId AS Discipli3_1_1_,
       discipline2_.Name AS Name2_2_,
       discipline2_.DisciplineTypeId AS Discipli3_2_2_,
       discipline3_.Name AS Name3_3_
FROM Tag tag0_
LEFT OUTER JOIN Equipment equipment1_ ON tag0_.EquipmentId=equipment1_.Id
LEFT OUTER JOIN Discipline discipline2_ ON equipment1_.DisciplineId=discipline2_.Id
LEFT OUTER JOIN DisciplineType discipline3_ ON discipline2_.DisciplineTypeId=discipline3_.Id
WHERE tag0_.Id IN
    ( SELECT tag4_.Id
     FROM Tag tag4_
     INNER JOIN Equipment equipment5_ ON tag4_.EquipmentId=equipment5_.Id
     WHERE discipline2_.DisciplineTypeId= '143e44f1-13f3-49f4-a2f0-d92c4cdef911' );

This results in the Where clause in the subquery to try and filter the subresults using a table in the outer query.

This always happens whenever I need to use ThenFetch to eager load something that requires two or more joins.

This is causing the query to execute extremely slowly.

---

Anish Patel added a comment — 19th June 2014, 12:24:46:

This issue is also present in version 3.3.3.SP1

---

Anish Patel added a comment — 21st July 2014, 9:58:36:

I've created a test for this issue:
https://github.com/anishpateluk/nhibernate-core/tree/NH-3622

---

Anish Patel added a comment — 10th November 2014, 8:07:20:

Any plans to fix this issue?

---

Alexander Zaytsev added a comment — 10th November 2014, 8:44:33:

<~anishpateluk>, can you please check with older version? I would like to understand if this an regression or not.

[bahusoid]

Related to https://nhibernate.jira.com/browse/NH-3002

I would say applied fix was incorrect. I'm going just to copy existing check from hibernate:
https://github.com/hibernate/hibernate-orm/blob/5ecf45020ff5f7d269425abf7d25823ca4776f62/hibernate-core/src/main/java/org/hibernate/hql/internal/ast/tree/DotNode.java#L555-L564