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

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.