[hibernate-issues] [Hibernate-JIRA] Created: (HHH-5752) revisionFieldName overide causes invalid column aliases on many-to-many relationships

[Adam Evans (JIRA)]

revisionFieldName overide causes invalid column aliases on many-to-many relationships
-------------------------------------------------------------------------------------

                 Key: HHH-5752
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5752
             Project: Hibernate Core
          Issue Type: Bug
          Components: envers
    Affects Versions: 3.5.6
         Environment: MySql5
            Reporter: Adam Evans


Had a issue which took a good while to track down.

When using AuditReader.find(Entity.clas, id, revision) to pull back a revision of a entity a SQL exception was given with a non unique column alias on entities with a many-to-many field, other entites without manytomany worked.

It looks like the query was generating a duplicate '_revision' field without using an column alias. '_revision' was set using the property overide 'org.hibernate.envers.revisionFieldName=_revision' .

Below is a copy of the failing query:

{code:sql}
SELECT _revision                AS col_0_0_,
       assoc_acti0_.activity_id AS col_0_1_,
       assoc_acti0_.facility_id AS col_0_2_,
       facility_v1_.id          AS col_1_0_,
       _revision                AS col_1_1_
FROM   assoc_activity_facility_versions assoc_acti0_
       CROSS JOIN facility_versions facility_v1_
WHERE  assoc_acti0_.facility_id = facility_v1_.id
       AND assoc_acti0_.activity_id = 1
       AND _revision = (SELECT MAX(_revision)
                        FROM   facility_versions facility_v2_
                        WHERE  _revision <= 1
                               AND facility_v1_.id = facility_v2_.id)
       AND _revision = (SELECT MAX(_revision)
                        FROM   assoc_activity_facility_versions assoc_acti3_
                        WHERE  _revision <= 1
                               AND assoc_acti0_.activity_id =
                                   assoc_acti3_.activity_id
                               AND assoc_acti0_.facility_id =
                                   assoc_acti3_.facility_id)
       AND _rev_type <> 2
       AND _rev_type <> 2  

{code}

After lots of debugging trying to create test cases based on my entity relationships which where passing without problem it occurred on the test project i'd not overiden 'org.hibernate.envers.revisionFieldName' . As soon as I set this property the test cases started failing. Removing this property from the original project, aliases are generated correctly and audited entities with many to many relatioships can now be retreivied. Below is the working sql generated with 'org.hibernate.envers.revisionFieldName' not set.

{code:sql}
SELECT assoc_acti0_.rev         AS col_0_0_,
       assoc_acti0_.activity_id AS col_0_1_,
       assoc_acti0_.category_id AS col_0_2_,
       category_a1_.id          AS col_1_0_,
       category_a1_.rev         AS col_1_1_
FROM   assoc_activity_category_aud assoc_acti0_
       CROSS JOIN category_aud category_a1_
WHERE  assoc_acti0_.category_id = category_a1_.id
       AND assoc_acti0_.activity_id = 1
       AND category_a1_.rev = (SELECT Max(category_a2_.rev)
                               FROM   category_aud category_a2_
                               WHERE  category_a2_.rev<=1
                                      AND category_a1_.id = category_a2_.id)
       AND assoc_acti0_.rev = (SELECT Max(assoc_acti3_.rev)
                               FROM   assoc_activity_category_aud assoc_acti3_
                               WHERE  assoc_acti3_.rev<=1
                                      AND assoc_acti0_.activity_id =
                                          assoc_acti3_.activity_id
                                      AND assoc_acti0_.category_id =
                                          assoc_acti3_.category_id)
       AND assoc_acti0_.revtype<>2
       AND category_a1_.revtype<>2  
{code}

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira