Hi there I am experiencing something strage when a try to join two tables referenced by a composite foreign key: here are the mappings:

<class name="bbc.monitoring.dynamite.Episode" table="episode">
<id column="pid" name="pid" type="java.lang.String" />
<property column="play_version_id" name="playVersionId" type="java.lang.String" />
<property column="title" name="title" type="java.lang.String" />
<property column="media_type" name="mediaType" type="java.lang.String" />
<property column="master_brand" name="masterBrand" type="java.lang.String" />
<property column="is_critical" name="isCritical" type="boolean" />
<property column="is_available" name="isAvailable" type="boolean" />
<property column="start_time" name="startTime" type="java.sql.Timestamp" />
<set name="ondemands" table="ondemands">
<key column="FK_pid" />
<element column="oid" type="java.lang.String" />
</set>
<set name="media" table="media" lazy="false" cascade="all" inverse="true">
<key column="FK_pid" />
<one-to-many class="bbc.monitoring.mediaselector.Media" />
</set>
</class>


<class name="bbc.monitoring.mediaselector.Media" table="media" dynamic-insert="true" dynamic-update="true">
<composite-id name="MediaPK" class="bbc.monitoring.mediaselector.MediaPK">
<key-property column="service" name="service" type="java.lang.String" />
<key-property column="FK_pid" name="pid" type="java.lang.String" />
</composite-id>
<property column="type" name="type" type="java.lang.String" />
<property column="expires" name="mediaExpire" type="java.sql.Timestamp" />
<set name="connection" table="connection" lazy="false" cascade="all" inverse="true">
<key>
<column name="media_service" />
<column name="media_FK_pid" />
</key>
<one-to-many class="bbc.monitoring.mediaselector.Connection" />
</set>
<many-to-one name="episode" class="bbc.monitoring.dynamite.Episode" not-null="true" insert="false" update="false">
<column name="FK_pid"></column>
</many-to-one>
</class>


<class name="bbc.monitoring.mediaselector.Connection" table="connection"
dynamic-insert="true"
dynamic-update="true">
<composite-id name="ConnectionPK">
<key-property column="identifier" name="identifier" type="java.lang.String" />
<key-property column="media_service" name="mediaService" type="java.lang.String" />
</composite-id>
<property column="href" name="href" type="java.lang.String" />
<property column="protocol" name="protocol" type="java.lang.String" />
<property column="kind" name="kind" type="java.lang.String" />
<property column="originServerURI" name="originServerURI" type="java.lang.String" />
<property column="responseCode" name="responseCode" type="integer" />
<property column="lastTimeFetched" name="lastTimeFetched" type="java.sql.Timestamp" />
<!-- need to add media_FK_pid as the many-to-one states that insert and update is false -->
<property column="media_FK_pid" name="episodePid" type="java.lang.String" />
<many-to-one name="media" class="bbc.monitoring.mediaselector.Media" not-null="true" insert="false" update="false">
<column name="media_FK_pid"></column>
<column name="media_service"></column>
</many-to-one>
</class>

this is the hql query:

Query query = session.createQuery("select ep from Episode ep join ep.media m join m.connection c where c.responseCode = 404 ");

this is the output of hibernate:

select episode0_.pid as pid0_, episode0_.play_version_id as play2_0_, episode0_.title as title0_, episode0_.media_type as media4_0_, episode0_.master_brand as master5_0_,
episode0_.is_critical as is6_0_, episode0_.is_available as is7_0_, episode0_.start_time as start8_0_ from episode episode0_ inner join media media1_ on episode0_.pid=media1_.FK_pid inner
join connection connection2_ on media1_.service=connection2_.media_FK_pid and media1_.FK_pid=connection2_.media_service where connection2_.responseCode=404

which does not work but if I manually map correctly the join :

media1_.service=connection2_.media_service and media1_.FK_pid=connection2_.media_FK_pid

it does work.
has someone come across similar behaviour?


kind regards

coco