[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2666) subselect fetching ignores max results

[Vikram Naik (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2666?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=43207#comment-43207 ] 

Vikram Naik commented on HHH-2666:
----------------------------------

I'm using Hibernate 3.6.3.Final, and facing the same issue. 

As Gavin King puts in https://hibernate.onjira.com/browse/HHH-304

"Any limit that gets applied to a query should also get applied to the subselect of any subsequent collection fetches."

This is a major issue for us, let us know how can I help. As Frédéric Donckels have already provided a test case, does it need anything else to get it on priority list?

> subselect fetching ignores max results
> --------------------------------------
>
>                 Key: HHH-2666
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2666
>             Project: Hibernate Core
>          Issue Type: Improvement
>          Components: query-hql, query-sql
>    Affects Versions: 3.2.1, 3.5.3
>         Environment: Hibernate 3.2.1
> HSQLDB 8.0
>            Reporter: James Roper
>            Priority: Minor
>         Attachments: TestHibernateSubselect.java
>
>
> When maxResults is set for a query, the hibernate subselect fetching strategy ignores it.  For example, I have a class entry, that has a list of comments, which are configured to use the subselect fetching strategy.  You can see that in the original query, only the top ? results are fetched:
>     select
>         top ? entry0_.id as id0_,
>         entry0_.title as title0_,
>         entry0_.entry as entry0_,
>         entry0_.time as time0_,
>         entry0_.category_id as category5_0_ 
>     from
>         Entry entry0_ 
>     order by
>         entry0_.time desc
> But then when it does the sub select query to lazily load the comments, this is the query it runs:
>     select
>         comments0_.entry_id as entry5_1_,
>         comments0_.id as id1_,
>         comments0_.id as id1_0_,
>         comments0_.time as time1_0_,
>         comments0_.comment as comment1_0_,
>         comments0_.author as author1_0_ 
>     from
>         Comment comments0_ 
>     where
>         comments0_.entry_id in (
>             select
>                 entry0_.id 
>             from
>                 Entry entry0_ 
>         ) 
>     order by
>         comments0_.time desc
> So, it loads every single comment in the database, even though only the comments for the top ? entries are needed.  Of course, if there was no order by clause on the first query, the sub select may not return the same results, so it should probably only do this when there is an order by clause in the first query, and it should make sure it includes the order by clause in the sub select.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira