The following sql query with a subselect produces 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:
{code}
@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 ) )
);
}
{code}
Full sql query passed to SQLServer2005LimitHandler by hibernate :
{code}
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
{code}
|
|