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

[Sean Blaes (JIRA)]

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

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