[hibernate-issues] [Hibernate-JIRA] Created: (HHH-3278) JPA query does not work on Derby when more than one entity has a column named DATE

[Felipe Leme (JIRA)]

JPA query does not work on Derby when more than one entity has a column named DATE
----------------------------------------------------------------------------------

                 Key: HHH-3278
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3278
             Project: Hibernate3
          Issue Type: Bug
          Components: core
    Affects Versions: 3.2.6
         Environment: Hibernate 3.2.6, Hibernate Annotations 3.3.0, Hibernate Entity Manager 3.3.1, Derby 10.4.1.3
            Reporter: Felipe Leme


I have a relationship that is more like this (sorry, didn't have time to provide a simpler scenario):

- MP has a field/column called date
- MPHistory also have the same field
- there is a 1-N relationship between MP -> MPHistory
- the relationship has a @OrderBy("date") clause

When Hibernate starts using H2, everything works fine. But if I use Derby, I get the following exception:

Caused by: java.sql.SQLException: Column name 'DATE' appears more than once in the result of the query expression.
 
The bad SQL is the following:

Hibernate: select distinct agententit0_.id as id1_0_, mps1_.id as id0_1_, history2_.id as id3_2_, agententit0_.connected as connected1_0_, agententit0_.jmx_name as jmx4_1_0_, agententit0_.status as status1_0_, agententit0_.host_id as host10_1_0_, agententit0_.host as host1_0_, agententit0_.local as local1_0_, agententit0_.port as port1_0_, agententit0_.type as type1_0_, mps1_.date as date0_1_, mps1_.name as name0_1_, mps1_.value as value0_1_, mps1_.frequency_type as frequency5_0_1_, mps1_.is_full_mode as is6_0_1_, mps1_.is_historical as is7_0_1_, mps1_.is_normal_mode as is8_0_1_, mps1_.tolerance as tolerance0_1_, mps1_.is_summary_mode as is10_0_1_, mps1_.value_class as value11_0_1_, mps1_.info_id as info12_0_1_, mps1_.obj_id as obj13_0__, mps1_.id as id0__, history2_.date as date3_2_, history2_.value as value3_2_, history2_.value_class as value4_3_2_, history2_.mp_id as mp5_1__, history2_.id as id1__ from ggs_objects agententit0_ left outer join mps mps1_ on agententit0_.id=mps1_.obj_id left outer join mps_history history2_ on mps1_.id=history2_.mp_id where agententit0_.type in (108, 8) order by date asc

More specifically, the final 'order by date asc' should be 'order by history2_.date asc'.

Doing some debugging, I found the problem at org.hibernate.sql.Template, method isFunctionOrKeyword (line 304), which returns:

   "(".equals(nextToken) ||
KEYWORDS.contains(lcToken) ||
functionRegistry.hasFunction(lcToken) ||
dialect.getKeywords().contains(lcToken) ||
FUNCTION_KEYWORDS.contains(lcToken);
 
The 3rd check ( dialect.getKeywords().contains(lcToken) ) returns true for Derby, then the whole method returns true, which in turns does not prepend the "$PlaceHolder$ (history2_, in this case), in the query:

else if (
isIdentifier(token, dialect) &&
!isFunctionOrKeyword(lcToken, nextToken, dialect, functionRegistry) // <<<<< THIS IS THE ISSUE >>>>
) {
result.append(TEMPLATE)
.append('.')
.append( dialect.quote(token) );
}

For now, I just removed the @OrderBy from my query (it's not really important in my case), but it looks like the Template method logic is wrong in this case. 

PS: I initially open the same ticket at JBoss' site (http://jira.jboss.org/jira/browse/HIBERNATE-95) - my mistake, I guess I should have opened it here instead...


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