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

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