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

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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira