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

[Lukasz Antoniak (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5752?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=42383#action_42383 ] 

Lukasz Antoniak commented on HHH-5752:
--------------------------------------

Could you please post your entity mappings, Envers configuration and execution of {{AuditReader.find()}} method? I was unable to reproduce this issue. Setting {{"org.hibernate.envers.revisionFieldName"}} to {{"_revision"}} in {{org.hibernate.envers.test.integration.manytomany.BasicSet}} test case produces valid SQL query. Example:
{code} 
select
    setowninge0_._revision as col_0_0_,
    setowninge0_.referencing_id as col_0_1_,
    setowninge0_.references_id as col_0_2_,
    setowninge1_.id as col_1_0_,
    setowninge1_._revision as col_1_1_ 
from
    SetOwningEntity_SetOwnedEntity_AUD setowninge0_ cross 
join
    SetOwningEntity_AUD setowninge1_ 
where
    setowninge0_.referencing_id=setowninge1_.id 
    and setowninge0_.references_id=? 
    and setowninge1_._revision=(
        select
            max(setowninge2_._revision) 
        from
            SetOwningEntity_AUD setowninge2_ 
        where
            setowninge2_._revision<=? 
            and setowninge1_.id=setowninge2_.id
    ) 
    and setowninge0_._revision=(
        select
            max(setowninge3_._revision) 
        from
            SetOwningEntity_SetOwnedEntity_AUD setowninge3_ 
        where
            setowninge3_._revision<=? 
            and setowninge0_.references_id=setowninge3_.references_id 
            and setowninge0_.referencing_id=setowninge3_.referencing_id
    ) 
    and setowninge0_.REVTYPE<>? 
    and setowninge1_.REVTYPE<>? 
{code}

> 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