tracker issue : CF-3041862

Problem:left

The sql generator for hibernate is generating cross joins instead of a left joins, this is causing data to be omitted that would otherwise be returned.This is the sql that is generated note the cross join.Hibernate:     select        item0_.ID_ITEM as ID1_0_,        item0_.ACCOUNTABILITY_NO as ACCOUNTA2_0_,        item0_.MODEL_NO as MODEL3_0_,        item0_.NOMENCLATURE as NOMENCLA4_0_,        item0_.SERIAL_NO as SERIAL5_0_,        item0_.MAC_ADDRESS as MAC6_0_,        item0_.Z_CUSTODIAN as Z7_0_,        item0_.Z_BUILDING_NO as Z8_0_,        item0_.LOCATION as LOCATION0_,        item0_.Z_ISSUED_TO as Z10_0_,        item0_.NOTES as NOTES0_,        item0_.MANUFACTURER as MANUFAC12_0_,        item0_.COMPUTER_NAME as COMPUTER13_0_,        item0_.INACTIVE_FLAG as INACTIVE14_0_,        item0_.DATE_PURCHASED as DATE15_0_,        item0_.IP as IP0_,        item0_.LAST_SEEN as LAST17_0_,        item0_.NEXT_PING_DUE as NEXT18_0_,        item0_.IS_ACTIVE as IS19_0_,        item0_.CREATED_BY as CREATED20_0_,        item0_.WHEN_CREATED as WHEN21_0_,        item0_.UPDATED_BY as UPDATED22_0_,        item0_.WHEN_UPDATED as WHEN23_0_,        item0_.NW_SYSTEM_ID as NW24_0_,        item0_.NW_PRIMARY_POC as NW25_0_,        item0_.NW_SYSTEM_ADMIN as NW26_0_,        item0_.NW_MANUFACTURER as NW27_0_,        item0_.NW_MODEL as NW28_0_,        item0_.NW_SERIAL_NUMBER as NW29_0_,        item0_.NW_HOST_NAME as NW30_0_,        item0_.NW_LAST_UPDATE as NW31_0_,        item0_.ISSUED_TO_ID as ISSUED32_0_,        item0_.CUSTODIAN_ID as CUSTODIAN33_0_,        item0_.BUILDING_ID as BUILDING34_0_     from        ITEM item0_ cross     join        person person1_ cross     join        person person2_     where        item0_.ISSUED_TO_ID=person1_.ID_PERSON         and item0_.CUSTODIAN_ID=person2_.ID_PERSON         and (            item0_.ACCOUNTABILITY_NO like 'abc%'             or item0_.SERIAL_NO like 'abc%'             or item0_.COMPUTER_NAME like 'abc%'             or item0_.NW_PRIMARY_POC like '%abc%'             or item0_.NW_SYSTEM_ADMIN like '%abc%'             or item0_.NW_SERIAL_NUMBER like '%abc%'             or person1_.NAME like '%abc%'             or person2_.NAME like '%abc%'        )
Method:

