We're updating the issue view to help you get more done. 

SQLServer2005LimitHandler applies TOP(?) to subselect

Description

The following sql query with a subselect inserts TOP into the subselect instead of the top-level select clause, producing the sql server error "The number of rows provided for a TOP or FETCH clauses row count parameter must be an integer." (because the parameters are in the wrong order).

Simplified test case:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 @Test public void testGetLimitStringWithMaxOnlyWithDistinctSubquery() { final String distinctQuery = "select page0_.CONTENTID as CONTENTI1_12_ " + "where page0_.CONTENTTYPE='PAGE' and (page0_.CONTENTID in " + "(select distinct page2_.PREVVER from CONTENT page2_ where (page2_.PREVVER is not null))"; assertEquals( "select TOP(?) page0_.CONTENTID as CONTENTI1_12_ " + "where page0_.CONTENTTYPE='PAGE' and (page0_.CONTENTID in " + "(select distinct page2_.PREVVER from CONTENT page2_ where (page2_.PREVVER is not null))", dialect.getLimitHandler().processSql( distinctQuery, toRowSelection( 0, 5 ) ) ); }

Full sql query passed to SQLServer2005LimitHandler by hibernate:

1 select page0_.CONTENTID as CONTENTI1_12_, page0_.HIBERNATEVERSION as HIBERNAT2_12_, page0_.TITLE as TITLE4_12_, page0_.LOWERTITLE as LOWERTIT5_12_, page0_.VERSION as VERSION6_12_, page0_.CREATOR as CREATOR7_12_, page0_.CREATIONDATE as CREATION8_12_, page0_.LASTMODIFIER as LASTMODI9_12_, page0_.LASTMODDATE as LASTMOD10_12_, page0_.VERSIONCOMMENT as VERSION11_12_, page0_.PREVVER as PREVVER12_12_, page0_.CONTENT_STATUS as CONTENT13_12_, page0_.PAGEID as PAGEID14_12_, page0_.SPACEID as SPACEID15_12_, page0_.CHILD_POSITION as CHILD_P16_12_, page0_.PARENTID as PARENTI17_12_ from CONTENT page0_ cross join SPACES space1_ where page0_.CONTENTTYPE='PAGE' and page0_.SPACEID=space1_.SPACEID and (page0_.PREVVER is null) and page0_.CONTENT_STATUS='current' and space1_.SPACEKEY=? and (page0_.CONTENTID in (select distinct page2_.PREVVER from CONTENT page2_ where page2_.CONTENTTYPE='PAGE' and page2_.TITLE=? and (page2_.PREVVER is not null) and page2_.CONTENT_STATUS='current')) and (exists (select spacepermi3_.PERMID from SPACEPERMISSIONS spacepermi3_ where page0_.SPACEID=spacepermi3_.SPACEID and (spacepermi3_.PERMUSERNAME=? or spacepermi3_.PERMGROUPNAME in (?) or spacepermi3_.PERMALLUSERSSUBJECT='authenticated-users' or (spacepermi3_.PERMUSERNAME is null) and (spacepermi3_.PERMGROUPNAME is null) and (spacepermi3_.PERMALLUSERSSUBJECT is null)) and spacepermi3_.PERMTYPE=?)) order by page0_.LASTMODDATE desc

Environment

None

Status

Assignee

Chris Cranford

Reporter

Clinton Volzke

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

Yes, likely

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

5.2.2

Priority

Minor