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

[Matthew Casperson (JIRA)]

Hibernate query unexpectedly returns duplicate results
------------------------------------------------------

                 Key: HHH-6824
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6824
             Project: Hibernate Core
          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