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

[James Roper (JIRA)]

subselect fetching ignores max results
--------------------------------------

                 Key: HHH-2666
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2666
             Project: Hibernate3
          Issue Type: Improvement
          Components: query-hql, query-sql
    Affects Versions: 3.2.1
         Environment: Hibernate 3.2.1
HSQLDB 8.0
            Reporter: James Roper
            Priority: Minor


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