InvalidWithClauseException when join polymorphic entity

[chrwell]

Situation

Updating NHibernate from 5.2.7 to 5.3.3 we ran into issues with queries operating on inheritance hierarchies. We join inherited entities and run operators such as distincts or simple selects. Analyzing the exception with clause can only reference columns in the driving table we soon came to find the cause being the HqlSqlWalker and its WithClauseVisitor. There the names of aliases are compared and if different, an exception is thrown.

Comparison with Hibernate

After some research we found this to be an issue in Hibernate as well: https://hibernate.atlassian.net/browse/HHH-11383. It was fixed here: hibernate/hibernate-orm@5ac46eb.

We use NHibernate as our ORM for large-scale projects and ensure compability with a test coverage of 20K+ tests (hence we discovered this issue quickly). For testing purposes we disabled the exception (as in Hibernate) and ran our queries, which then work smoothly.

Request

Is it possible to adapt the change from Hibernate to NHibernate and disable the check of alias names in this case and consequently not throwing the exception?

On a side note: perhaps this became a problem in the first place after NHibernate release 5.3.0 and this change #2078

[bahusoid]

I don't see how #2078 can lead to exception you see. Can you provide example of query that stopped working in 5.3?

Is it possible to adapt the change from Hibernate

This check is no longer present in master (5.4) branch. But it's part of bigger changes that won't be backported to 5.3 branch

[chrwell]

Sure, here a simplified example that leads to the exception. Let's say we have Adult and Child entities, both inheriting from Person, and Family entities connecting them by navigation property.

Mappings

public class FamilyEntity
{
	public long Id { get; set; }
	public ICollection<PersonEntity> Persons { get; set; }
}

public class FamilyMap : ClassMapping<FamilyEntity>
{
	public FamilyMap()
	{
		Table("Families");

		Id(p => p.Id,
			id =>
			{
				id.Column("Family_Id");
				id.Generator(Generators.Increment);
			});

		Bag(p => p.Persons,
			bag =>
			{
				bag.Inverse(true);
				bag.Cascade(Cascade.All.Include(Cascade.DeleteOrphans));
				bag.Lazy(CollectionLazy.NoLazy);
				bag.Key(k => k.Column("Family_Id"));
			}, a => a.OneToMany());
	}
}

public class PersonEntity
{
	public long Id { get; set; }
	
	private FamilyEntity _family;
	public FamilyEntity Family
	{
		get { return _family; }
		set
		{
			_family?.Persons.Remove(this);
			_family = value;
			_family?.Persons.Add(this);
		}
	}
}

public class PersonMap : ClassMapping<PersonEntity>
{
	public PersonMap()
	{
		Table("Persons");

		Id(p => p.Id,
			id =>
			{
				id.Column("Person_Id");
				id.Generator(Generators.Increment);
			});

		ManyToOne(p => p.Family,
			r =>
			{
				r.Lazy(LazyRelation.NoLazy);
				r.Access(Accessor.Field);
				r.Column("Family_Id");
				r.NotNullable(true);
			});
	}
}

public class AdultEntity : PersonEntity
{
}

public class AdultMap : JoinedSubclassMapping<AdultEntity>
{
	public AdultMap()
	{
		Table("Adults");
		Key(prop => prop.Column("Person_Id"));
	}
}

public class ChildEntity : PersonEntity
{
}

public class ChildMap : JoinedSubclassMapping<ChildEntity>
{
	public ChildMap()
	{
		Table("Children");
		Key(prop => prop.Column("Person_Id"));
	}
}

Query

Now a query that will get all children ids belonging to a parent via the family:

ISessionFactory sessionFactory;
long adultId;

using (var session = sessionFactory.OpenSession())
{
	var ids = (from adult in session.Query<AdultEntity>()
				join child in session.Query<ChildEntity>()
					on adult.Family.Id equals child.Family.Id
					where adult.Id == adultId
				select child.Id)
		.Distinct()
		.ToList();
}

Exception

We get this stack trace:

