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

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 Assignee: Gail Badner 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/red... https://sourceforge.net/p/topicindex/code/383/tree/trunk/src/main/com/red... https://sourceforge.net/p/topicindex/code/383/tree/trunk/src/main/com/red...