I have a left join fetch HQL for a one-to-many parent-children relationship query. A transfer data object is used in the HQL. According to an online document of this site, I should use the uniquResult method of the Query class to retrieve the uniqu result. I, however, run into an exception. Does that mean the method can't be applied for a TDO?



Hibernate version:
3.0
Mapping documents:

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

Full stack trace of any exception that occurs:
org.springframework.orm.hibernate3.HibernateSystemException: query did not return a unique result: 7; nested exception is org.hibernate.NonUniqueResultException: query did not return a unique result: 7
org.hibernate.NonUniqueResultException: query did not return a unique result: 7
at org.hibernate.impl.AbstractQueryImpl.uniqueElement(AbstractQueryImpl.java:616)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:603)
...

Name and version of the database you are using:
PostgreSQL 8.1
The generated SQL (show_sql=true):
Hibernate: select article0_.id as col_0_0_, user3_.name as col_1_0_, article0_.postDate as col_2_0_, article0_.category_id as col_3_0_, article0_.subject as col_4_0_, article0_.summary as col_5_0_, article0_.content as col_6_0_, count(commentsin2_.id) as col_7_0_, commentsin2_.id as id2_, commentsin2_.id as id, commentsin2_.subject as subject8_, commentsin2_.content as content8_, commentsin2_.postDate as postDate8_, commentsin2_.IPAddress as IPAddress8_, commentsin2_.article_id as article6_8_, commentsin2_.replyTo as replyTo8_, commentsin2_.commentator_id as commenta8_8_, commentsin2_.rating as rating8_, commentsin2_.article_id as article6_0__, commentsin2_.id as id0__ from ilc_Articles article0_ inner join ilc_article_cities cities1_ on article0_.id=cities1_.article_id left outer join ilc_Comments commentsin2_ on article0_.id=commentsin2_.article_id, ilc_users user3_, ilc_categories category4_ where article0_.category_id=category4_.id and article0_.submitter_id=user3_.id and article0_.status=1 and cities1_.city_id=? group by article0_.id , user3_.name , article0_.postDate , article0_.category_id , article0_.subject , article0_.summary , article0_.content , commentsin2_.id , commentsin2_.commentator_id , commentsin2_.postDate , commentsin2_.subject , commentsin2_.content , commentsin2_.IPAddress , commentsin2_.replyTo , commentsin2_.article_id , commentsin2_.rating order by article0_.postDate desc
Hibernate: select category0_.id as id0_, category0_.name as name9_0_ from ilc_categories category0_ where category0_.id=?
Hibernate: select category0_.id as id0_, category0_.name as name9_0_ from ilc_categories category0_ where category0_.id=?
Hibernate: select category0_.id as id0_, category0_.name as name9_0_ from ilc_categories category0_ where category0_.id=?
Hibernate: select article0_.id as id2_, article0_.cityId as cityId5_2_, article0_.category_id as category3_5_2_, article0_.subject as subject5_2_, article0_.summary as summary5_2_, article0_.content as content5_2_, article0_.postDate as postDate5_2_, article0_.IPAddress as IPAddress5_2_, article0_.status as status5_2_, article0_.submitter_id as submitter10_5_2_, category1_.id as id0_, category1_.name as name9_0_, user2_.id as id1_, user2_.cityId as cityId1_1_, user2_.username as username1_1_, user2_.password as password1_1_, user2_.name as name1_1_, user2_.email as email1_1_, user2_.timeOfCreation as timeOfCr7_1_1_, user2_.timeOfLastUpdate as timeOfLa8_1_1_, user2_.status as status1_1_, user2_.encoding as encoding1_1_, user2_.IPAddress as IPAddress1_1_, user2_.street1 as street12_1_1_, user2_.street2 as street13_1_1_, user2_.postalCode as postalCode1_1_, user2_.city as city1_1_, user2_.province as province1_1_, user2_.country as country1_1_ from ilc_Articles article0_ left outer join ilc_categories category1_ on article0_.category_id=category1_.id left outer join ilc_users user2_ on article0_.submitter_id=user2_.id where article0_.id=?
Hibernate: select user0_.id as id0_, user0_.cityId as cityId1_0_, user0_.username as username1_0_, user0_.password as password1_0_, user0_.name as name1_0_, user0_.email as email1_0_, user0_.timeOfCreation as timeOfCr7_1_0_, user0_.timeOfLastUpdate as timeOfLa8_1_0_, user0_.status as status1_0_, user0_.encoding as encoding1_0_, user0_.IPAddress as IPAddress1_0_, user0_.street1 as street12_1_0_, user0_.street2 as street13_1_0_, user0_.postalCode as postalCode1_0_, user0_.city as city1_0_, user0_.province as province1_0_, user0_.country as country1_0_ from ilc_users user0_ where user0_.id=?
Hibernate: select user0_.id as id0_, user0_.cityId as cityId1_0_, user0_.username as username1_0_, user0_.password as password1_0_, user0_.name as name1_0_, user0_.email as email1_0_, user0_.timeOfCreation as timeOfCr7_1_0_, user0_.timeOfLastUpdate as timeOfLa8_1_0_, user0_.status as status1_0_, user0_.encoding as encoding1_0_, user0_.IPAddress as IPAddress1_0_, user0_.street1 as street12_1_0_, user0_.street2 as street13_1_0_, user0_.postalCode as postalCode1_0_, user0_.city as city1_0_, user0_.province as province1_0_, user0_.country as country1_0_ from ilc_users user0_ where user0_.id=?
Hibernate: select referrals0_.article_id as article5_2_, referrals0_.id as id2_, referrals0_.id as id1_, referrals0_.dateAndTime as dateAndT2_11_1_, referrals0_.IPAddress as IPAddress11_1_, referrals0_.actor as actor11_1_, referrals0_.article_id as article5_11_1_, referrals0_.cityId as cityId11_1_, referrals0_.num as num11_1_, user1_.id as id0_, user1_.cityId as cityId1_0_, user1_.username as username1_0_, user1_.password as password1_0_, user1_.name as name1_0_, user1_.email as email1_0_, user1_.timeOfCreation as timeOfCr7_1_0_, user1_.timeOfLastUpdate as timeOfLa8_1_0_, user1_.status as status1_0_, user1_.encoding as encoding1_0_, user1_.IPAddress as IPAddress1_0_, user1_.street1 as street12_1_0_, user1_.street2 as street13_1_0_, user1_.postalCode as postalCode1_0_, user1_.city as city1_0_, user1_.province as province1_0_, user1_.country as country1_0_ from ilc_referrals referrals0_ left outer join ilc_users user1_ on referrals0_.actor=user1_.id where referrals0_.article_id=?
Hibernate: select views0_.article_id as article5_2_, views0_.id as id2_, views0_.id as id1_, views0_.dateAndTime as dateAndT2_10_1_, views0_.IPAddress as IPAddress10_1_, views0_.actor as actor10_1_, views0_.article_id as article5_10_1_, views0_.cityId as cityId10_1_, user1_.id as id0_, user1_.cityId as cityId1_0_, user1_.username as username1_0_, user1_.password as password1_0_, user1_.name as name1_0_, user1_.email as email1_0_, user1_.timeOfCreation as timeOfCr7_1_0_, user1_.timeOfLastUpdate as timeOfLa8_1_0_, user1_.status as status1_0_, user1_.encoding as encoding1_0_, user1_.IPAddress as IPAddress1_0_, user1_.street1 as street12_1_0_, user1_.street2 as street13_1_0_, user1_.postalCode as postalCode1_0_, user1_.city as city1_0_, user1_.province as province1_0_, user1_.country as country1_0_ from ilc_views views0_ left outer join ilc_users user1_ on views0_.actor=user1_.id where views0_.article_id=?
Hibernate: select cities0_.article_id as article1_0_, cities0_.city_id as city2_0_ from ilc_article_cities cities0_ where cities0_.article_id=?
Hibernate: select topicsinte0_.article_id as article1_0_, topicsinte0_.topic as topic0_ from ilc_article_topics topicsinte0_ where topicsinte0_.article_id=?

Debug level Hibernate log excerpt:

Hey
As far as the exception goes you are getting a Set of results, instead you are trying to get Unique. Can you post the code what you have. Probably we can have a better insight on the issue
Cheers
Vijay

Before I posted this question, I had searched on this forum with the key words: "one-to-many" and "query". I had read the first eight pages of the 1139 returned threads. When I continue my reading this morning, I saw a statement from Anthony of the team:

fetch return distinct root instances

join has the same meaning as SQL join, so just use
Set results = new HashSet(query.list()) if you don't want duplicate results.

(http://forum.hibernate.org/viewtopic.ph ... many+query)

Well, this information is not documented.

I need a list of the TDOs. So, I have to convert the returned result to a set and convert it to a list again. Havn't tested it out yet, but I don't know whether the desired order will be preserved.