NHibernate.Hql.Ast.ANTLR.InvalidWithClauseException: with clause can only reference columns in the driving table [.Distinct[System.Int64, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089](.Select[<>f__AnonymousType1`2[[AdultEntity],[ChildEntity]], System.Int64, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089](.Where[<>f__AnonymousType1`2[[AdultEntity],[ChildEntity]]](.Join[AdultEntity,ChildEntity,System.Int64, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089,<>f__AnonymousType1`2[[AdultEntity],[ChildEntity]]](NHibernate.Linq.NhQueryable`1[AdultEntity], NHibernate.Linq.NhQueryable`1[ChildEntity], Quote((adult) => (adult.Family.Id)), Quote((child) => (child.Family.Id)), Quote((adult, child, ) => (new <>f__AnonymousType1`2[[AdultEntity],[ChildEntity]](adult, child)))), Quote((<>h__TransparentIdentifier0, ) => (Equal(<>h__TransparentIdentifier0.adult.Id, Convert(p1<System.Int32>))))), Quote((<>h__TransparentIdentifier0, ) => (<>h__TransparentIdentifier0.child.Id))))]
    bei NHibernate.Hql.Ast.ANTLR.WithClauseVisitor.Visit(IASTNode node)
   bei NHibernate.Hql.Ast.ANTLR.Util.NodeTraverser.VisitDepthFirst(IASTNode ast)
   bei NHibernate.Hql.Ast.ANTLR.Util.NodeTraverser.VisitDepthFirst(IASTNode ast)
   bei NHibernate.Hql.Ast.ANTLR.Util.NodeTraverser.TraverseDepthFirst(IASTNode ast)
   bei NHibernate.Hql.Ast.ANTLR.HqlSqlWalker.HandleWithFragment(FromElement fromElement, IASTNode hqlWithNode)
   bei NHibernate.Hql.Ast.ANTLR.HqlSqlWalker.CreateEntityJoin(IQueryable entityPersister, IASTNode aliasNode, Int32 joinType, IASTNode with)
   bei NHibernate.Hql.Ast.ANTLR.HqlSqlWalker.CreateFromJoinElement(IASTNode path, IASTNode alias, Int32 joinType, IASTNode fetchNode, IASTNode propertyFetch, IASTNode with)
   bei NHibernate.Hql.Ast.ANTLR.HqlSqlWalker.joinElement()
   bei NHibernate.Hql.Ast.ANTLR.HqlSqlWalker.fromElement()
   bei NHibernate.Hql.Ast.ANTLR.HqlSqlWalker.fromElementList()
   bei NHibernate.Hql.Ast.ANTLR.HqlSqlWalker.fromClause()
   bei NHibernate.Hql.Ast.ANTLR.HqlSqlWalker.unionedQuery()
   bei NHibernate.Hql.Ast.ANTLR.HqlSqlWalker.query()
   bei NHibernate.Hql.Ast.ANTLR.HqlSqlWalker.selectStatement()
   bei NHibernate.Hql.Ast.ANTLR.HqlSqlWalker.statement()
   bei NHibernate.Hql.Ast.ANTLR.HqlSqlTranslator.Translate()
   bei NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.Analyze(String collectionRole)
   bei NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.DoCompile(IDictionary`2 replacements, Boolean shallow, String collectionRole)
   bei NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IQueryExpression queryExpression, IASTNode ast, String queryIdentifier, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)
   bei NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)
   bei NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters)
   bei NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow)
   bei NHibernate.Impl.AbstractSessionImpl.CreateQuery(IQueryExpression queryExpression)
   bei NHibernate.Linq.DefaultQueryProvider.PrepareQuery(Expression expression, IQuery& query)
   bei NHibernate.Linq.DefaultQueryProvider.ExecuteList[TResult](Expression expression)
   bei NHibernate.Linq.NhQueryable`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   bei System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   bei System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

[bahusoid]

Ok. I see. #2078 is unrelated. And I'm afraid simple removal of exception won't really help you unless SQLite is your only database.

The problem is that in 5.3 we started to use real inner/left joins in generated SQL for LINQ queries instead of implicit joins (#2327, #2328). But your case with subclasses is not handled nicely as you use base table column in ON statement. SQL generated in 5.3 with exception removed:

SELECT DISTINCT childentit1_.Person_Id AS col_0_0_
FROM Adults adultentit0_
INNER JOIN Persons adultentit0_1_ ON adultentit0_.Person_Id = adultentit0_1_.Person_Id
INNER JOIN Children childentit1_ ON (childentit1_1_.Family_Id = adultentit0_1_.Family_Id) -- <-- childentit1_1_ is not yet joined
INNER JOIN Persons childentit1_1_ ON childentit1_.Person_Id = childentit1_1_.Person_Id
WHERE adultentit0_.Person_Id = @p0

As you can see in 5.3 base table Persons childentit1_1_ is joined after Children childentit1_ so ON clause for Children is invalid. SQLite is the only db engine (that I know) that is able to process such queries.
Good news: this issue is already fixed in master branch by #2361.
Bad news: not sure how to proceed with 5.3 branch.

As a workaround in 5.3 you can use cross join for such queries (kinda simulate behavior of 5.2):

var ids = (from adult in session.Query<AdultEntity>()
		from child in session.Query<ChildEntity>() //join is replaced with from and ON condition is moved to where
		where adult.Family.Id == child.Family.Id && adult.Id == adultId
		select child.Id)
	.Distinct()
	.ToList();

[bahusoid]

not sure how to proceed with 5.3 branch

Maybe just for 5.3 we should simply fallback to cross join if inner entity join is done on polymorphic entity... Don't know LINQ parts very well but seems this logic can be placed here

Can be reproduced adding the following test to JoinTests:

[Test(Description = "GH-2580")]
public void CanInnerJoinOnSubclassWithBaseTableReferenceInOnClause()
{
	var result = (from o in db.Animals
				join o2 in db.Mammals on o.BodyWeight equals  o2.BodyWeight
				select new {o, o2}).Take(1).ToList();
}

[fredericDelaporte]

@maca88, may you take a look on this issue?

[maca88]

I've made a PR that checks whether an inherited member from another table is used in InnerKeySelector and in case that it exists, a cross join will be used.

[fredericDelaporte]

Fixed by #2581