[jboss-jira] [JBoss JIRA] Commented: (HIBERNATE-95) JPA query does not work on Derby when more than one entity has a column named DATE

[Felipe Leme (JIRA)]

    [ http://jira.jboss.com/jira/browse/HIBERNATE-95?page=comments#action_12412506 ] 
            
Felipe Leme commented on HIBERNATE-95:
--------------------------------------

Note sure if this is the right place to report it; I created another ticket on http://opensource.atlassian.com/projects/hibernate/browse/HHH-3278, just in case.

> JPA query does not work on Derby when more than one entity has a column named DATE
> ----------------------------------------------------------------------------------
>
>                 Key: HIBERNATE-95
>                 URL: http://jira.jboss.com/jira/browse/HIBERNATE-95
>             Project: Hibernate
>          Issue Type: Bug
>         Environment: Hibernate 3.2.6, Hibernate Annotations 3.3.0, Hibernate Entity Manager 3.3.1, Derby 10.4.1.3
>            Reporter: Felipe Leme
>         Assigned To: Steve Ebersole
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.jboss.com/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira