NH-2174 - Invalid SQL is generated for OneToMany collections

[nhibernate-bot]

Anton created an issue — 14th April 2010, 3:13:57:

Mappings:

Detail:

<class lazy="false" table="o_Details" dynamic-update="true" name="Sang.OBranch.Server.Documents.Details.DocumentDetail, ORDB_lib">
    <cache usage="nonstrict-read-write" />
    <composite-id access="field">
      <key-property name="_id_item" column="id_item" />
      <key-property name="_Version" column="Version" />
      <key-property name="_id_Doc" column="id_Doc" />
      <key-property name="_id_base" column="id_base" />
    </composite-id>
    <discriminator column="id_type" />
  </class>

Inheritor:

  <subclass discriminator-value="102" extends="Sang.OBranch.Server.Documents.Details.DocumentDetail, ORDB_lib" lazy="false" name="Sang.OBranch.Server.Documents.Details.DocumentDetailDocument, ORDB_lib">
    <join table="o_DetailsDoc" fetch="select">
      <key>
        <column name="id_item" />
        <column name="Version" />
        <column name="id_Doc" />
        <column name="id_base" />
      </key>
      <many-to-one name="_ReferencedDocument" not-null="true" fetch="select" update="false" insert="false" lazy="false">
        <column name="id_Doc_detail" not-null="true" />
        <column name="id_base_detail" not-null="true" />
      </many-to-one>
    </join>
  </subclass>

Document:

  <class lazy="false" table="o_DocumentInstances" abstract="true" dynamic-update="true" name="Sang.OBranch.Server.Documents.Document, ORDB_lib">
    <cache usage="nonstrict-read-write" />
    <composite-id access="field">
      <key-property name="_id_Doc" column="id_Doc" />
      <key-property name="_id_base" column="id_base" />
    </composite-id>
    <discriminator column="Type" />
    <bag name="_RefferedDetails" lazy="true" inverse="true" generic="true">
      <key>
        <column name="id_Doc_detail" />
        <column name="id_base_detail" />
      </key>
      <one-to-many class="Sang.OBranch.Server.Documents.Details.DocumentDetailDocument, ORDB_lib" />
    </bag>
  </class>

So, when NH initializes DocumentInstance._RefferedDetails collection it generates wrong SQL:

SELECT refferedd0_.id_Doc_detail as id6_9_, refferedd0_.id_base_detail as id7_9_, refferedd0_.id_item as id1_9_, refferedd0_.Version as Version9_, refferedd0_.id_Doc as id3_9_, refferedd0_.id_base as id4_9_, refferedd0_.id_item as id1_25_8_, refferedd0_.Version as Version25_8_, refferedd0_.id_Doc as id3_25_8_, refferedd0_.id_base as id4_25_8_, refferedd0_1_.id_Doc_detail as id5_189_8_, refferedd0_1_.id_base_detail as id6_189_8_, refferedd0_.id_type as id5_25_8_, -- ...

FROM o_Details refferedd0_ 
inner join o_DetailsDoc refferedd0_1_ on refferedd0_.id_item=refferedd0_1_.id_item and refferedd0_.Version=refferedd0_1_.Version and refferedd0_.id_Doc=refferedd0_1_.id_Doc and refferedd0_.id_base=refferedd0_1_.id_base 

WHERE refferedd0_.id_Doc_detail=? and refferedd0_.id_base_detail=?

But, refferedd0_.id_Doc_detail as id6_9_ is wrong, because it is in o_DetailsDoc.id_Doc_detail, and alias should be refferedd0_1_

---

Anton added a comment — 14th April 2010, 3:15:05:

Sorry, JIRA formatting does not works...

---

Anton added a comment — 17th April 2010, 23:57:08:

Fabio Maulo updated NH-2174:
Priority: Minor (was: Critical)

How it can be minor issue priority?

---

Fabio Maulo added a comment — 27th July 2010, 13:43:20:

Because few users, in %, are using those ugly composite-id without, at leat, a class representing the POID.
What is critical for you does not mean that it is critical for the project, and the project here is NHibernate.

[apon81]

I have the same problem

[bahusoid]

I believe https://nhibernate.jira.com/browse/NH-1747 is the same core issue. Only fix was made for single column case. So lazy load fix for multicolumn case should be quite easy.

I'm pretty sure Linq/QueryOver fetch/join case is not fixed yet even for single column (and it seems it would require recent table group join feature to work properly).

The following issues looks related: #1227, #1260

[apon81]

In my case, the key is single column. This is my mappings:

  <class name="Base" table="Base">
    <id name="Id" type="Int64">
      <generator class="identity" />
    </id>
    <discriminator />
  </class>
  <class name="Link" table="Link">
    <id name="Id" type="Int64">
      <generator class="identity" />
    </id>
    <set name="Inherits" cascade="all">
      <key column="Link" />
      <one-to-many class="Inherit" />
    </set>
  </class>
  <subclass name="Inherit" extends="Base">
    <join table="Inherit">
      <key column="Id" />
      <many-to-one name="Link" />
      <property name="Str" />
    </join>
  </subclass>

I checked on the latest release 5.3.5 and the problem reproduced.
My criteria:

var cliteria = session.CreateCriteria<Link>().SetProjection(Projections.RootEntity());
cliteria.CreateCriteria(nameof(Link.Inherits)).Add(Restrictions.In(Projections.Id(), new[] { 1L }));
var result = cliteria.List();

and query:

SELECT this_.Id as id1_1_0_
FROM Link this_
inner join Base inherit1_ on this_.Id=inherit1_.Link
left outer join Inherit inherit1_1_ on inherit1_.Id=inherit1_1_.Id
WHERE inherit1_.Id in (@p0)

[bahusoid]

cliteria.CreateCriteria(nameof(Link.Inherits))

So your issue is different. This one about lazy loading. Lazy loading should work for you. Your issue is what I meant with:

I'm pretty sure Linq/QueryOver fetch case is not fixed yet even for single column (and it seems it would require recent table group join feature to work properly).

So fix is not straightforward for your case.

[bahusoid]

Lazy loading is fixed by #2675 and join/fetch is fixed by #2680