Hibernate version: 2.1.4

Mapping documents:

<hibernate-mapping package="pallas.crossroads.data.bean">
<class name="FunctionalArea" table="FUNCTIONAL_AREA">
<id
column="FUNCTIONAL_AREA_ID"
name="id"
type="integer"
>
<generator class="sequence">
<param name="sequence">FUNCTIONAL_AREA_ID</param>
</generator>
</id>
<property
column="DELETED"
length="1"
name="deleted"
not-null="false"
type="boolean"
/>
<property
column="CAN_DELETE"
length="1"
name="canDelete"
not-null="true"
type="boolean"
/>
<property
column="EDIT_DATE"
length="7"
name="editDate"
not-null="false"
type="date"
/>
<property
column="CAN_DISPLAY"
length="1"
name="canDisplay"
not-null="true"
type="boolean"
/>
<property
column="NAME"
length="50"
name="name"
not-null="true"
type="string"
/>
<property
column="CREATE_USER"
length="20"
name="createUser"
not-null="false"
type="string"
/>
<property
column="CAN_UPDATE"
length="1"
name="canUpdate"
not-null="true"
type="boolean"
/>
<property
column="DESCRIPTION"
length="1000"
name="description"
not-null="true"
type="string"
/>
<property
column="CODE"
length="20"
name="code"
not-null="true"
type="string"
/>
<property
column="EDIT_USER"
length="20"
name="editUser"
not-null="false"
type="string"
/>
<property
column="CREATE_DATE"
length="7"
name="createDate"
not-null="false"
type="date"
/>
<property
column="CAN_ADD"
length="1"
name="canAdd"
not-null="true"
type="boolean"
/>

<many-to-one
class="Type"
name="functionalAreaType"
not-null="true"
>
<column name="FUNCTIONAL_AREA_TYPE_ID" />
</many-to-one>

<set
name="securityGroupSet"
inverse="true"
lazy="false"
where="DELETED=0" >
<key column="FUNCTIONAL_AREA_ID"/>
<one-to-many class="SecGroupFunctionalArea"/>
</set>

</class>
</hibernate-mapping>

<hibernate-mapping package="pallas.crossroads.data.bean">
<class
name="SecurityGroup"
table="SECURITY_GROUP"
>
<id
name="id"
type="integer"
column="SECURITY_GROUP_ID"
>
<generator class="assigned"/>
</id>

<property
name="deleted"
column="DELETED"
type="boolean"
not-null="true"
length="1"
/>
<property
name="code"
column="CODE"
type="string"
not-null="true"
length="20"
/>
<property
name="name"
column="NAME"
type="string"
not-null="true"
length="20"
/>
<set
name="functionalAreaSet"
inverse="true"
lazy="false"
where="DELETED=0"
cascade="save-update"
>
<key column="SECURITY_GROUP_ID"/>
<one-to-many class="SecGroupFunctionalArea"/>
</set>
</class>
</hibernate-mapping>


<hibernate-mapping package="pallas.crossroads.data.bean">
<class name="SecGroupFunctionalArea" table="SEC_GROUP_FUNCTIONAL_AREA">
<id
column="SEC_GROUP_FUNCTIONAL_AREA_ID"
name="id"
type="integer"
>
<generator class="sequence">
<param name="sequence">SEC_GROUP_FUNCTIONAL_AREA_ID</param>
</generator>
</id>
<property
column="DELETED"
length="1"
name="deleted"
not-null="false"
type="boolean"
/>
<property
column="EDIT_DATE"
length="7"
name="editDate"
not-null="true"
type="date"
/>
<property
column="ALLOW_DELETE"
length="1"
name="allowDelete"
not-null="true"
type="boolean"
/>
<property
column="ALLOW_ADD"
length="1"
name="allowAdd"
not-null="true"
type="boolean"
/>
<property
column="CREATE_USER"
length="20"
name="createUser"
not-null="false"
type="string"
/>
<property
column="EDIT_USER"
length="20"
name="editUser"
not-null="false"
type="string"
/>
<property
column="ALLOW_DISPLAY"
length="1"
name="allowDisplay"
not-null="true"
type="boolean"
/>
<property
column="CREATE_DATE"
length="7"
name="createDate"
not-null="false"
type="date"
/>
<property
column="ALLOW_UPDATE"
length="1"
name="allowUpdate"
not-null="true"
type="boolean"
/>

<many-to-one
class="SecurityGroup"
name="securityGroup"
not-null="true"
>
<column name="SECURITY_GROUP_ID" />
</many-to-one>
<many-to-one
class="FunctionalArea"
name="functionalArea"
not-null="true"
>
<column name="FUNCTIONAL_AREA_ID" />
</many-to-one>
</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
List l = s.find(query);
Iterator itr = l.iterator();
while(itr.hasNext()){
System.out.println(itr.next());
}

Full stack trace of any exception that occurs:
N/A

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

The generated SQL (show_sql=true):

14:53:47,254 DEBUG SQL:237 - select functional0_.FUNCTIONAL_AREA_ID as FUNCTION1_0_, securitygr1_.SEC_GROUP_FUNCTIONAL_AREA_ID as SEC_GROU1_1_, functional0_.DELETED as DELETED0_, functional0_.CAN_DELETE as CAN_DELETE0_, functional0_.EDIT_DATE as EDIT_DATE0_, functional0_.CAN_DISPLAY as CAN_DISP5_0_, functional0_.NAME as NAME0_, functional0_.CREATE_USER as CREATE_U7_0_, functional0_.CAN_UPDATE as CAN_UPDATE0_, functional0_.DESCRIPTION as DESCRIPT9_0_, functional0_.CODE as CODE0_, functional0_.EDIT_USER as EDIT_USER0_, functional0_.CREATE_DATE as CREATE_12_0_, functional0_.CAN_ADD as CAN_ADD0_, functional0_.FUNCTIONAL_AREA_TYPE_ID as FUNCTIO14_0_, securitygr1_.DELETED as DELETED1_, securitygr1_.EDIT_DATE as EDIT_DATE1_, securitygr1_.ALLOW_DELETE as ALLOW_DE4_1_, securitygr1_.ALLOW_ADD as ALLOW_ADD1_, securitygr1_.CREATE_USER as CREATE_U6_1_, securitygr1_.EDIT_USER as EDIT_USER1_, securitygr1_.ALLOW_DISPLAY as ALLOW_DI8_1_, securitygr1_.CREATE_DATE as CREATE_D9_1_, securitygr1_.ALLOW_UPDATE as ALLOW_U10_1_, securitygr1_.SECURITY_GROUP_ID as SECURIT11_1_, securitygr1_.FUNCTIONAL_AREA_ID as FUNCTIO12_1_ from FUNCTIONAL_AREA functional0_ left outer join SEC_GROUP_FUNCTIONAL_AREA securitygr1_ on functional0_.FUNCTIONAL_AREA_ID=securitygr1_.FUNCTIONAL_AREA_ID where securitygr1_.DELETED=0

14:53:47,494 DEBUG SQL:237 - select type0_.TYPE_ID as TYPE_ID1_, type0_.DATA as DATA1_, type0_.DELETED as DELETED1_, type0_.EDIT_DATE as EDIT_DATE1_, type0_.CODE as CODE1_, type0_.NAME as NAME1_, type0_.CREATE_USER as CREATE_U7_1_, type0_.DESCRIPTION as DESCRIPT8_1_, type0_.EDIT_USER as EDIT_USER1_, type0_.SORT_ORDER as SORT_ORDER1_, type0_.CREATE_DATE as CREATE_11_1_, type0_.CATEGORY_ID as CATEGOR12_1_, category1_.CATEGORY_ID as CATEGORY1_0_, category1_.DELETED as DELETED0_, category1_.SYS_FLAG as SYS_FLAG0_, category1_.EDIT_DATE as EDIT_DATE0_, category1_.NAME as NAME0_, category1_.CREATE_USER as CREATE_U6_0_, category1_.DESCRIPTION as DESCRIPT7_0_, category1_.EDIT_USER as EDIT_USER0_, category1_.SORT_ORDER as SORT_ORDER0_, category1_.CREATE_DATE as CREATE_10_0_ from "TYPE" type0_ left outer join "CATEGORY" category1_ on type0_.CATEGORY_ID=category1_.CATEGORY_ID where type0_.TYPE_ID=?

14:53:47,564 DEBUG SQL:237 - select securitygr0_.SECURITY_GROUP_ID as SECURITY1_0_, securitygr0_.DELETED as DELETED0_, securitygr0_.CODE as CODE0_, securitygr0_.NAME as NAME0_ from SECURITY_GROUP securitygr0_ where securitygr0_.SECURITY_GROUP_ID=?

14:53:47,614 DEBUG SQL:237 - select securitygr0_.SEC_GROUP_FUNCTIONAL_AREA_ID as SEC_GROU1___, securitygr0_.FUNCTIONAL_AREA_ID as FUNCTIO12___, securitygr0_.SEC_GROUP_FUNCTIONAL_AREA_ID as SEC_GROU1_1_, securitygr0_.DELETED as DELETED1_, securitygr0_.EDIT_DATE as EDIT_DATE1_, securitygr0_.ALLOW_DELETE as ALLOW_DE4_1_, securitygr0_.ALLOW_ADD as ALLOW_ADD1_, securitygr0_.CREATE_USER as CREATE_U6_1_, securitygr0_.EDIT_USER as EDIT_USER1_, securitygr0_.ALLOW_DISPLAY as ALLOW_DI8_1_, securitygr0_.CREATE_DATE as CREATE_D9_1_, securitygr0_.ALLOW_UPDATE as ALLOW_U10_1_, securitygr0_.SECURITY_GROUP_ID as SECURIT11_1_, securitygr0_.FUNCTIONAL_AREA_ID as FUNCTIO12_1_, securitygr1_.SECURITY_GROUP_ID as SECURITY1_0_, securitygr1_.DELETED as DELETED0_, securitygr1_.CODE as CODE0_, securitygr1_.NAME as NAME0_ from SEC_GROUP_FUNCTIONAL_AREA securitygr0_ left outer join SECURITY_GROUP securitygr1_ on securitygr0_.SECURITY_GROUP_ID=securitygr1_.SECURITY_GROUP_ID where securitygr0_.FUNCTIONAL_AREA_ID=? and securitygr0_.DELETED=0

14:53:47,644 DEBUG SQL:237 - select functional0_.SEC_GROUP_FUNCTIONAL_AREA_ID as SEC_GROU1___, functional0_.SECURITY_GROUP_ID as SECURIT11___, functional0_.SEC_GROUP_FUNCTIONAL_AREA_ID as SEC_GROU1_3_, functional0_.DELETED as DELETED3_, functional0_.EDIT_DATE as EDIT_DATE3_, functional0_.ALLOW_DELETE as ALLOW_DE4_3_, functional0_.ALLOW_ADD as ALLOW_ADD3_, functional0_.CREATE_USER as CREATE_U6_3_, functional0_.EDIT_USER as EDIT_USER3_, functional0_.ALLOW_DISPLAY as ALLOW_DI8_3_, functional0_.CREATE_DATE as CREATE_D9_3_, functional0_.ALLOW_UPDATE as ALLOW_U10_3_, functional0_.SECURITY_GROUP_ID as SECURIT11_3_, functional0_.FUNCTIONAL_AREA_ID as FUNCTIO12_3_, functional1_.FUNCTIONAL_AREA_ID as FUNCTION1_0_, functional1_.DELETED as DELETED0_, functional1_.CAN_DELETE as CAN_DELETE0_, functional1_.EDIT_DATE as EDIT_DATE0_, functional1_.CAN_DISPLAY as CAN_DISP5_0_, functional1_.NAME as NAME0_, functional1_.CREATE_USER as CREATE_U7_0_, functional1_.CAN_UPDATE as CAN_UPDATE0_, functional1_.DESCRIPTION as DESCRIPT9_0_, functional1_.CODE as CODE0_, functional1_.EDIT_USER as EDIT_USER0_, functional1_.CREATE_DATE as CREATE_12_0_, functional1_.CAN_ADD as CAN_ADD0_, functional1_.FUNCTIONAL_AREA_TYPE_ID as FUNCTIO14_0_, type2_.TYPE_ID as TYPE_ID1_, type2_.DATA as DATA1_, type2_.DELETED as DELETED1_, type2_.EDIT_DATE as EDIT_DATE1_, type2_.CODE as CODE1_, type2_.NAME as NAME1_, type2_.CREATE_USER as CREATE_U7_1_, type2_.DESCRIPTION as DESCRIPT8_1_, type2_.EDIT_USER as EDIT_USER1_, type2_.SORT_ORDER as SORT_ORDER1_, type2_.CREATE_DATE as CREATE_11_1_, type2_.CATEGORY_ID as CATEGOR12_1_, category3_.CATEGORY_ID as CATEGORY1_2_, category3_.DELETED as DELETED2_, category3_.SYS_FLAG as SYS_FLAG2_, category3_.EDIT_DATE as EDIT_DATE2_, category3_.NAME as NAME2_, category3_.CREATE_USER as CREATE_U6_2_, category3_.DESCRIPTION as DESCRIPT7_2_, category3_.EDIT_USER as EDIT_USER2_, category3_.SORT_ORDER as SORT_ORDER2_, category3_.CREATE_DATE as CREATE_10_2_ from SEC_GROUP_FUNCTIONAL_AREA functional0_ left outer join FUNCTIONAL_AREA functional1_ on functional0_.FUNCTIONAL_AREA_ID=functional1_.FUNCTIONAL_AREA_ID left outer join "TYPE" type2_ on functional1_.FUNCTIONAL_AREA_TYPE_ID=type2_.TYPE_ID left outer join "CATEGORY" category3_ on type2_.CATEGORY_ID=category3_.CATEGORY_ID where functional0_.SECURITY_GROUP_ID=? and functional0_.DELETED=0

14:53:47,694 DEBUG SQL:237 - select securitygr0_.SEC_GROUP_FUNCTIONAL_AREA_ID as SEC_GROU1___, securitygr0_.FUNCTIONAL_AREA_ID as FUNCTIO12___, securitygr0_.SEC_GROUP_FUNCTIONAL_AREA_ID as SEC_GROU1_1_, securitygr0_.DELETED as DELETED1_, securitygr0_.EDIT_DATE as EDIT_DATE1_, securitygr0_.ALLOW_DELETE as ALLOW_DE4_1_, securitygr0_.ALLOW_ADD as ALLOW_ADD1_, securitygr0_.CREATE_USER as CREATE_U6_1_, securitygr0_.EDIT_USER as EDIT_USER1_, securitygr0_.ALLOW_DISPLAY as ALLOW_DI8_1_, securitygr0_.CREATE_DATE as CREATE_D9_1_, securitygr0_.ALLOW_UPDATE as ALLOW_U10_1_, securitygr0_.SECURITY_GROUP_ID as SECURIT11_1_, securitygr0_.FUNCTIONAL_AREA_ID as FUNCTIO12_1_, securitygr1_.SECURITY_GROUP_ID as SECURITY1_0_, securitygr1_.DELETED as DELETED0_, securitygr1_.CODE as CODE0_, securitygr1_.NAME as NAME0_ from SEC_GROUP_FUNCTIONAL_AREA securitygr0_ left outer join SECURITY_GROUP securitygr1_ on securitygr0_.SECURITY_GROUP_ID=securitygr1_.SECURITY_GROUP_ID where securitygr0_.FUNCTIONAL_AREA_ID=? and securitygr0_.DELETED=0

14:53:47,734 DEBUG SQL:237 - select typeset0_.TYPE_ID as TYPE_ID__, typeset0_.CATEGORY_ID as CATEGOR12___, typeset0_.TYPE_ID as TYPE_ID0_, typeset0_.DATA as DATA0_, typeset0_.DELETED as DELETED0_, typeset0_.EDIT_DATE as EDIT_DATE0_, typeset0_.CODE as CODE0_, typeset0_.NAME as NAME0_, typeset0_.CREATE_USER as CREATE_U7_0_, typeset0_.DESCRIPTION as DESCRIPT8_0_, typeset0_.EDIT_USER as EDIT_USER0_, typeset0_.SORT_ORDER as SORT_ORDER0_, typeset0_.CREATE_DATE as CREATE_11_0_, typeset0_.CATEGORY_ID as CATEGOR12_0_ from "TYPE" typeset0_ where typeset0_.CATEGORY_ID=?


Debug level Hibernate log excerpt:
N/A

Sorry that this message is already so long, but I am only trying to be thorough in including all necessary information, as the message template advises.

I am trying to create an HQL query to join the three objects definied in the mapping files above using outer joins.

FunctionalArea and SecurityGroup are joined in the SecGroupFunctionalArea table. A FunctionalArea may have one, many, or no SecGroupFunctionalAreas. Ditto for SecurityGroup.

If I run the following HQL:

from FunctionalArea fa

four FunctionalArea objects are returned. This is correct.

However, if I run the following:

from FunctionalArea fa left outer join fa.securityGroupSet sgfa

only two FunctionalAreas are returned--the two that have SecGroupFunctionalArea associations. I would expect the outer join HQL to return all of my FunctionalAreas, regardless of whether there are any SecGroupFunctionalAreas, just as the following SQL, when run against my Oracle database, returns 4 rows:

select fa.name
from functional_area fa
left outer join sec_group_functional_area sgfa on (fa.functional_area_id = sgfa.functional_area_id)

Please, someone tell me what I'm missing here. I've been working on this problem for the better part of two days and have gotten nowhere.

Thanks.

--Rob

With a little more analysis of the generated SQL I have identified the problem, but no solution presents itself. I think that the problem might be a limitation in Hibernate, but perhaps there is some way around this that someone could help me with.

The problem has to do with the where attribute in my set element for securityGroupSet in the FunctionalArea mapping:



Here, again, is the HQL I am running:



Here is a simplified version of the first SQL query generated by hibernate:



Although there are four rows in the functional_area table (two with associated rows in sec_group_functional_area), this SQL selects two rows. The reason for this is the where clause. When you apply a where condition to your outter join table, you effectively turn your outer join into an inner join.

The proper way to generate SQL in this cirsumstance would be:



Note that the where condition is appended to the join clause. This SQL properly selects all four functional_area rows, including the two that do not have sec_group_functional_area associations.

How can I make Hibernate apply the where condition to the join clause instead of in a where clause?

Thanks.

Here I am responding a second time to my own post.

It looks like there is already an issue report on this issue:

http://opensource.atlassian.com/projects/hibernate/browse/HB-1089

Aparently this will be an enhancement in Hibernate 3. However, the issue in question does not directly address the use of where attribute in a colletion mapping. My sincere hope is that where conditions in collection mappings will be appeneded to the join clause instead of the where clause. Or perhaps there will be a new join-condition attribute for this.

Can anyone comment?

Well, this will teach me to complete my research before posting. I can see that exactly what I'm asking for is in the release notes for Hibernate 3 alpha.

Thanks.