[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-6824) Hibernate query unexpectedly returns duplicate results

[Matthew Casperson (JIRA)]

    [ https://hibernate.onjira.com/browse/HHH-6824?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=45552#comment-45552 ] 

Matthew Casperson commented on HHH-6824:
----------------------------------------

This was probably due to a onetoone relationship with a join table that listed one side of the relationship twice. Setting a unqiue index on the table highlighted and fixed the problem.

> Hibernate query unexpectedly returns duplicate results
> ------------------------------------------------------
>
>                 Key: HHH-6824
>                 URL: https://hibernate.onjira.com/browse/HHH-6824
>             Project: Hibernate ORM
>          Issue Type: Bug
>          Components: query-hql
>    Affects Versions: 3.6.8
>         Environment: JBoss AS7, Seam 2.2.2
>            Reporter: Matthew Casperson
>
> I have a situation where a query run through Hibernate is returning a single duplicate record, where the same query in SQL does not. The SQL query (formatted for readability) is:
> select * from Topic where 
> (
> 	(
> 		exists (select 1 from TopicToTag where TopicToTag.TopicID = Topic.TopicID and TopicToTag.TagID = 119) 
> 		Or exists (select 1 from TopicToTag where TopicToTag.TopicID = Topic.TopicID and TopicToTag.TagID = 133) 
> 		Or exists (select 1 from TopicToTag where TopicToTag.TopicID = Topic.TopicID and TopicToTag.TagID = 132)
> 	) 
> 	And 
> 	(
> 		exists (select 1 from TopicToTag where TopicToTag.TopicID = Topic.TopicID and TopicToTag.TagID = 19) 
> 		And not exists (select 1 from TopicToTag where TopicToTag.TopicID = Topic.TopicID and TopicToTag.TagID = 14)
> 	)
> )
> The Hibernate query is:
> select topic from com.redhat.topicindex.entity.Topic as Topic where ((exists (select 1 from TopicToTag topicToTag where topicToTag.topic = topic and topicToTag.tag.tagId = 132)  Or exists (select 1 from TopicToTag topicToTag where topicToTag.topic = topic and topicToTag.tag.tagId = 119)  Or exists (select 1 from TopicToTag topicToTag where topicToTag.topic = topic and topicToTag.tag.tagId = 133) ) And (exists (select 1 from TopicToTag topicToTag where topicToTag.topic = topic and topicToTag.tag.tagId = 19)  And not exists (select 1 from TopicToTag topicToTag where topicToTag.topic = topic and topicToTag.tag.tagId = 14) ))
> The SQL query will return 474 records, while the Hibernate will return 475 entities with 1 duplicate.
> The details of the tables are:
> Table Topic
> ===========
> TopicID, TopicTitle, TopicText, TopicAddedBy, TopicTimeStamp, TopicSVNURL, TopicXML
> -----------
> TopicID          int(11) PK
> TopicTitle       varchar(512)
> TopicText        text
> TopicAddedBy     varchar(512)
> TopicTimeStamp   timestamp
> TopicSVNURL      varchar(512)
> TopicXML         text
> Table TopicToTag
> ================
> TopicToTagID, TopicID, TagID
> ----------------
> TopicToTagID     int(11) PK
> TopicID          int(11)
> TagID            int(11)
> Table Tag
> =========
> TagID, TagName, TagDescription
> ---------
> TagID            int(11) PK
> TagName          varchar(512)
> TagDescription   text
> The code for the entities can be viewed at:
> https://sourceforge.net/p/topicindex/code/383/tree/trunk/src/main/com/redhat/topicindex/entity/Tag.java
> https://sourceforge.net/p/topicindex/code/383/tree/trunk/src/main/com/redhat/topicindex/entity/Topic.java
> https://sourceforge.net/p/topicindex/code/383/tree/trunk/src/main/com/redhat/topicindex/entity/TopicToTag.java

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira