Invalid SQL created for some joins in a subquery

[dplaskon]

I have found what appears to be a regression in the 5.5.0 release (this worked fine previously with 5.4.6.

The mappings are rather complex, so I'll do my best to explain here:

The hql fragment causing the issue is as follows:

and exists(
from om.ActivityShiftAssignments ass, ScheduledActivityShift s 
inner join s.Activity a
where ass.ActivityShift.Id = s.Id
and a.Active = 1
and ( s.ShiftInterval.Start between :fromDate and :toDate or :fromDate between s.ShiftInterval.Start and s.ShiftInterval.End )
)

This generates the following sql which is invalid:

and (exists (
select activitysh2_.ActivityShiftAssignmentId, scheduleda3_.ActivityShiftId, activity4_.ActivityId 
from ActivityShiftAssignment activitysh2_ 
where scheduleda3_.ActivityTypeId=3 
and organizati0_.OrganizationMemberId=activitysh2_.OrganizationMemberId 
and organizati0_.OrganizationMemberId=activitysh2_.OrganizationMemberId 
and activitysh2_.ActivityShiftId=scheduleda3_.ActivityShiftId 
and activity4_.Active=1 
and (scheduleda3_.StartDate between ? and ? or ? between scheduleda3_.StartDate and scheduleda3_.EndDate))) 

As you can see "scheduleda3_" is never included in the from statement, and a join for "activity4_" is never generated either.

I'm not sure what details may be relevant here, other than to say that ScheduledActivityShift is a subclass of ActivityShift, which is directly referenced in the ActivityShiftAssignment entity. I used the implicit join in order to access some of the subclass-specific properties previously.

I can attempt to create a minimal test case, but wanted to put this out there first in case others (with simpler mappings) have encountered the same issue.

[fredericDelaporte]

It does not look like there is other reports for this as of now. Have you been able to elaborate a test case?

[dplaskon]

@fredericDelaporte - sorry, I have not had the time to do so. I will try to put something minimal together next week.

[dplaskon]

My guess would be that this might be related to: #3369

But I'm not entirely sure without digging further.

[dplaskon]

Ok, the repro is actually much simpler than I had originally thought. An anonymized query, but this will do the trick:

select e.Id from EntityA e where exists (from e.Entities b, EntityB c)

Where "e.Entities" is mapped as a set (or probably any other type of collection) on EntityA

It seems that multiple items in a select list in exists after the initial collection are ignored.

[fredericDelaporte]

And that example works in 5.4.x and fails in 5.5.x? Or does it just not include the (unneeded) join on EntityB? If that is the later, it would be better to have an actually failing test case on 5.5 but succeeding on 5.4.x. Can you also provide the example class definition? We have too little information from your example to know maybe relevant details. The set belonging to EntityA contains what? Other EntityA, or EntityB, or something else?

[dplaskon]

@fredericDelaporte - here are some additional details that should help.

Consider the following mappings:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-access="property">
  <class name="EntityA" table="EntityA" lazy="true">
    <id name="Id" column="Id">
      <generator class="native" />
    </id>
    <set name="Children" table="EntityB" cascade="all-delete-orphan" lazy="true" inverse="true" batch-size="10">
      <key column="EntityAId" not-null="true" />
      <one-to-many class="EntityB" />
    </set>
  </class>
  <class name="EntityB" table="EntityB" lazy="true">
    <id name="Id" column="Id">
      <generator class="native" />
    </id>
    <many-to-one name="Parent" column="EntityAId" class="EntityA" not-null="true" />
  </class>
  <class name="EntityC" table="EntityC" lazy="true">
    <id name="Id" column="Id">
      <generator class="native" />
    </id>
  </class>
</hibernate-mapping>

When this hql query is executed:

select e.Id from EntityA e where exists (from e.Children b, EntityC c)

5.4.6 produces:

select entitya0_.Id as col_0_0_
from   EntityA entitya0_
where  exists (select children1_.Id,
                      entityc2_.Id
               from   EntityB children1_,
                      EntityC entityc2_
               where  entitya0_.Id = children1_.EntityAId)

5.5.0 produces:

ERROR: 
Could not execute query: 
select entitya0_.Id as col_0_0_ from EntityA entitya0_ 
where exists (select children1_.Id, entityc2_.Id
  from EntityB children1_
  where entitya0_.Id=children1_.EntityAId
    and entitya0_.Id=children1_.EntityAId)

The multi-part identifier "entityc2_.Id" could not be bound.

I hope this helps - let me know if I can provide any further info!

[fredericDelaporte]

7cd90cc adds the corresponding test case. It does fail when cherry-picked on 3875011 (#3369 commit). But it does not fail when cherry-picked on the previous commit, 0fdb589.

The #3369 change here is the culprit:

nhibernate-core/src/NHibernate/Hql/Ast/ANTLR/Tree/FromElement.cs

Lines 719 to 722 in 07c367f

	else
	{
	FromClause.AppendFromElement(this);
	}

It does add the node as a from element even when it is already from a from. I propose a fix in #3476.

[fredericDelaporte]

Fixed by #3476.