Hi,
We have an Article table in a many-to-many relationship with SiteSection. The association table is Article_Site_Section. In this table, ArticleID 14021 is present in SiteSection 238, 239 and 240.

When I pass in rownum=3 to the query using Criteria API, Hibernate returns 3 rows (i.e 3 Article instances) that have:

ArticleID=14021, SiteSection=238,239,240
ArticleID=14021, SiteSection=238,239,240
ArticleID=14021, SiteSection=238,239,240

We are avoiding this by doing:
and this reduces the number of rows to 1. So if I want it to be 3 after the result transformation, I have to set rownum to be more than 3.

What I really want, when I pass rownum=3, is result like this:
ArticleID=14021, SiteSection=238,239,240
ArticleID=14022, SiteSection=238,240,243,244
ArticleID=14023, SiteSection=238,240


I am not sure if I am missing something or if Hibernate doesnt provide for this.

Any help is much much appreciated.
Thanks,
Sushil
__________________________________________________________

Hibernate version: 3.0

__________________________________________________________


Mapping documents:

<class name="com.repository.article.Article" table="ARTICLE">
<id name="artID" type="int" column="ART_ID">
...................
...................
<set name="siteSections" table="ARTICLE_SITE_SECTION" fetch="join" batch-size="50">
<key column="ART_ID"/>
<many-to-many class="com.repository.article.SiteSection"
column="SITE_SECTION_ID" outer-join="true"/>
</set>
</class>

__________________________________________________________

Code between sessionFactory.openSession() and session.close():


__________________________________________________________


Name and version of the database you are using: Oracle9i Enterprise Edition Release 9.2.0.5.0

__________________________________________________________


The generated SQL (show_sql=true):

select * from ( select this_.ART_ID as ART1_2_, this_.CREATE_SITE_ID as CREATE2_1_2_,
this_.SITE_ID as SITE3_1_2_,
this_.LANGUAGE_ID as LANGUAGE4_1_2_,
this_.DESCRIPTION as DESCRIPT5_1_2_,
this_.PUBLISH_DATE as PUBLISH6_1_2_,
this_.HEADLINE as HEADLINE1_2_,
this_.EYEBROW as EYEBROW1_2_,
this_.TEAMSITE_OBJECT_ID as TEAMSITE9_1_2_,
this_.SUB_HEAD as SUB10_1_2_,
this_.TEASER as TEASER1_2_,
this_.CREATE_DATE as CREATE12_1_2_,
this_.BYLINE as BYLINE1_2_,
this_.EMBARGO_DATE as EMBARGO14_1_2_,
this_.LAST_MODIFIED as LAST15_1_2_,
this_.BROWSER_TITLE as BROWSER16_1_2_,
this_.FIRSTPAGE as FIRSTPAGE1_2_,
this_.CD_ARTID as CD18_1_2_,
this_.CD_DOCID as CD19_1_2_,
this_.TOPIC as TOPIC1_2_,
this_.KEYWORDS as KEYWORDS1_2_,
this_.EXTERNAL_REF as EXTERNAL22_1_2_,
this_.CREATORBIO as CREATORBIO1_2_,
this_.COPYRIGHTHOLDER as COPYRIG24_1_2_,
this_.COPYRIGHTYEAR as COPYRIG25_1_2_,
this_.ISSUE_NUMBER as ISSUE26_1_2_,
this_.DATELINE_LOCATION as DATELINE27_1_2_,
this_.ONLINE_RELEASE_DATE as ONLINE28_1_2_,
this_.ENDNOTES as ENDNOTES1_2_,

sitesectio1_.SITE_SECTION_ID as SITE1_0_,
sitesectio1_.SECTION_NAME as SECTION2_11_0_,

sitearticl2_.SITE_ARTICLE_TYPE_ID as SITE1_1_,
sitearticl2_.SITE_ARTICLE_TYPE_NAME as SITE2_13_1_,
sitearticl2_.SITE_ARTICLE_TYPE_DESCRIPTION as SITE3_13_1_,
sitearticl2_.SITE_ID as SITE4_13_1_

from ARTICLE this_
inner join ARTICLE_SITE_SECTION sitesectio4_
on this_.ART_ID=sitesectio4_.ART_ID

inner join SITE_SECTION sitesectio1_
on sitesectio4_.SITE_SECTION_ID=sitesectio1_.SITE_SECTION_ID

inner join ARTICLE_SITE_ARTICLE_TYPE sitearticl6_
on this_.ART_ID=sitearticl6_.ART_ID

inner join SITE_ARTICLE_TYPE sitearticl2_
on sitearticl6_.SITE_ARTICLE_TYPE_ID=sitearticl2_.SITE_ARTICLE_TYPE_ID

where this_.SITE_ID in (?) and sitesectio1_.SITE_SECTION_ID in (?,?,?,?,?,?,?)
and sitearticl2_.SITE_ARTICLE_TYPE_ID in (?) order by this_.PUBLISH_DATE desc )
where rownum <= ?