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

[Karol Kowalczyk (JIRA)]

     [ https://hibernate.onjira.com/browse/HHH-6042?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Karol Kowalczyk updated HHH-6042:
---------------------------------

    Attachment: KosztRealizacjiZapytaniaWHibernate.doc

Costs and plans for queries in hibernate by db2 explain

> To much columns in select queries after creating criteria with join
> -------------------------------------------------------------------
>
>                 Key: HHH-6042
>                 URL: https://hibernate.onjira.com/browse/HHH-6042
>             Project: Hibernate ORM
>          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, KosztRealizacjiZapytaniaWHibernate.doc, 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.
For more information on JIRA, see: http://www.atlassian.com/software/jira