[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

[j nadler (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3058?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=37329#action_37329 ] 

j nadler commented on HHH-3058:
-------------------------------

The same problem exists for UNION queries:   (select top 10 ...) union (select ...) union (select ...)

The 'top 10' only gets inserted on the first query in the union, which is incorrect.

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

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