[hibernate-issues] [Hibernate-JIRA] Created: (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
--
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