NH-3972 - SQL error when selecting a column of a subclass when sibling classes have a column of the same name

[nhibernate-bot]

Maik Schott created an issue — 29th March 2017, 11:58:56:

I have an entity class "DataRecord" which has four joined sub-classes "Incident", "Problem", "RequestForChange" and "Change". Each sub-class has property called "State" which is a many-to-one reference to four other tables - "IncidentState", "ProblemState", "RequestForChangeState" and "ChangeState" - respectively.

If I use the NHibernate LINQ provider to select one of "State" columns by explicitly providing the sub-class to look for, invalid SQL is created referencing the wrong sub-table.

My mapping is basically:

<?xml version="1.0" encoding="utf-8" ?>

<hibernate-mapping 
  xmlns="urn:nhibernate-mapping-2.2"
  assembly="Entities"
  namespace="Entities.Data">
      
  <class name="DataRecord" table="DataRecords" abstract="false" >
    <id name="Id" column="Id">
      <generator class="hilo">
        <param name="table">AppDBHiLo</param>
        <param name="column">NextHiValue</param>
        <param name="max_lo">10</param>
      </generator>
    </id>
    <discriminator column="Type" insert="false" type="int"/> 
    <version name="Version" column="Version" />
   
    <property name="Subject" column="Subject" not-null="false" />

    <joined-subclass name="Entities.Data.Incident" table="Incidents">
      <key column="DataRecordId"/>
      <many-to-one name="State" column="StateDataIncidentStateId" fetch="join"
      cascade="none" class="Entities.Data.DataIncidentState" not-null="false"/>
    </joined-subclass>
        
    <joined-subclass name="Entities.Data.Problem" table="Problems">
      <key column="DataRecordId"/>
      <many-to-one name="State" column="StateDataProblemStateId" fetch="select"
      cascade="none" class="Entities.Data.DataProblemState" not-null="false"/>
    </joined-subclass>
        
    <joined-subclass name="Entities.Data.RequestForChange" table="RequestForChanges">
      <key column="DataRecordId"/>
      <many-to-one name="State" column="StateDataRequestForChangeStateId" fetch="select"
      cascade="none" class="Entities.Data.DataRequestForChangeState" not-null="false"/>
    </joined-subclass>
        
    <joined-subclass name="Entities.Data.Change" table="Changes">
      <key column="DataRecordId"/>
      <many-to-one name="State" column="StateDataChangeStateId" fetch="select"
      cascade="none" class="Entities.Data.DataChangeState" not-null="false"/>
    </joined-subclass>
  </class>
</hibernate-mapping>

My LINQ query is:

var query = new NhQueryable<DataRecord>(session.GetSessionImplementation()).Select(x => new
{
	Subject = x.Subject,
	State = ((Change)x).State.Description
});

The NhQueryable must use the parent class here, because the query is dynamically built and may or may not include properties of the other sub-classes.

The constructed expression, i.e. query.Expression.DebugView, is:

.Call System.Linq.Queryable.Select(
    .Constant<NHibernate.Linq.NhQueryable`1<Entities.Data.DataRecord>>(NHibernate.Linq.NhQueryable`1<Entities.Data.DataRecord>),
    '(.Lambda #Lambda1<System.Func`2<Entities.Data.DataRecord,<>f**AnonymousType0`2[System.String,System.String>]>))

