Bug 899515 (JBPAPP6-1221)

Summary: HHH-6655 TemplateRenderer and CriteriaQueryBuilder.TrimSpec behaves differently for DB2 causing querybuilder.trim test in JPA2 TCK to fail
Product: [JBoss] JBoss Enterprise Application Platform 6 Reporter: Madhumita Sadhukhan <msadhukh>
Component: HibernateAssignee: Brett Meyer <brmeyer>
Status: CLOSED NEXTRELEASE QA Contact:
Severity: high Docs Contact:
Priority: high    
Version: 6.0.0CC: brian.stansberry, brmeyer, msadhukh, pslavice, stliu, theute
Target Milestone: ---   
Target Release: EAP 6.1.0   
Hardware: Unspecified   
OS: Unspecified   
URL: http://jira.jboss.org/jira/browse/JBPAPP6-1221
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2012-10-11 16:21:23 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:
Bug Depends On:    
Bug Blocks: 900612    
Attachments:
Description Flags
Client_trim_from_standalone_DB2.jtr
none
Client_trim_from_standalone_postgresql.jtr
none
Client.java none

Description Madhumita Sadhukhan 2011-09-14 12:47:56 UTC 
project_key: JBPAPP6

https://hibernate.onjira.com/browse/HHH-6655

Failing test:com.sun.ts.tests.ejb30.persistence.criteriaapi.querybuilder.Client
testname=trim

For DB2 a warning message is generated by TempateRenderer which is not seen for other databases:
WARN: HHH00174:Function template anticipated 3 arguments, but 2 arguments encountered

Also the query created is different in case of DB2:
Hibernate: select replace(replace(ltrim(rtrim(replace(replace(customer0_.NAME,' ','${space}$'),?,' '))),' ',?),'${space}$',' ') as col_0_0_ from CUSTOMER_TABLE customer0_ where customer0_.NAME=? fetch first 2 rows only

and finally it fails because of above warning:

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: DB2 SQL Error: SQLCODE=-313, SQLSTATE=07001
 
For other DBs same code generates following query and test passes:

Hibernate: select trim(BOTH ? from customer0_.NAME) as col_0_0_ from CUSTOMER_TABLE customer0_ where customer0_.NAME=? limit ?

Attaching log and testcode
Comment 1 Madhumita Sadhukhan 2011-09-14 12:53:48 UTC 
Attachment: Added: Client_trim_from_standalone_DB2.jtr
Attachment: Added: Client_trim_from_standalone_postgresql.jtr
Attachment: Added: Client.java
Comment 2 Madhumita Sadhukhan 2011-09-14 12:55:21 UTC 
Strong can you please take a look at this? assigning to you.
Comment 3 Strong Liu 2011-09-14 14:47:16 UTC 
may relates to this one https://hibernate.onjira.com/browse/HHH-1949
Comment 4 Strong Liu 2011-09-14 17:15:39 UTC 
http://opensource.atlassian.com/projects/hibernate/browse/HHH-6655
Comment 5 Rajesh Rajasekaran 2012-05-01 17:05:30 UTC 
Link: Added: This issue is a dependency of JBPAPP-8827
Comment 6 Strong Liu 2012-05-09 02:35:42 UTC 
Affects: Added: Release Notes
Comment 7 Strong Liu 2012-05-10 04:03:15 UTC 
Affects: Removed: Release Notes
Comment 8 Rajesh Rajasekaran 2012-06-06 22:26:14 UTC 
Link: Added: This issue is a dependency of JBPAPP-9292
Comment 9 Rajesh Rajasekaran 2012-06-06 22:26:28 UTC 
Link: Removed: This issue is a dependency of JBPAPP-8827
Comment 10 Brian Stansberry 2012-08-14 19:01:05 UTC 
https://hibernate.onjira.com/browse/HHH-6280
Comment 11 Brett Meyer 2012-09-25 14:17:00 UTC 
After re-running the test on DB2 9, HHH-6280 appears to be no longer relevant.  I updated HHH-6655 with the real issue.
Comment 12 Brett Meyer 2012-10-09 15:54:02 UTC 
HHH-6655 is resolved.  Unless there's a critical reason to patch this in EAP 6.0.1, I'm holding off until the component upgrade in 6.1.0.
Comment 13 Brett Meyer 2012-10-11 16:21:23 UTC 
Release Notes Docs Status: Added: Documented as Resolved Issue
Release Notes Text: Added: For DB2 databases, the "trim" function had problems using parameter values as arguments.  Forcing Hibernate to default to using literal values in TrimFunction corrected the issue.
Comment 14 Anne-Louise Tangring 2012-11-13 20:46:40 UTC 
Release Notes Docs Status: Removed: Documented as Resolved Issue 
Release Notes Text: Removed: For DB2 databases, the "trim" function had problems using parameter values as arguments.  Forcing Hibernate to default to using literal values in TrimFunction corrected the issue. 
Docs QE Status: Removed: NEW