Audit reader returns all revisions for collections in many to many instead of just the ones related to the parent revision
I have an object with many to many collections. Only the parent side is audited. On insert, delete, update, I see the correct entries in the audit tables, but if I use AuditReader.find to get the audit for a specific revision it seems like the previous versions are also returned. See the below example and use cases
@Audited
@Entity
public class One {
@Id
@Column(name = "one_id")
private Long oneId;
// some properties....
@ManyToMany
@Fetch(FetchMode.SUBSELECT)
@JoinTable(name = "one_to_many",
joinColumns = {@JoinColumn(name = "one_id")},
inverseJoinColumns = {@JoinColumn(name = "many_id")})
@Audited(targetAuditMode= RelationTargetAuditMode.NOT_AUDITED)
private Set<Many> many;
//getters and setters .....
}
@Entity
public class Many {
// Entity Name Constants
protected static final String TABLE_NAME = "segment";
protected static final String SEGMENT_NAME_COLUMN_NAME = "segment_name";
protected static final String SUB_SEGMENT_NAME_COLUMN_NAME = "sub_segment_name";
@Id
@Column(name = "many_id")
private Long manyId;
// some properties and getters and setters.....
}
use case 1
insert row into "One" with 3 "Many" objects
AuditReader.find with revision 1 returns the One object with the 3 many objects attached
In the audit table for the many, the 3 rows are there with revision one and type ADD
use case 2
update "One" by removing one of the Many objects
AuditReader.find with revision 2 still returns 3 "Many" objects attached allthough only 2 was expected.
the join table one_to_many actually has the correct entries per revision ie 3 for revision 1 and
2 for revision 2
This led me to believe the Audit reader find actually queries for previous revisions as well. I confirmed
this by looking at the generated hibernate SQL query (something similar to the below. I changed the aliases from the
hibernate output ones to be more readable):
select tab1.revision_id,
tab1.one_id ,
tab1.many_id_,
tab2.many_id
from one_to_many tab1
cross join many tab2 where
and tab1.many_id=tab2.many_id
and tab1.one_id=? -- id of the one object for which we want the audit entry
and tab1.revision_id=(select max(tab3.revision_id)
from one_to_many tab3
where tab3.revision_id <= ? -- the revision in this case 2 corresponding to use case 2 above
and tab1.one_id=tab3.one_id
and tab1.many_id=tab3.many_id)
and tab1.revision_type <> ?
The question here is in the clause above where tab3.revision_id <= ? ,
I would have expected only an equals to get the collections only for the revision in question
Is this expected behaviour or should I do something else in the mapping ? I also tried this with an AuditReader query by adding
a restriction for the revision id but get the same results.
I am using envers packaged with hibernate 3.5.1
