*** SUMMARY: ***
When we have a mixed table-per-class-hierarchy / table-per-subclass persistence implementation for inheritance, a polymorphic query on the abstract base class does not work (even when the object being retrieved is not the subclass that has the <join> element). The generated query includes the fields from all the <join> tables but does not include the joined tables in the FROM clause (I included the generated query). Note that when I remove all <join> elements (meaning, I remove the 'table-per-subclass' part), the polymorphic query works.

*** DETAILS: ***
In the example, we have the following simple inheritance hierarchy:
- PhysicalNetworkDevice - abstract base class; has all common Properties
- - Hub - subclass of PhysicalNetworkDevice; has no extra Properties of its own
- - Router - subclass of PhysicalNetworkDevice; has extra Properties of its own
- - Switch - subclass of PhysicalNetworkDevice; has extra Properties of its own

The database tables are as follows (uses a mixed table-per-class-hierarchy / table-per-subclass inheritance implementation):
tblDevice: implements table-per-class-hierarchy inheritance with id field=ID; discriminator field=TypeName
tblRouter: table-per-subclass inheritance with id field=ID; foreign key to tblDevice=FK_pDeviceID
tblSwitch: table-per-subclass inheritance with id field=ID; foreign key to tblDevice=FK_pDeviceID

Hibernate version: Custom-built 1.2.0.GA merged with the NH-466 patch

Mapping documents:

Notice the <join> element:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false">
<class name="Domain.PhysicalDevice.PhysicalNetworkDevice, Domain" table="dbo.tblDevice">

<id type="Int32" column="ID" name="ID" unsaved-value="null">
<generator class="identity"></generator>
</id>
<discriminator column="TypeName"/>
<property name="Manufacturer" column="Manufacturer"/>
<subclass name="Domain.PhysicalDevice.Hub, Domain" discriminator-value="Hub">
</subclass>
<subclass name="Domain.PhysicalDevice.Router, Domain" discriminator-value="Router">
<join table="dbo.tblRouter">
<key column="FK_pDeviceID"/>
<property name="Tier" column="Tier"/>
<property name="LoopBackIP" column="LoopBackIP"/>
</join>
</subclass>
<subclass name="Domain.PhysicalDevice.Switch, Domain" discriminator-value="Switch">
<join table="dbo.tblSwitch">
<key column="FK_pDeviceID"/>
<property name="Tier" column="Tier"/>
</join>
</subclass>
</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Note: The id, 6, refers to a Hub object (which is a subclass that doesn't even have the <join> element). The query is wrong both when we retrieve a Hub object or when we retrieve an object with a <join> element (Router, Switch). When I remove the <join> element from all <subclass> elements, the polymorphic query works fine. Note that all of the following produce the same error:

// retrieve-by-id
Object o = session.Get(typeof(PhysicalNetworkDevice), 6);

// HQL
IQuery q = _session.CreateQuery("FROM PhysicalNetworkDevice WHERE ID=:id");
q.SetInt32("id", 6);
q.List();

// ICriteria
ICriteria c = _session.CreateCriteria(typeof(PhysicalNetworkDevice));
c.Add(Expression.Eq("ID", 6));
c.List();

Full stack trace of any exception that occurs:
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
at NHibernate.Loader.Criteria.CriteriaLoader.List(ISessionImplementor session)
at NHibernate.Impl.SessionImpl.Find(CriteriaImpl criteria, IList results)
at NHibernate.Impl.SessionImpl.Find(CriteriaImpl criteria)
at NHibernate.Impl.CriteriaImpl.List()

Name and version of the database you are using: Microsoft SQL Server 2005; Version: 9.00.1406.00; Product Level: RTM; Edition: Developer Edition

The generated SQL (show_sql=true):

Notice that the fields from tblRouter (Tier and Loopback) and tblSwitch (Tier) are included but their tables are not:

{SELECT physicalne0_.ID as ID0_0_, physicalne0_.Manufacturer as Manufact3_0_0_, physicalne0_1_.Tier as Tier1_0_, physicalne0_1_.LoopBackIP as LoopBackIP1_0_, physicalne0_2_.Tier as Tier2_0_
FROM dbo.tblDevice physicalne0_
WHERE physicalne0_.ID=?}

Debug level Hibernate log excerpt: {"The multi-part identifier \"physicalne0_1_.Tier\" could not be bound.\r\nThe multi-part identifier \"physicalne0_1_.LoopBackIP\" could not be bound.\r\nThe multi-part identifier \"physicalne0_2_.Tier\" could not be bound."}

I would expect that two queries actually happens. First to check discriminator value to determine class and second to actually get the object.

Edit:

I have actually tried this on SVN version with 2 classes A, B, where A is base class of B. Querying for all A objects generated invalid query accessing B's properties, but not including B in 'FROM'. IMHO the correct way is to use 2 queries so that large inheritance hierarchies doesn't suffer from big performance penalty.

Hi all!

I have this same issue!!!, with sub-classes only in NHibernate HQL query. Has this issue been fixed?

Or, can we not do complex queries using per table base class and per table sub-classes?

It seems like the join between on class and another base class table does not work properly with the sub-class fields. I'm seeing the incorrect field names in the generated query. While the HQL query is using the sub-class fields.

I'd post my code but it's about 500 or 600 lines of code and would be difficault to read.

Seems like for an object base query one object against another object that is a base table and the query should be smart enough to figure out the sub-class fields that are referenced.

In my case the object1 that is Left Outer Joined with object2 (a list of base class object) . I don't have a list of sub-class objects. Maybe this is the key to add a list of the sub-class objects???

Jerry