NHCD-39 - JetDriver: session.Refresh(...) -> wrong SQL-string ('join', 'where' and 'and' in wrong order).

[nhibernate-bot]

Martin Gämperle created issue - 20/Sep/10 1:28 PM

Class hierarchy: 'B1' and 'B2' inherit from 'BBase'. 'A' has a list of 'B1's and a list of 'B2's as properties.

Mapping-file of A:

<class name="A" table="A" dynamic-update="true" lazy="false">
  <id name="SID" type="Int32" column="SID" unsaved-value="0">
    <generator class="hilo">
      <param name="max_lo">100</param>
    </generator>
  </id>
  <bag name="B1s" inverse="true" lazy="true" cascade="all-delete-orphan" batch-size="50">
    <key column="FK_A_SID"/>
    <one-to-many class="B1"/>
  </bag>
  <bag name="B2s" inverse="true" lazy="true" ascade="all-delete-orphan" batch-size="50">
    <key column="FK_A_SID"/>
    <one-to-many class="B2"/>
  </bag>
</class>

Mapping-file of BBase:

<class name="BBase" table="BBASE" discriminator-value="-1" dynamic-update="true" lazy="false">
  <id name="SID" type="Int32" column="SID" unsaved-value="0">
    <generator class="hilo">
    <param name="max_lo">100</param>
    </generator>
  </id>
  <discriminator column="TYPE" type="Int32" force="true"/>
  <many-to-one name="A" column="FK_A_SID" class="A" not-null="true"/>

  <!-- B1 -->
  <subclass name="B1" discriminator-value="0" lazy="false">
    ...
  </subclass>

  <!-- B2 -->
  <subclass name="B2" discriminator-value="1" lazy="false">
    ...
  </subclass>
</class>

When 'session.Refresh(A)' is called it produces an SQL-string that looks like this:
SELECT a.SID as ..., ..., b1s.FK_A_SID as ..., b1s.SID as ..., ... FROM A a LEFT OUTER JOIN BBASE b1s ON a.SID=b1s.FK_A_SID AND b1s.TYPE=0 WHERE a.SID=?

This SQL-string leads to an error. When I manually switch the 'AND' and 'WHERE' statements then it works:
SELECT a.SID as ..., ..., b1s.FK_A_SID as ..., b1s.SID as ..., ... FROM A a LEFT OUTER JOIN BBASE b1s ON a.SID=b1s.FK_A_SID WHERE a.SID=? AND b1s.TYPE=0

Remark: This happens only for the first bag in 'A's mapping-file.

---

Mark Junker added a comment - 21/Oct/11 8:17 PM

Using additional conditions in the JOIN .. ON .. clause is common practice in modern RDBMS and the SQL would execute just fine in those environments. I will take a look at this problem when I get to the point where I need sublcasses too (which may take some months).