[hibernate-issues] [Hibernate-JIRA] Created: (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}
--
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira