cartesian join on association mapping (nested 3 layers)

stewart.cambridge · **Joined:** Sat Nov 19, 2005 2:46 pm **Posts:** 69

Hello.
I have a 3 layered parent-child model, which is running a cartesian SQL query. I am struggling to understand why, and would be grateful for any pointers. I will rate, of course :-)

A WorkflowTemplate has many StageTemplates, which in turn has many ElementTemplates. My question is: Why does the generated SQL join all 3 tables together?

Hibernate version: 3.1
Mapping documents:
WorkflowTemplate

Code:

    <class name="objects.template.WorkflowTemplate" table="workflow_template" >
      <id name="id" column="id" type="long" unsaved-value="-1" >
            <generator class="native"/>
      </id>
      <bag name="stageTemplates" inverse="true" cascade="all">
        <key column="workflow_id"/>
        <one-to-many class="objects.template.stage.StageTemplate"/>
      </bag>
      <property name="code" type="java.lang.String" update="true" insert="true" column="code" />
      <property name="name" type="java.lang.String" update="true" insert="true" column="name" />
    </class>

StageTemplate

Code:

<class name="objects.template.stage.StageTemplate" table="stage_template" >
      <id name="id" column="id" type="long" unsaved-value="-1" >
        <generator class="native"/>
      </id>
      <many-to-one name="workflowTemplate" class="objects.template.WorkflowTemplate"
            cascade="all" outer-join="auto" update="true" insert="true" column="workflow_id" not-null="true" />
      <bag name="elementTemplates" inverse="true" cascade="all" lazy="true">
        <key column="stage_id"/>
        <one-to-many class="objects.template.element.ElementTemplate"/>
      </bag>
      <property name="code" type="java.lang.String" update="true" insert="true" column="code" />
      <property name="name" type="java.lang.String" update="true" insert="true" column="name" />
    </class>

ElementTemplate

Code:

    <class name="objects.template.element.ElementTemplate" table="element_template" >
        <id name="id" column="id" type="long" unsaved-value="-1" >
            <generator class="native"/>
        </id>
        <many-to-one name="stageTemplate" class="objects.template.stage.StageTemplate"
            cascade="all" outer-join="auto" update="true" insert="true" column="stage_id" not-null="true" />
        <property name="code" type="java.lang.String" update="true" insert="true" column="code" />
        <property name="name" type="java.lang.String" update="true" insert="true" column="name" />
    </class>

Code between sessionFactory.openSession() and session.close():
Using "Open Session in View" pattern. In the servlet I am fetching a list of templates from an owning object:

Code:

Collection<? extends WorkflowTemplate> templates = company.getBusinessTemplates();
for(WorkflowTemplate t : templates)
      logger.debug("STAGES: " + t.getStageTemplates()); // prints 49 - which is equal to the number of ElementTemplates in the entire WorkflowTemplate (there are 10 StageTemplates with a varying number of ElementTemplates in each)

Full stack trace of any exception that occurs: No Exception is thrown

Name and version of the database you are using: MySQL 5.0.15-nt
The generated SQL (show_sql=true):
(When run directly on database, this returns 49 rows. It is giving a cartesian product, by joining all workflows, stages and elements together.)

Code:

select businesste0_.id as id31_2_,
businesste0_.code as code31_2_,
businesste0_.name as name31_2_,
stagetempl1_.workflow_id as workflow3_4_,
stagetempl1_.id as id4_,
stagetempl1_.id as id26_0_,
stagetempl1_.workflow_id as workflow3_26_0_,
stagetempl1_.sequence as sequence26_0_,
stagetempl1_.code as code26_0_,
stagetempl1_.name as name26_0_,
elementtem2_.stage_id as stage2_5_,
elementtem2_.id as id5_,
elementtem2_.id as id13_1_,
elementtem2_.stage_id as stage2_13_1_,
elementtem2_.sequence as sequence13_1_,
elementtem2_.code as code13_1_,
elementtem2_.name as name13_1_
from workflow_template businesste0_
left outer join stage_template stagetempl1_ on businesste0_.id=stagetempl1_.workflow_id
left outer join element_template elementtem2_ on stagetempl1_.id=elementtem2_.stage_id
where businesste0_.id=?

Debug level Hibernate log excerpt: Will gladly supply if it's really needed.

stewart.cambridge · **Joined:** Sat Nov 19, 2005 2:46 pm **Posts:** 69

Update:
I have been able to get the behaviour I want using a <list> instead of a <bag> for all lists in the mappings.

But the generated SQL is still joining all 3 tables, which makes me wonder if I targeted the correct SQL query.

I'm still very curious about what's happening under hibernate's bonnet (that's a "hood" for all who speak US English) here. Can anyone explain?

(I'll still rate if someone can explain: I can't stand fixing a bug by "lucking in" on the solution - I'd much rather know)