1 Reply Latest reply on May 17, 2010 4:12 PM by Adam Warski

    Audit reader returns all revisions for collections in many to many instead of just the ones related to the parent revision

    Travers Snyman Newbie

      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


      public class One {

          @Column(name = "one_id")
          private Long oneId;

      // some properties....

      @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 .....




      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";

          @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 ,
      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