Hi,

We have a problem when searching for Job objects in the database using hibernate.

When searching we do a join between the Job object (table) and the Customer object (table).

In our search method we have a number of criteria (

DetachedCriteria criteria = DetachedCriteria.forClass(JobImpl.class) )

and it works fine. The sql generated by hibernate shows that is an inner join beeing executed (Hibernate statement A below).

But if we add an criteria for the customer object map (DetachedCriteria customerCriteria = criteria.createCriteria("customers")) , then hibernates generates an extra select statement when running the search method(Hibernate statement A + Hibernate statement B is generated).

How can this happen ?

Has this to do with customer beeing a map or why is an extra select (statement B) being generated ?





Hibernate version:3.2.4 sp1

Mapping documents:
<class name="JobImpl" table="JOB" proxy="Job" mutable="false">

<id name="objectId" type="java.lang.Integer" column="OBJECT_ID" />

<property name="OrderId" type="java.lang.String" column="ORDER_REFERENCE_ID"/>

<map name="customers" lazy="false" fetch="join">

<key column="PARENT_ID" update="false" />

<index type="customerEnum" column="CUS_CUSTOMER_TYPE" length="40" />

<one-to-many class="CustomerImpl" />

</map>

</class>




Name and version of the database you are using: oracle 9i

The generated SQL (show_sql=true):
Hibernate statment A:

select

*

from

( select

this_.OBJECT_ID as OBJECT1_61_1_,

customerim1_.OBJECT_ID as OBJECT1_62_0_,

customerim1_.CUS_IDENTITY_NUMBER as CUS7_62_0_,

customerim1_.CUS_IDENTITY_PREFIX as CUS8_62_0_,

from

JOB this_

inner join

CUSTOMER customerim1_

on this_.OBJECT_ID=customerim1_.PARENT_ID

where

this_.JOB_ORDER_STATUS<>?

order by

customerim1_.CUS_IDENTITY_PREFIX asc,

customerim1_.CUS_IDENTITY_NUMBER asc )

where

rownum <= ?



Hibernate statement B:

select

customers0_.OBJECT_ID as OBJECT1_1_,

customers0_.CUS_IDENTITY_NUMBER as CUS7_62_0_,

customers0_.CUS_IDENTITY_PREFIX as CUS8_62_0_

from

CUSTOMER customers0_

where

customers0_.PARENT_ID=?