[hibernate-issues] [Hibernate-JIRA] Created: (HHH-6042) To much columns in select queries after creating criteria with join

[Karol Kowalczyk (JIRA)]

To much columns in select queries after creating criteria with join
-------------------------------------------------------------------

                 Key: HHH-6042
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6042
             Project: Hibernate Core
          Issue Type: Bug
          Components: core
    Affects Versions: 3.2.6
         Environment: <dependency>
   <groupId>org.hibernate</groupId>
   <artifactId>hibernate</artifactId>
   <version>3.2.6.ga</version>
</dependency>

Any database
            Reporter: Karol Kowalczyk
         Attachments: AbstractEntityJoinWalker.java, Loader.java

When I create criteria for any class with any value of property of association f.e.:
java code:
    eventDCriteria = DetachedCriteria.forClass(Event.class);
    personDCriteria = eventDCriteria.createCriteria("participants");
    personDCriteria.add(Expression.eq("age", 41));
    List<Event> events=(List<Event>)getHibernateTemplate().findByCriteria(eventDCriteria, 0, 2);

class mappings:
    <class name="Event" table="EVENTS">
        <id name="id" column="EVENT_ID">
            <generator class="native"/>
        </id>
        <property name="date" type="timestamp" column="EVENT_DATE"/>
        <property name="title"/>

        <set name="participants" table="PERSON_EVENT" inverse="true">
            <key column="EVENT_ID"/>
            <many-to-many column="PERSON_ID" class="Person"/>
        </set>
    </class>
    <class name="Person" table="PERSON">
        <id name="id" column="PERSON_ID">
            <generator class="native"/>
        </id>
        <property name="age"/>
        <property name="firstname"/>
        <property name="lastname"/>

        <set name="events" table="PERSON_EVENT" >
            <key column="PERSON_ID"/>
            <many-to-many column="EVENT_ID" class="Event"/>
        </set>
        
        <set name="emailAddresses" table="PERSON_EMAIL_ADDR" >
            <key column="PERSON_ID"/>
            <element type="string" column="EMAIL_ADDR"/>
        </set>

    </class>
a Hibernate still creates select query f.e.:
select e.EVENT_ID, e.EVENT_DATE, e.title, pe.EVENT_ID, pe.PERSON_ID, p.PERSON_ID, p.age, p.firstname, ...
  from EVENTS e
  inner join PERSON_EVENT pe on pe.EVENT_ID=e.EVENT_ID
  inner join PERSON p on p.PERSON_ID=pe.PERSON_ID
  where p.age=?;

Why this hibernates query have UNNECESSARY list of columns: pe.EVENT_ID, pe.PERSON_ID, p.PERSON_ID, p.age, p.firstname, ...?

This query is not INDEXONLY for any databases.

More EFFECTIVE query is:
select e.EVENT_ID, e.EVENT_DATE, e.title
  from EVENTS e
  inner join PERSON_EVENT pe on pe.EVENT_ID=e.EVENT_ID
  inner join PERSON p on p.PERSON_ID=pe.PERSON_ID
  where p.age=?;

A prepared only for my two changes in hibernate: Loader.java and AbstractEntityJoinWalker.java to get more effective selects.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira