[hibernate-issues] [Hibernate-JIRA] Updated: (HHH-3374) Subselect fetching fetches all elements in link table instead only needed

[Alexander V. Zinin (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3374?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Alexander V. Zinin updated HHH-3374:
------------------------------------

    Attachment: hibernate_bug.rar

Also available here: http://zinin.ru/share/hibernate_bug.rar



> Subselect fetching fetches all elements in link table instead only needed
> -------------------------------------------------------------------------
>
>                 Key: HHH-3374
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3374
>             Project: Hibernate3
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.2.6
>            Reporter: Alexander V. Zinin
>         Attachments: hibernate_bug.rar
>
>
> I've some entity with many-to-many collection inside. Example:
> public class Message implements Serializable {
>     @ManyToMany(fetch = FetchType.LAZY)
>     @JoinTable(name = "message_has_file_attachment", joinColumns = {@JoinColumn(name = "message_id")},
>             inverseJoinColumns = {@JoinColumn(name = "file_attachment_id")})
>     @Fetch(value = FetchMode.SUBSELECT)
>     public Set<MessageImageFileAttachment> getFileAttachments() {
>         return fileAttachments;
>     }
> }
> I want to fetch this collection for items I've fetched. I do:
>         DetachedCriteria criteria = DetachedCriteria.forClass(messageClass);
>                 criteria.setFetchMode("fileAttachments", FetchMode.SELECT);
>                 List list = getHibernateTemplate().findByCriteria(criteria, firstResult, maxResults);
>                 if (list.size() > 0) {
>                     Message message = (Message) list.get(0);
>                     Hibernate.initialize(message.getFileAttachments());
>                 }
> And hibernate generates subquery like this:
> select
>         fileattach0_.message_id as message1_1_,
>         fileattach0_.file_attachment_id as file2_1_,
>         messageima1_.id as id12_0_,
>         messageima1_.creationDate as creation3_12_0_,
>         messageima1_.hostname as hostname12_0_,
>         messageima1_.name as name12_0_,
>         messageima1_.path as path12_0_,
>         messageima1_.submited as submited12_0_,
>         messageima1_.description as descript8_12_0_,
>         messageima1_.hasSmallImage as hasSmall9_12_0_,
>         messageima1_.height as height12_0_,
>         messageima1_.smallHeight as smallHe11_12_0_,
>         messageima1_.smallWidth as smallWidth12_0_,
>         messageima1_.tinyHeight as tinyHeight12_0_,
>         messageima1_.tinyWidth as tinyWidth12_0_,
>         messageima1_.width as width12_0_ 
>     from
>         message_has_file_attachment fileattach0_ 
>     left outer join
>         file_attachment messageima1_ 
>             on fileattach0_.file_attachment_id=messageima1_.id 
>     where
>         fileattach0_.message_id in (
>             select
>                 this_.id 
>             from
>                 message this_ 
>             inner join
>                 a_user user2_ 
>                     on this_.user_id=user2_.id 
>             left outer join
>                 file_attachment articlemes3_ 
>                     on this_.image_id=articlemes3_.id 
>             )
> So it fetched ALL rows in table ! And what happens if there will be a millions rows ?
> http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#performance
> Subselect fetching - a second SELECT  is used to retrieve the associated collections for all entities retrieved in a previous query or fetch. Unless you explicitly disable lazy fetching by specifying lazy="false", this second select will only be executed when you actually access the association.

-- 
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