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

[Alexander V. Zinin (JIRA)]

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


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