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

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}