[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1348) Join w/ Foreign Key within a Single Object not functioing

Join w/ Foreign Key within a Single Object not functioing --------------------------------------------------------- Key: HHH-1348 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1348 Project: Hibernate3 Type: Bug

Environment: Webshpere 5.0 Reporter: Michael Lambert

I have a legacy database I need to join into using something other than the main objects primary key and it simply wont let me join on any field other than the primary key. No matter what I insert into "property-ref" it ALWAYS uses the objects primary key in the inner join: <?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping package="com.dcx.ps.gpsis.ibsc.data.entities" default-lazy="false"> <class name="HPerson" table="PIBPERSN"> <cache usage="read-write" /> <id name="id" column="I_PERSN_IBSC" type="long"> <generator class="increment"/> </id> <timestamp name="lastUpdated" column="T_STMP_UPD" /> <property name="lastUpdatedBy" type="string" column="I_USER_UPD" not-null="true" /> <property name="tid" column="I_USER_LDAP" type="string" not-null="true" unique="true" /> <property name="description" column="N_USER_LDAP" type="string" not-null="true"/> <set name="positions" inverse="true" cascade="save-update"> <cache usage="read-write" /> <key column="I_PERSN_IBSC" /> <one-to-many class="HPosition" /> </set> <join table="PUSRPRF" inverse="true"> <key column="I_USER_LDAP" property-ref="tid" /> <property name="firstName" type="string" column="N_FIRST" /> <property name="lastName" type="string" column="N_LAST" /> </join> </class> <query name="findPersonsByName"> <![CDATA[ from HPerson person where person.firstName like :firstname and person.lastName like :lastname ]]> </query> <query name="findPersonsByFirstName"> <![CDATA[ from HPerson person where person.firstName like :firstname ]]> </query> <query name="findPersonsByLastName"> <![CDATA[ from HPerson person where person.lastName like :lastname ]]> </query> <query name="findPersonByTid"> <![CDATA[ from HPerson person where person.tid like :tid ]]> </query> <query name="findPersons"> <![CDATA[ from HPerson ]]> </query> </hibernate-mapping> Results in the following query and error when I execute any query (in this case findPersonByTid has been run): could not execute query: [select hperson0_.I_PERSN_IBSC as I1_4_, hperson0_.T_STMP_UPD as T2_4_, hperson0_.I_USER_UPD as I3_4_, hperson0_.I_USER_LDAP as I4_4_, hperson0_.N_USER_LDAP as N5_4_, hperson0_1_.N_FIRST as N2_5_, hperson0_1_.N_LAST as N3_5_ from T5279KN.PIBPERSN hperson0_ left outer join T5279KN.PUSRPRF hperson0_1_ on hperson0_.I_PERSN_IBSC=hperson0_1_.I_USER_LDAP where hperson0_.I_USER_LDAP like ?] COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/6000] SQL0401N The data types of the operands for the operation "=" are not compatible. SQLSTATE=42818 Where the outter join SHOULD be hperson0_.I_USER_LDAP =hperson0_1_.I_USER_LDAP; I_PERSN_IBSC is the primary key.