[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3058) When using native sql query and multiple selects with setMaxResults, Hibernate inserts the TOP statement in the first query

When using native sql query and multiple selects with setMaxResults, Hibernate inserts the TOP statement in the first query --------------------------------------------------------------------------------------------------------------------------- Key: HHH-3058 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3058 Project: Hibernate Core Issue Type: Bug Components: query-sql Affects Versions: 3.2.4.sp1 Environment: Hibernate 3.2.4sp1, Microsoft SQL Server Reporter: Sean Blaes Priority: Minor When using a query similar to the following: DECLARE @SUB_TABLE TABLE( ID INT ) INSERT INTO @SUB_TABLE SELECT ID FROM FOO SELECT * FROM BAR INNER JOIN @SUB_TABLE ON ... Then if I call setMaxResults(100) on the query object. It inserts "TOP 100" in the first query, which isn't really what would be expected. Since the second SELECT actually consists of the data that will be returned, the TOP 100 statement should go there. I'd expect it's an easy fix. Just start your search for SELECT from the bottom rather than the top. However, I guess you have to make sure you're not hitting a subquery of a larger select either... I may try cooking up a patch this weekend for this if I can find the time, and I'll attach it to this issue if I do.