NH-3640 - Inner Join on reffered entities in union-subclass should work correct

[nhibernate-bot]

dennis.kovalenko created an issue — 18th July 2014, 15:08:19:

On our project we have needs to make data federalization. We need split one table on separate tables depending of specialty. To work with those tables I use nhibernate feature union-subclass.
For example we have 2 base entities: Animal and Box. Animal has reference to Box.
To split tables we need to do some steps:

1. Create 2 separate tables CatBox and DogBox instead of Box with fields (Id,Name) and tables Cat and Dog instead of Animal (ID, Name, BoxId).
   Create sequences for ids: animalid_seq and box_idseq.
2. Create new classes Cat and Dog and inherit it from Animal. Same for CatBox and DogBox

public abstract class Animal
    {
        public virtual long Id { get; set; }
        public virtual string Name { get; set; }
        public virtual Box Box { get; set; }
    }
    public class Cat : Animal { }
    public class Dog : Animal { }

    public abstract class Box
    {
        public virtual long Id { get; set; }
        public virtual string Name { get; set; }
    }
    public class CatBox : Box { }
    public class DogBox : Box { }

3. Create mappings.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-access="property" assembly="NHibernateTest" namespace="NHibernateTest.DataBase.Domain">
  <class name="Animal" abstract="true" >
    <id name="Id" type="Int64">
      <generator class="sequence">
        <param name="sequence">animal*id*seq</param>
      </generator>
    </id>
    <property name="Name" />

    <union-subclass name="Cat" table="`Cat`">
      <many-to-one name="Box"  column="BoxId" class="CatBox" />
    </union-subclass>
    <union-subclass name="Dog" table="`Dog`">
      <many-to-one name="Box"  column="BoxId" class="DogBox"/>
    </union-subclass>
  </class>

  <class name="Box" abstract="true" >
    <id name="Id" type="Int64">
      <generator class="sequence"><param name="sequence">box*id*seq</param></generator>
    </id>
    <property name="Name" />

    <union-subclass name="CatBox" table="`CatBox`">
    </union-subclass>
    <union-subclass name="DogBox" table="`DogBox`">
    </union-subclass>
  </class>
</hibernate-mapping>

I have 2 scenarios of using:

1. Select special type of animal, for example Cats
2. Select all animals

When I do that without additional conditions all works good. But when I try add alias and condition to referred class Box, then happen strange thing.

1. In the first scenario all work like I expected.

var cats = session.CreateCriteria<Cat>()
                    .CreateAlias("Box", "box", JoinType.InnerJoin)
                    .Add(Restrictions.Eq("box.Name", "Test"))
                    .List<Cat>();

SELECT this*.Id AS Id48_1*
	,this*.NAME AS Name48_1*
	,this*.BoxId AS BoxId49_1*
	,box1*.Id AS Id51_0*
	,box1*.NAME AS Name51_0*
FROM <Cat> this_
INNER JOIN [CatBox] box1* ON this_.BoxId = box1*.Id
WHERE box1_.NAME = 'Test'

2. In the secont scenario here is an mistake:

	var animals = session.CreateCriteria<Animal>()
                    .CreateAlias("Box", "box", JoinType.LeftOuterJoin)
                    .Add(Restrictions.Eq("box.Name", "Test"))
                    .List<Animal>();

SELECT this*.Id AS Id48_2*
	,this*.NAME AS Name48_2*
	,this*.BoxId AS BoxId49_2*
	,this*.BoxId AS BoxId50_2*
	,this*.clazz_ AS clazz_2*
	,box1*.Id AS Id51_0*
	,box1*.NAME AS Name51_0*
	,box1*.Id AS Id51_1*
	,box1*.NAME AS Name51_1*
FROM (
	SELECT Id
		,NAME
		,BoxId
		,1 AS clazz_
	FROM <Cat>
	
	UNION ALL
	
	SELECT Id
		,NAME
		,BoxId
		,2 AS clazz_
	FROM [Dog]
	) this_
INNER JOIN [CatBox] box1* ON this_.BoxId = box1*.Id
INNER JOIN [DogBox] box1* ON this_.BoxId = box1*.Id
WHERE box1_.NAME = 'Test'

You see here is 2 same alias name and of course SQL error

I Think in this case inner join should be inside of UNION group. Here will be mistake even if we will have different names for property box because of inner join which should be inside of unions.

---

Alexander Zaytsev added a comment — 12th October 2014, 10:51:59:

A test case would be appreciated

---

LordJZ added a comment — 4th March 2015, 21:15:25:

I can confirm this bug. Simplified repro case:

  <class name="B" table="T" lazy="true" discriminator-value="0">
    <!-- id and other properties omitted -->

    <subclass name="D1" discriminator-value="1" lazy="true">
      <many-to-one name="R" class="R1" column="RId" not-null="true" />
    </subclass>

    <subclass name="D2" discriminator-value="2" lazy="true">
      <many-to-one name="R" class="R2" column="RId" not-null="true" />
    </subclass>
  </class>

public class B {
public virtual R R { get; set; }
}
public class D1 : B { }
public class D2 : B { }

Query for this class produces invalid SQL:

LEFT OUTER JOIN R r1* ON this_.RId=r1*.Id
LEFT OUTER JOIN R r1* ON this_.RId=r1*.Id

bq. The correlation name 'r1_' is specified multiple times in a FROM clause.

Specifying relationship for the base class B produces a different bug: wrong proxy type instantiated e.g. R for both D1 and D2 (i.e. properties are ignored) instead of R1 for D1 and R2 for D2.

Edit: formatting

---

LordJZ added a comment — 14th March 2015, 6:04:24:

Additionally, specifying eager fetch on property R makes NH think that it is of type R2, and when parsing query results it will fail with this exception:

{ code }Object with id: 12345 was not of the specified subclass: R2 (loading object was of wrong class ) { code }