here are the hibernate hbmxml filesITEM.hbmxml<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"                                   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"><hibernate-mapping>    <class entity-name="item" lazy="true" name="cfc:GAITS.cfc.item" table="ITEM">        <id name="id_item" type="int">            <column length="10" name="ID_ITEM"/>            <generator class="identity"/>        </id>        <property name="accountability_no" type="string">            <column name="ACCOUNTABILITY_NO" not-null="true"/>        </property>        <property name="model_no" type="string">            <column name="MODEL_NO"/>        </property>        <property name="nomenclature" type="string">            <column name="NOMENCLATURE"/>        </property>        <property name="serial_no" type="string">            <column name="SERIAL_NO"/>        </property>        <property name="mac_address" type="string">            <column name="MAC_ADDRESS"/>        </property>        <property name="z_custodian" type="string">            <column name="Z_CUSTODIAN"/>        </property>        <property name="z_building_no" type="string">            <column name="Z_BUILDING_NO"/>        </property>        <property name="location" type="string">            <column name="LOCATION"/>        </property>        <property name="z_issued_to" type="string">            <column name="Z_ISSUED_TO"/>        </property>        <property name="notes" type="string">            <column name="NOTES"/>        </property>        <property name="manufacturer" type="string">            <column name="MANUFACTURER"/>        </property>        <property name="computer_name" type="string">            <column name="COMPUTER_NAME"/>        </property>        <property name="inactive_flag" type="string">            <column name="INACTIVE_FLAG" not-null="true"/>        </property>        <property name="date_purchased" type="timestamp">            <column name="DATE_PURCHASED"/>        </property>        <property name="ip" type="string">            <column name="IP"/>        </property>        <property name="last_seen" type="timestamp">            <column name="LAST_SEEN"/>        </property>        <property name="next_ping_due" type="timestamp">            <column name="NEXT_PING_DUE"/>        </property>        <property name="is_active" type="boolean">            <column name="IS_ACTIVE"/>        </property>        <property name="created_by" type="string">            <column name="CREATED_BY"/>        </property>        <property name="when_created" type="timestamp">            <column name="WHEN_CREATED"/>        </property>        <property name="updated_by" type="string">            <column name="UPDATED_BY"/>        </property>        <property name="when_updated" type="timestamp">            <column name="WHEN_UPDATED"/>        </property>        <property name="nw_system_id" type="integer">            <column name="NW_SYSTEM_ID"/>        </property>        <property name="nw_primary_poc" type="string">            <column name="NW_PRIMARY_POC"/>        </property>        <property name="nw_system_admin" type="string">            <column name="NW_SYSTEM_ADMIN"/>        </property>        <property name="nw_manufacturer" type="string">            <column name="NW_MANUFACTURER"/>        </property>        <property name="nw_model" type="string">            <column name="NW_MODEL"/>        </property>        <property name="nw_serial_number" type="string">            <column name="NW_SERIAL_NUMBER"/>        </property>        <property name="nw_host_name" type="string">            <column name="NW_HOST_NAME"/>        </property>        <property name="nw_last_update" type="timestamp">            <column name="NW_LAST_UPDATE"/>        </property>        <many-to-one class="cfc:GAITS.cfc.person" column="ISSUED_TO_ID" name="issued_to" fetch="join" not-found="ignore"/>        <many-to-one class="cfc:GAITS.cfc.person" column="CUSTODIAN_ID" name="custodian" fetch="join" not-found="ignore"/>        <many-to-one class="cfc:GAITS.cfc.building" column="BUILDING_ID" name="building" fetch="join" not-found="ignore"/>        <bag name="mac_addresses" inverse="true">            <key column="ITEM_ID"/>            <one-to-many class="cfc:GAITS.cfc.item_MAC"/>        </bag>        <bag name="transfers" inverse="true">            <key column="ITEM_ID"/>            <one-to-many class="cfc:GAITS.cfc.transfer"/>        </bag>    </class></hibernate-mapping>Person.hbmxml<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"                                   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"><hibernate-mapping>    <class entity-name="person" lazy="true" name="cfc:GAITS.cfc.person" table="person">        <id name="id_person" type="int">            <column length="10" name="ID_PERSON"/>            <generator class="identity"/>        </id>        <property name="name" type="string">            <column name="NAME"/>        </property>        <property name="is_custodian" type="boolean">            <column name="IS_CUSTODIAN" not-null="true"/>        </property>        <property name="email" type="string">            <column name="EMAIL"/>        </property>        <property name="work_phone" type="string">            <column name="WORK_PHONE"/>        </property>        <property name="mobile_phone" type="string">            <column name="MOBILE_PHONE"/>        </property>        <property name="code" type="string">            <column name="CODE"/>        </property>        <many-to-one class="cfc:GAITS.cfc.building" column="BUILDING_ID" name="building" not-found="ignore"/>        <bag name="items_custodian_for" inverse="true">            <key column="CUSTODIAN_ID" not-null="true"/>            <one-to-many class="cfc:GAITS.cfc.item"/>        </bag>        <bag name="items_issued_to" inverse="true">            <key column="ISSUED_TO_ID" not-null="true"/>            <one-to-many class="cfc:GAITS.cfc.item"/>        </bag>        <bag name="transfers_custodian_for" inverse="true">            <key column="CUSTODIAN_ID" not-null="true"/>            <one-to-many class="cfc:GAITS.cfc.transfer"/>        </bag>        <bag name="transfers_issued_to" inverse="true">            <key column="ISSUED_TO_ID" not-null="true"/>            <one-to-many class="cfc:GAITS.cfc.transfer"/>        </bag>        <bag name="previous_transfers_custodian_for" inverse="true">            <key column="PREV_CUSTODIAN_ID"/>            <one-to-many class="cfc:GAITS.cfc.transfer"/>        </bag>        <bag name="previous_transfers_issued_to" inverse="true">            <key column="PREV_ISSUED_TO_ID"/>            <one-to-many class="cfc:GAITS.cfc.transfer"/>        </bag>    </class></hibernate-mapping>item.cfccomponent persistent="true" {property name="id_item" fieldtype="id" generator="identity";property name="accountability_no" ormtype="string" type="string";property name="model_no" ormtype="string" type="string";property name="nomenclature" ormtype="string" type="string";property name="serial_no" ormtype="string" type="string";property name="mac_address" ormtype="string" type="string";//property name="z_custodian" ormtype="string" type="string";//property name="z_building_no" ormtype="string" type="string";property name="location" ormtype="string" type="string";//property name="z_issued_to" ormtype="string" type="string";property name="notes" ormtype="string" type="string";property name="manufacturer" ormtype="string" type="string";property name="computer_name" ormtype="string" type="string";property name="inactive_flag" ormtype="string" type="string";property name="date_purchased" ormtype="timestamp" type="date";property name="ip" ormtype="string" type="string";property name="last_seen" ormtype="timestamp" type="date";property name="next_ping_due" ormtype="timestamp" type="date";property name="is_active" ormtype="boolean" type="boolean";property name="created_by" ormtype="string" type="string";property name="when_created" ormtype="timestamp" type="date";property name="updated_by" ormtype="string" type="string";property name="when_updated" ormtype="timestamp" type="date";property name="nw_system_id" ormtype="integer" type="numeric";property name="nw_primary_poc" ormtype="string" type="string";property name="nw_system_admin" ormtype="string" type="string";property name="nw_manufacturer" ormtype="string" type="string";property name="nw_model" ormtype="string" type="string";property name="nw_serial_number" ormtype="string" type="string";property name="nw_host_name" ormtype="string" type="string";property name="nw_last_update" ormtype="timestamp" type="date";property name="issued_to" column="ISSUED_TO_ID" ormtype="integer" fieldtype="many-to-one" cfc="person" fkColumn="ID_PERSON" fetch="join" remotingfetch="true" missingRowIgnored="true";property name="custodian" column="CUSTODIAN_ID"ormtype="integer" fieldtype="many-to-one" cfc="person" fkColumn="ID_PERSON" fetch="join" remotingfetch="true" missingRowIgnored="true";property name="building" column="BUILDING_ID" ormtype="integer" fieldtype="many-to-one" cfc="building" fkColumn="ID_BUILDING" fetch="join" remotingfetch="true" missingRowIgnored="true";property name="mac_addresses" fieldtype="one-to-many" cfc="item_mac" fkColumn="ITEM_ID" inverse="true";property name="transfers" fieldtype="one-to-many" cfc="transfer" fkColumn="ITEM_ID" inverse="true";public string function getIDName() {var id = StructFindValue( GetMetaData(This), "id")[1].owner.name;return id;}public any function getIDValue() {return variables[getIDName()];}public void function setIDValue(any idvalue) {variables[getIDName()] = arguments.idvalue;}public void function nullifyZeroID() {if (getIDValue() eq 0){variables[getIDName()] = JavaCast("Null", "");}}}person.cfccomponent persistent="true" {property name="id_person" fieldtype="id" generator="identity";property name="name" ormtype="string" type="string";property name="is_custodian" ormtype="boolean" type="boolean";property name="email" ormtype="string" type="string";property name="work_phone" ormtype="string" type="string";property name="mobile_phone" ormtype="string" type="string";property name="code" ormtype="string" type="string";property name="building" column="BUILDING_ID" ormtype="integer" fieldtype="many-to-one" cfc="building" fkColumn="ID_BUILDING" missingRowIgnored="true" remotingfetch="true";property name="items_custodian_for" fieldtype="one-to-many" cfc="item" fkColumn="CUSTODIAN_ID" inverse="true";property name="items_issued_to" fieldtype="one-to-many" cfc="item" fkColumn="ISSUED_TO_ID" inverse="true";property name="transfers_custodian_for" fieldtype="one-to-many" cfc="transfer" fkColumn="CUSTODIAN_ID" inverse="true";property name="transfers_issued_to" fieldtype="one-to-many" cfc="transfer" fkColumn="ISSUED_TO_ID" inverse="true";property name="previous_transfers_custodian_for" fieldtype="one-to-many" cfc="transfer" fkColumn="PREV_CUSTODIAN_ID" inverse="true";property name="previous_transfers_issued_to" fieldtype="one-to-many" cfc="transfer" fkColumn="PREV_ISSUED_TO_ID" inverse="true";public string function getIDName() {var id = StructFindValue( GetMetaData(This), "id")[1].owner.name;return id;}public any function getIDValue() {return variables[getIDName()];}public void function setIDValue(any idvalue) {variables[getIDName()] = arguments.idvalue;}public void function nullifyZeroID() {if (getIDValue() eq 0){variables[getIDName()] = JavaCast("Null", "");}}}original query from an other cfcremote GAITS.cfc.item[] function search(string q) {var hqlString = "FROM item ";var whereClause = "";if (len(arguments.q) gt 0){whereClause  = ListAppend(whereClause, " accountability_no LIKE '#arguments.q#%'", "|");   //whereClause  = ListAppend(whereClause, " model_no LIKE '%#arguments.q#%'", "|");   //whereClause  = ListAppend(whereClause, " nomenclature LIKE '%#arguments.q#%'", "|");   whereClause  = ListAppend(whereClause, " serial_no LIKE '#arguments.q#%'", "|");   //whereClause  = ListAppend(whereClause, " mac_address LIKE '%#arguments.q#%'", "|");   //whereClause  = ListAppend(whereClause, " location LIKE '%#arguments.q#%'", "|");   //whereClause  = ListAppend(whereClause, " notes LIKE '%#arguments.q#%'", "|");   //whereClause  = ListAppend(whereClause, " manufacturer LIKE '%#arguments.q#%'", "|");   whereClause  = ListAppend(whereClause, " computer_name LIKE '#arguments.q#%'", "|");   //whereClause  = ListAppend(whereClause, " inactive_flag LIKE '%#arguments.q#%'", "|");   //whereClause  = ListAppend(whereClause, " created_by LIKE '%#arguments.q#%'", "|");   //whereClause  = ListAppend(whereClause, " updated_by LIKE '%#arguments.q#%'", "|");   whereClause  = ListAppend(whereClause, " nw_primary_poc LIKE '%#arguments.q#%'", "|");   whereClause  = ListAppend(whereClause, " nw_system_admin LIKE '%#arguments.q#%'", "|");   //whereClause  = ListAppend(whereClause, " nw_manufacturer LIKE '%#arguments.q#%'", "|");//whereClause  = ListAppend(whereClause, " nw_model LIKE '%#arguments.q#%'", "|");whereClause  = ListAppend(whereClause, " nw_serial_number LIKE '%#arguments.q#%'", "|");whereClause  = ListAppend(whereClause, " issued_to.name LIKE '%#arguments.q#%'", "|");whereClause  = ListAppend(whereClause, " custodian.name LIKE '%#arguments.q#%'", "|");whereClause = Replace(whereClause, "|", " OR ", "all");}if (len(whereClause) gt 0){hqlString = hqlString & " WHERE " & whereClause;}return ormExecuteQuery(hqlString);}
Result:

Hibernate SQL generator is  generating faulty sql

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3041862

External Customer Info:
External Company:  
External Customer Name: garry watkins
External Customer Email: 44AA11BE4624E368992015A8
External Test Config: 08/09/2010

Attachments:

Comments: