SQL from simple HQL has duplicate tables in from clause

Rising_Phorce · **Joined:** Wed Feb 08, 2006 12:42 pm **Posts:** 3

An HQL query is generating extra joins because two tables are added to the from clause twice.

Hibernate version: 3

Mapping documents:

schema:

Code:

Table - Timesheet
id (pk)
name

Table - Entry
id (pk)
date

Join Table - Timesheet_Entry
timesheet_id
entry_id

mapping:

Timesheet.hbm.xml:
...

Code:

<id name="id" type="long">
            <column name="ID" />
            <generator class="native" />
        </id>
        <set name="timesheetEntries" table="TIMESHEET_ENTRY" 
          cascade="all,delete-orphan" fetch="subselect" >
      <key column="TIMESHEET_ID" not-null="true" />
      <many-to-many column="ENTRY_ID" 
         class="Entry" lazy="false" unique="true" />
   </set>

...

Code between sessionFactory.openSession() and session.close():

HQL:

Code:

.createQuery("select sum(t.timesheetEntries.hours) as hours " +
   "from Timesheet t where t.id=? and " +           
   "t.timesheetEntries.entryDate=?");

Name and version of the database you are using: MySQL 5.0.18

The generated SQL (show_sql=true):

TIMESHEET_ENTRY and ENTRY are in the from clause twice causing the sum to not be accurate.

(with sensible table aliases):

Code:

select sum(e1.HOURS) as hours
from timesheet t1, TIMESHEET_ENTRY te1,
     entry e1, TIMESHEET_ENTRY te2, entry e2
where t1.ID=te2.TIMESHEET_ID
  and te2.ENTRY_ID=e2.ID
  and t1.ID=te1.TIMESHEET_ID
  and te1.ENTRY_ID=e1.ID
  and t1.ID=1
  and e2.ENTRY_DATE='2006-02-06'

pksiv · **Posted:** Wed Feb 08, 2006 2:50 pm

You might try using a join in your query.

Code:

select sum(te.hours) as hours 
  from Timesheet t inner join t.timesheetEntries as te
 where t.id = ? and te.entryDate = ?

I haven't tested this but I think the syntax is correct for your query.

Rising_Phorce · **Joined:** Wed Feb 08, 2006 12:42 pm **Posts:** 3

adding the inner join to the HQL caused inner joins to be added to the first two tables in the from clause but the duplicate table still exist. See new sql below.

Code:

select sum(entry4_.HOURS) as col_0_0_
from timesheet timesheet0_
inner join TIMESHEET_ENTRY timesheete1_ 
     on timesheet0_.ID=timesheete1_.TIMESHEET_ID
inner join entry entry2_ 
     on timesheete1_.ENTRY_ID=entry2_.ID,
TIMESHEET_ENTRY timesheete3_,
entry entry4_
where timesheet0_.ID=timesheete3_.TIMESHEET_ID 
and timesheete3_.ENTRY_ID=entry4_.ID 
and timesheet0_.ID=?
and entry2_.ENTRY_DATE=?

Rising_Phorce · **Joined:** Wed Feb 08, 2006 12:42 pm **Posts:** 3

I must have made an error put the suggested HQL into my code. It's working now. I'll give credit. But can anyone answer or point me in the direction of documentation that describes why I need that join?