I'm using a limit while also left join fetching a collection in the same query. If the query is supposed to return 100 values, it actually won't in reality since one is forced to filter out the duplicate results, which will obviously be less than 100.

Now, I don't expect SQL to know about this requirement, or even Hibernate actually. However, I'm curious what is the best way to solve this problem? Should I limit to 150 or 200 instead (a value chosen by design looking at the distribution of data)? If I did that, I might very well get close to 100 results after the duplicates are removed. If I queried enough results, I might have extra (like 120) and I could filter out the remaining 20 to ensure 100 results were returned each time.

Am I on the right track or is there some extra support in Hibernate that I can use to simplify the problem to a simple line of code?

Well, earlier today I just scrapped the whole concept since loading 2.1 times the records just to get 100 all the time was actually slower than doing the fetch join. If you haven't been able to tell, I really hate performance testing.