(Sorry for duplicate - site got stuck on "Submit")
I've been using Hibernate for a while, but i'm having a problem with something I usually take for granted.
(I've read the issues with Hibernate and distinct at
http://www.hibernate.org/117.html#A12)
We recently moved from MySQL to Oracle and this problem started. It has to do with the infamous distinct / outer join scenario - which I understand, but this is still weird.
For starters, this works fine in MySQL.
The example invovles to the mapped objects (below) with a simple one to many relationship between them.
When i run a simple query:
Code:
select
distinct s
from
Song s
inner join
s.systemUser u
left outer join
s.songLinks l
where
(
s.dataStatus in (
1
)
)
order by
s.artist asc,
s.title asc
or SQL query:
Code:
select
*
from
( select
distinct song0_.song_id as song1_2_,
song0_.system_user_id as system2_2_,
song0_.title as title2_,
song0_.artist as artist2_,
song0_.album as album2_,
song0_.genre as genre2_,
song0_.year as year2_,
song0_.sample_wav_path as sample8_2_,
song0_.title_wav_path as title9_2_,
song0_.artist_wav_path as artist10_2_,
song0_.album_wav_path as album11_2_,
song0_.original_file_name as original12_2_,
song0_.date_created as date13_2_,
song0_.data_status as data14_2_
from
song song0_
inner join
system_user systemuser1_
on song0_.system_user_id=systemuser1_.system_user_id
left outer join
song_link songlinks2_
on song0_.song_id=songlinks2_.song_id
where
song0_.data_status in (
1
)
order by
song0_.artist asc,
song0_.title asc )
where
rownum <= 25
I get duplicate Songs if the song has more than one SongLink in the code.
If I set up Hibernate logging and take the SQL query and run it on same Oracle -
there are no duplicates!
If I take the HQL query and run it using the Hibernate Tools console using the same JDBC driver -
there are no duplicates.
I need the outer join because there are more "where" items here, but I can reproduce the problem with this statement as well.
Only when run through the actual code there are duplicates. I'm baffled.
Any ideas? Is it because i'm using limits / paging?
Hibernate version: 3.3.1 GA
Mapping documents:Code:
<hibernate-mapping package="myapp">
<class name="Song" table="song">
<meta attribute="implement-equals">true</meta>
<meta attribute="generated-class">myapp.BaseSong</meta>
<id name="songId"
type="java.lang.Long"
column="song_id">
<meta attribute="use-in-equals">true</meta>
<generator class="native">
<param name="sequence">song_seq</param>
</generator>
</id>
<many-to-one name="systemUser"
class="SystemUser"
column="system_user_id"
cascade="lock,refresh"
not-null="true"
index="idx_song_system_user"
foreign-key="fk_song_system_user">
</many-to-one>
<property name="title"
column="title"
type="string"
length="200"
not-null="false">
</property>
<property name="artist"
column="artist"
type="string"
length="200"
not-null="false">
</property>
<property name="album"
column="album"
type="string"
length="200"
not-null="false">
</property>
<property name="genre"
column="genre"
type="string"
length="100"
not-null="false">
</property>
<property name="year"
column="year"
type="integer"
not-null="false">
</property>
<property name="sampleWavPath"
column="sample_wav_path"
type="string"
length="500"
not-null="false">
</property>
<property name="titleWavPath"
column="title_wav_path"
type="string"
length="500"
not-null="false">
</property>
<property name="artistWavPath"
column="artist_wav_path"
type="string"
length="500"
not-null="false">
</property>
<property name="albumWavPath"
column="album_wav_path"
type="string"
length="500"
not-null="false">
</property>
<property name="originalFileName"
column="original_file_name"
type="string"
length="500"
not-null="false"
index="idx_song_original_file_name">
</property>
<property name="dateCreated"
column="date_created"
type="timestamp"
not-null="true">
</property>
<property name="dataStatus"
column="data_status"
type="java.lang.Integer"
not-null="true"
index="idx_song_data_status">
</property>
<set name="songLinks" fetch="select" lazy="true" table="song_link" cascade="all-delete-orphan">
<key column="song_id" />
<one-to-many class="SongLink" />
</set>
</class>
<class name="SongLink" table="song_link">
<meta attribute="implement-equals">true</meta>
<meta attribute="generated-class">myapp.BaseSongLink</meta>
<id name="songLinkId"
type="java.lang.Long"
column="song_link_id">
<meta attribute="use-in-equals">true</meta>
<generator class="native">
<param name="sequence">song_link_seq</param>
</generator>
</id>
<many-to-one name="song"
class="Song"
column="song_id"
cascade="lock,refresh"
not-null="true"
index="idx_song_link_song"
foreign-key="fk_song_link_song">
</many-to-one>
<many-to-one name="systemUser"
class="SystemUser"
column="system_user_id"
cascade="lock,refresh"
not-null="true"
index="idx_song_link_system_user"
foreign-key="fk_song_link_system_user">
</many-to-one>
<property name="systemPath"
column="system_path"
type="string"
length="500"
not-null="false">
</property>
<property name="format"
column="format"
type="java.lang.Integer"
not-null="true"
index="idx_song_link_format">
</property>
<property name="contentType"
column="content_type"
type="string"
length="100"
not-null="true"
index="idx_song_link_content_type">
</property>
<property name="price"
column="price"
type="java.lang.Integer"
not-null="false">
</property>
<property name="dateCreated"
column="date_created"
type="timestamp"
not-null="true">
</property>
<property name="dataStatus"
column="data_status"
type="java.lang.Integer"
not-null="true"
index="idx_song_link_data_status">
</property>
</class>
</hibernate-mapping>
Oracle 11g
Login
FAQ
Search