.Lambda #Lambda1<System.Func`2[Entities.Data.DataRecord,<>f**AnonymousType0`2[System.String,System.String]]>(Entities.Data.DataRecord $x)
{
    .New <>f**AnonymousType0`2<System.String,System.String>(
        $x.Subject,
        (((Entities.Data.Change)$x).State).Description)
}

When executing I get the following GenericADOException:

could not execute query

[ select
	datarecord0*.<Subject> as col_0_0*, 
	datarecord1*.[Description] as col_1_0* 
  from
	[DataRecords] datarecord0_ left outer join 
	[Incidents] datarecord0*1_ on datarecord0_.[Id]=datarecord0_1*.[DataRecordId] left outer join 
	[Problems] datarecord0*2_ on datarecord0_.[Id]=datarecord0_2*.[DataRecordId] left outer join 
	[RequestForChanges] datarecord0*3_ on datarecord0_.[Id]=datarecord0_3*.[DataRecordId] left outer join 
	[Changes] datarecord0*4_ on datarecord0_.[Id]=datarecord0_4*.[DataRecordId] left outer join 
	[DataStates] datarecord1* on datarecord0_1_.[StateDataChangeStateId]=datarecord1*.[Id] ]}}

with the InnerException:

Invalid column name 'StateDataChangeStateId'.

As you can see the last join to access ((Change)x).State is correctly using the foreign key column name "StateDataChangeStateId" of "Change" but incorrectly using the table for "Incident" (datarecord01).

Instead of datarecord0*1_.<StateDataChangeStateId> it should be datarecord0_4*.[StateDataChangeStateId].

---

Frédéric Delaporte added a comment — 29th March 2017, 14:20:28:

This Linq query looks invalid to me. It does not surprise me that querying a base class and trying to project a subclass specific property by "cheating" with a cast utterly fails.

---

Frédéric Delaporte added a comment — 30th March 2017, 12:25:32:

Querying like this is semantically incorrect: is is non-sense for rows resolving to another subclass. Therefor I do not consider this as being a NHibernate bug, but a user query bug (invalid query). Closing.

---

Maik Schott added a comment — 30th March 2017, 13:57:08:

What would be the correct way to query a class and project columns from joined tables representing sub-classes?

The result I want is:

SELECT rec.Subject, i.SomeProperty, cs.Description
FROM
  DataRecords rec LEFT OUTER JOIN
  Incidents i ON rec.Id=i.DataRecordId LEFT OUTER JOIN
  Changes c ON rec.Id=c.DataRecordId LEFT OUTER JOIN
  ChangeStates cs ON c.StateDataChangeStateId = cs.Id

Isn't this semantically equal to (where DataRecordId and StateDataChangeStateId are foreign keys): {code:c#} var query = new NhQueryable<DataRecord>(session.GetSessionImplementation()).Select(x => new { Subject = x.Subject, SomeProperty = ((Incident)x).SomeProperty, State = ((Change)x).State.Description }); {code}

---

Frédéric Delaporte added a comment — 30th March 2017, 14:10:30:

No, it is not, since in iterative languages such as .Net, the Linq query must fail (think to what it would do with linq-to-objects: trying to hard cast an Incident to Change or the other way round, depending on what x is currently).

Maybe support for this could be added (I do not think it is currently supported):

var query = session.Query<DataRecord>()
    .Select(x => new
    {
        Subject = x.Subject,
        SomeProperty = (x as Incident)?.SomeProperty,
        State = (x as Change)?.State.Description
    });

Maybe this should already work:

var query = session.Query<DataRecord>()
    .Select(x => new
    {
        Subject = x.Subject,
        SomeProperty = x is Incident ? ((Incident)x).SomeProperty : null,
        State = x is Change ? ((Change)x).State.Description : null
    });

---

Maik Schott added a comment — 30th March 2017, 14:47:15:

But it does work perfectly fine in general, i.e. NHibernate has no trouble accessing ((Incident)x).SomeProperty.

It just doesn't work for properties sharing the same name with properties of sibling subclasses, because in this case for the foreign table reference NHibernate uses first child class (Incident in my mapping), but for the foreign key reference NHibernate uses the last child class (Change in my mapping). If I switch the order in my mapping (i.e. exchange Incident and Change) and do not change my LINQ instead of datarecord0*1_.<StateDataChangeStateId> with datarecord0_1_ being Incident it suddenly becomes datarecord0_1_.[StateDataIncidentStateId] with datarecord0_1* now being Change.

Yes, I could also use an as-cast which preserves the notion of the cast being expected to fail sometimes (and NHibernate supports this, but seems to treat it like a hard cast), but using the null-propagating operator ?. is impossible, as expression trees as used by the IQuerable methods do not support this.

Unfortunately, your example doesn't work and basically generates the same SQL as without type checking, except it includes a new column for representing the x is Incident and x is Change condition expression.

[bahusoid]

But it does work perfectly fine in general, i.e. HNibernate has no trouble accessing ((Incident)x).SomeProperty. It just doesn't work for properties sharing the same name with properties of sibling subclasses

It seems the main issue here is that there is no syntax in hql to express desired behavior - it's not possible to select specific subclass property in hql select. It only supports kinda duck typing like select base.SubclassProperty where subclass is detected by requested subclass property name. But if SubclassProperty is present in multiple subclasses there is no way to specify which one to use.

So one possible solution - add support for entity casting in hql. Something like select cast_entity(alias, SublcassEntityName).AmbigousSubclassProperty

[bahusoid]

add support for entity casting in hql. Something like select cast_entity(alias, SublcassEntityName)

Hibernate uses treat - treat(alias as SublcassEntityName). Series of commits adding this functionality: https://github.com/search?q=repo%3Ahibernate%2Fhibernate-orm+treat&type=commits&s=committer-date&o=asc