Hibernate version:3.22

I have a object relation like this:

|InstrumentHeader|1--*|InstrumentAlernateId|*--1|InstrumentNumberScheme|

Part of the Mapping Files:

InstrumentHeaderDTO:
<hibernate-mapping>
<class
name="com.ubs.arte.common.model.financialinstrument.InstrumentHeaderDTO"
table="BHF_V_FINSTR_HEADER"
mutable="false"
lazy="false">

...

<set
name="alternateIds"
inverse="false"
sort="unsorted"
lazy="false"
batch-size="4"
mutable="false">
<key column="INSTRUMENT_ID"/>
<one-to-many class="com.ubs.arte.common.model.financialinstrument.InstrumentAlternateIdDTO" />
</set>

...
</class>
</hibernate-mapping>

InstrumentAlternateIdDTO:
<hibernate-mapping
auto-import="false">
<class
name="com.ubs.arte.common.model.financialinstrument.InstrumentAlternateIdDTO"
table="BHF_V_FINSTR_ALTERNATE_ID"
lazy="false">

...

<many-to-one
lazy="false"
name="instrumentNumberScheme"
class="com.ubs.arte.common.model.referencedata.generic.InstrumentNumberSchemeDTO"
column="INSTR_NUMBER_SCHEME_ID"
not-null="true"
fetch="select"
/>

...

</class>
</hibernate-mapping>

InstrumentNumberSchemeDTO:
<hibernate-mapping>
<class
name="com.ubs.arte.common.model.referencedata.generic.GenericReferenceDataDTO"
table="BHF_V_GENERIC" lazy="false">

...

<property name="code" type="java.lang.String">
<column name="CODE" not-null="true"/>

</property>

<property name="mnemonic" type="java.lang.String" column="MNEMONIC"/>

...

<subclass
name="com.ubs.arte.common.model.referencedata.generic.InstrumentNumberSchemeDTO"
discriminator-value="InstrumentID" lazy="false"/>

...

</class>
</hibernate-mapping>


I did a criteria query:

public List findAlternateIds(InstrumentAltInstrumentSearchCriteria aSearchCriteria) {
if (aSearchCriteria == null) {
return Collections.EMPTY_LIST;
}

Session session = getSession();

Criteria criteria = session.createCriteria(InstrumentHeaderDTO.class);

String alternateId = "AL";
criteria.createAlias(InstrumentHeaderDTO.PN_ALTERNATE_IDS, alternateId, CriteriaSpecification.LEFT_JOIN);

// instrument number scheme
if (!ArteUtil.isNull(aSearchCriteria.getInstrumentNumberScheme())) {
criteria.add(Restrictions.eq(alternateId + "." + InstrumentAlternateIdDTO.PN_INSTRUMENT_NUMBER_SCHEME,
aSearchCriteria.getInstrumentNumberScheme()));
}

Disjunction disjunction = Restrictions.disjunction();
// instrument alternate id
if (!StringUtil.isEmpty(aSearchCriteria.getId())) {
disjunction.add(Restrictions
.like(alternateId + "." + InstrumentAlternateIdDTO.PN_INSTRUMENT_ALTERNATE_ID,
aSearchCriteria.getId(), MatchMode.START).ignoreCase());

}

// use the same id in the name
if (!StringUtil.isEmpty(aSearchCriteria.getId())) {
disjunction.add(Restrictions.like(InstrumentHeaderDTO.PN_NAME_SHORT, aSearchCriteria.getId(),
MatchMode.ANYWHERE).ignoreCase());

}
criteria.add(disjunction);

// ......................................................................
return criteria.list();

}

This results in a SQL statement like this:

select this_.ID as ID63_1_, ...
from BHF_V_FINSTR_HEADER this_, BHF_V_FINSTR_ALTERNATE_ID al1_
where this_.ID=al1_.INSTRUMENT_ID(+) and al1_.INSTR_NUMBER_SCHEME_ID=?
and (lower(al1_.INSTRUMENT_ALTERNATE_ID) like ? or lower(this_.NAME_SHORT) like ?)


My problem now is the following:

1. case) When I do the query with an InstrumentNumberScheme and an InstrumentAlternateId I get one row in the 'result set'
and my InstrumentHeader has only one InstrumentAlternateId (the selected) in the set although there are
6 InstrumentAlternateId objects in the database.

2. case) When I do the query with the name only I get 6 rows in the 'result set'
and my InstrumentHeader has 6 InstrumentAlternateId objects (the selected).

So in the first case not the complete set was loaded from hibernate! It seems that hibernate uses the 'result set'
to fill the set and doesn't reload it properly.

Does anyone know this problem is it my mistake or is it a hibernate bug?

Thanks very much in advance!

I have had this exact same problem with Hibernate 3.2. For me it appeared to be directly related to using a LEFT_JOIN as opposed to a normal one, combined with a condition. It does appear as if the result of the query is cached somewhere despite my query and second level caches being turned off and this then used to give the wrong result later on.

The only 'fix' I found was to abandon the Criteria API and write the query using HQL instead. Criteria complicate things by fetching all columns for all rows referenced in the query, so Hibernate has all the data with which to create the wrong answer. Whereas HQL allows a join without fetching the joined tables, so the related collection data is not retrieved and it is not possible for Hibernate to wrongly retain the values.