I am mapping a class hierarchy to a single table. One of the subclasses has a list associated with it which is stored in another table. Since the list is small and I need it all the time, I set it to lazy="false" and outer-join="true".

When I execute a query, if there are any subclasses in the database which are of the type that has the list, then I get multiple identical instances of those subclasses in my result, one for each item in the list.

If I turn off outer-join, then the problem does not occur.

This is my query:

Criteria criteria = mSession.createCriteria(Question.class);
criteria.add(Expression.eq("questionId.formId", formId));
criteria.addOrder(Order.asc("questionId.questionNumber"));
return criteria.list();

A manual check of the database using a SQL query tool shows that there are not duplicate rows in the database (which makes sense since the DB has unique indexes on the primary keys).


Hibernate version:
2.1

Mapping documents:
<hibernate-mapping>

<class name="com.pogo.besl.form.Question" table="FORM_QUESTION" discriminator-value="null"
dynamic-update="false" polymorphism="explicit">

<!-- Primary key -->
<composite-id name="questionId" class="com.pogo.besl.form.QuestionId">
<key-property name="formId" column="FORM_ID"/>
<key-property name="questionNumber" column="QUESTION_NUMBER"/>
</composite-id>

<!-- Discriminator -->
<discriminator column="QUESTION_TYPE" type="character" not-null="true"/>

<!-- Base class -->
<property name="text" column="LONG_TEXT" not-null="true"/>
<property name="abbreviated" column="SHORT_TEXT" not-null="true"/>
<property name="tag" column="TAG" not-null="true"/>
<property name="requiresResponse" column="MANDATORY" not-null="true"/>

<!-- Subclass QuestionLong -->
<subclass name="com.pogo.besl.form.QuestionLong" discriminator-value="L">
<property name="maxLength" column="MAX_RESPONSE_LENGTH"/>
<property name="inputWidth" column="INPUT_WIDTH"/>
<property name="inputHeight" column="INPUT_HEIGHT"/>

<!-- Subclass QuestionPredefined -->
<subclass name="com.pogo.besl.form.QuestionPredefined" discriminator-value="P">
<property name="type" type="com.pogo.serv.form.PredefinedQuestionTypeUserType" column="PREDEFINED_TYPE"/>
<property name="validate" column="PREDEFINED_VALIDATE"/>
</subclass>

</subclass>

<!-- Subclass QuestionShort -->
<subclass name="com.pogo.besl.form.QuestionShort" discriminator-value="S">
<property name="multiSelect" column="MULTISELECT"/>
<property name="dropDown" column="DROPDOWN"/>
<list name="choices" lazy="false" table="FORM_CHOICE" outer-join="true" cascade="all">
<key>
<column name="FORM_ID"/>
<column name="QUESTION_NUMBER"/>
</key>
<index column="CHOICE_NUMBER"/>
<composite-element class="com.pogo.besl.form.Choice">
<property name="longText" column="LONG_TEXT"/>
<property name="shortText" column="SHORT_TEXT"/>
</composite-element>
</list>
</subclass>




</class>

</hibernate-mapping>

Name and version of the database you are using:
Oracle

The generated SQL (show_sql=true):
Hibernate: select this.FORM_ID as FORM_ID0_, this.QUESTION_NUMBER as QUESTION2_0
_, this.QUESTION_TYPE as QUESTION3_0_, this.LONG_TEXT as LONG_TEXT0_, this.SHORT
_TEXT as SHORT_TEXT0_, this.TAG as TAG0_, this.MANDATORY as MANDATORY0_, this.MA
X_RESPONSE_LENGTH as MAX_RESP8_0_, this.INPUT_WIDTH as INPUT_WI9_0_, this.INPUT_
HEIGHT as INPUT_H10_0_, this.PREDEFINED_TYPE as PREDEFI11_0_, this.PREDEFINED_VA
LIDATE as PREDEFI12_0_, this.MULTISELECT as MULTISE13_0_, this.DROPDOWN as DROPD
OWN0_, choices1_.FORM_ID as FORM_ID__, choices1_.QUESTION_NUMBER as QUESTION2___
, choices1_.LONG_TEXT as LONG_TEXT__, choices1_.SHORT_TEXT as SHORT_TEXT__, choi
ces1_.CHOICE_NUMBER as CHOICE_N5___ from FORM_QUESTION this, FORM_CHOICE choices
1_ where this.FORM_ID=? and this.FORM_ID=choices1_.FORM_ID(+) and this.QUESTION_
NUMBER=choices1_.QUESTION_NUMBER(+) order by this.QUESTION_NUMBER asc

Debug level Hibernate log excerpt:

This is a FAQ.

Hi Gavin, thanks for the reply. I found the FAQ you refer to and fixed my code by removing the duplicates myself.

I understand how an outer join produces duplicate rows but I was a little suprised that Hibernate passes this through to the client. Are there applications where this is desirable? If nothing else, it seems like a bug that you get a different result from the same query depending on how the fetch mode is set. Would be nice to have Hibernate remove the duplicates before returning the list.

Well, i somewhat agree except that:

* back in the dim dark days when i last tried it out, removing duplicates was an expensive operation for Hibernate, and one that could be better optimized by the user

* it makes setMaxResults() behave incredibly unintuitively - can you imagine how many bug reports I would get on that....