subselect fetching ignores max results

Description

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.

Environment

Hibernate 3.2.1
HSQLDB 8.0

Activity

Show:
Vikram Naik
August 10, 2011, 1:26 PM

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?

Daniel Mauricio Patino León
September 29, 2011, 11:06 PM

This kind of fecth strategy is very usefull, please fix it!

Thank you

Laird Nelson
October 17, 2011, 10:12 PM

I established a link between this bug and because it seemed to me the two are reporting the same problem. Please feel free to sever this link if these are not the same bug.

umanath kandhallu
January 20, 2012, 7:27 AM
Edited

i used following as a work around, where i set 200 as maxResults in parent criteria query.

i defined parent child @OneToMany with in below fashion instead of using subselect:

@Fetch(FetchMode.JOIN)
@BatchSize(size=200)

hibernate used subquery to fetch child records like one below:

select * from child where parentid in (?,?,?...)
instead of
select * from child where parentid in (criteria query which used to select parent)

Dmitriy Barbitov
July 3, 2012, 9:39 AM

Hibernate 4.1.4.Final: still the same issue (Postgresql 8.4):

Hibernate: select this_.id as id4_0_, ... from uslugi.zapros_common this_ where this_.is_archive=? order by this_.id asc limit ?
Hibernate: select ... from ... where zaprostkmv0_.zapros_common_id in (select this_.id from uslugi.zapros_common this_ where this_.is_archive=?)

Assignee

Unassigned

Reporter

James Roper

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Minor
Configure