[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5172) Invalid alias when referencing two different tables in formula tags

[Stephen Visser (JIRA)]

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

Stephen Visser commented on HHH-5172:
-------------------------------------

Here are some related but not identical issues:
HB-765
HB-1558
HB-1530

> Invalid alias when referencing two different tables in formula tags
> -------------------------------------------------------------------
>
>                 Key: HHH-5172
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5172
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.5.0-Final
>         Environment: mappings
>            Reporter: Stephen Visser
>
> I have a complicated Hibernate entity which is defined as the join between two tables.  It looks like the following:
> {quote}
> <hibernate-mapping>
>     <class name="User" table="PERSONS">
>         <id name="personID" column="PERSON_ID"/>
>         <property name="lmsUserName" column="WEB_USERNAME" />
>         <property name="userName" insert="false" update="false" unique="true">
>             <formula>
>                 coalesce(lower(WEB_USERNAME), lower(USERNAME))
>              </formula>
>         </property>
>         <join table="APPLICATION_USERS" optional="true">
>              <key column="PERSON_ID"/>
>              <property name="sasUserName" column="USERNAME"/>
>         </join>
>     </class>
> </hibernate-mapping>
> {quote}
> We can find by criteria as shown below:
> {quote}
>    final Criteria hibernateCriteria = session.createCriteria(User.class).add(Restrictions.eq("userName", "EXAMPLE_USERNAME"));
>    final User retrievedObject = (User) hibernateCriteria.uniqueResult();
> {quote}
> This results in the following SQL query being generated:
> {quote}
> select userimpl0_.PERSON_ID as PERSON1_2_0_, userimpl0_.WEB_USERNAME as WEB5_2_0_, userimpl0_2_.USERNAME as USERNAME4_0_, coalesce(lower(userimpl0_.WEB_USERNAME), lower(userimpl0_.USERNAME)) as formula0_0_, 
> from PERSONS userimpl0_ left outer join APPLICATION_USERS userimpl0_2_ on userimpl0_.PERSON_ID=userimpl0_2_.PERSON_ID 
> where coalesce(lower(userimpl0_.WEB_USERNAME), lower(userimpl0_.USERNAME)) = 'EXAMPLE_USERNAME' 
> {quote}
> Due to the nature of our legacy data system, we are trying to find the userName in one of two columns in the DB.  A user may have one or both of the user names specified.  We want to query by userName in order to avoid making multiple queries.  To do this, we make use of the coalesce function as shown above.
> The query created by retrieving by userName prepends both WEB_USERNAME and USERNAME in the coalesce function with a single alias.  In fact, I need WEB_USERNAME to be prepended with the alias of the PERSONS table (this is done correctly) and USERNAME to be prepended by the alias for the APPLICATION_USERS table (this is where the problem lies).
> Ideally, I think it's reasonable to have one of two options:
> 1) Be able to 'escape' the automatic prepend of table aliases.  This would work fine for me as I have no duplicate columns in this example.  But I don't think that it is a very universal solution...
> 2) Be able to reference the alias for a given table using a well-defined syntax (i.e. {PERSONS}.USERNAME )
> I've written a little bit here: https://forum.hibernate.org/viewtopic.php?t=1004224

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