Some Eclipse Foundation services are deprecated, or will be soon. Please ensure you've read this important communication.

Bug 315087

Summary: Support for literal casts is missing
Product: z_Archived Reporter: Timo Westkämper <timo.westkamper>
Component: EclipselinkAssignee: Nobody - feel free to take it <nobody>
Status: RESOLVED FIXED QA Contact:
Severity: enhancement    
Priority: P3 CC: dimo.velev, douglas.clarke, jamesssss, janne.kytomaki, tom.ware
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: Linux   
URL: http://wiki.eclipse.org/EclipseLink/Release/2.1.0/JPAQueryEnhancements#Query_Casting
Whiteboard:
Attachments:
Description Flags
adds support fo CAST and EXTRACT none

Description Timo Westkämper CLA 2010-05-31 10:32:56 EDT 
Build Identifier: 

Support for cast in JPQL is missing. It is available in HQL from Hibernate : 

cast(... as ...), where the second argument is the name of a Hibernate type, and extract(... from ...) if ANSI cast() and extract() is supported by the underlying database 

(source : http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html)

Could this be added as an extension? 

I tried with with EclipseLink JPA 2.1.0-SNAPSHOT

Reproducible: Always
Comment 1 Tom Ware CLA 2010-05-31 10:47:56 EDT 
How does the cast functionality compare to what is described here:

https://bugs.eclipse.org/bugs/show_bug.cgi?id=259266
http://wiki.eclipse.org/EclipseLink/Development/2.1/AdvancedJPA_Queries/DownCast
Comment 2 Timo Westkämper CLA 2010-05-31 10:51:51 EDT 
Down cast of entity aliases is something else.

The cast support I am referring to is support for literal conversion :

byte -> int
int -> string
string -> int
int -> date
etc.
Comment 3 Timo Westkämper CLA 2010-05-31 10:53:23 EDT 
Here is the cast documentation from MySQL : http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast
Comment 4 Doug Clarke CLA 2010-06-14 05:45:06 EDT 
Resolved in 2.1 - http://wiki.eclipse.org/EclipseLink/Release/2.1.0/JPAQueryEnhancements#Query_Casting

*** This bug has been marked as a duplicate of bug 259266 ***
Comment 5 Timo Westkämper CLA 2010-06-15 16:35:13 EDT 
Could I please have a JPQL example query for my case. In the presented link is only the following :

  select e from Employee e join TREAT(e.projects AS LargeProject) lp 
  where lp.budget = value

Can TREAT be used for literal casts?

e.g.

  select treat(e.name as int) from Employee e

or 

  select e.name from Employee e where treat(e.name as int) > 0

As the issue has been resolved I assume that this is possible now. If not then I'd like to see this issue reopened.
Comment 6 Doug Clarke CLA 2010-06-16 21:23:10 EDT 
I guess I jumped the gun closing your request. our TREAT...AS support allows you to cast with a mapped inheritance hierarchy. It does not do the primitive conversions you are looking for. sorry.

I also tried using the new FUNC support added in 2.1 that allows you to call database operations such as:

SELECT FUNC('TO_NUMBER', e.firstName) FROM Employee e

or

select e.name from Employee e where FUNC('TO_NUMBER', e.name) > 0

Unfortunately the current FUNC support does not appear to address the SQL structure of CAST( AS ). It uses a comma to separate the args. 

I am re-openening and will discuss with the developer of FUNC how we might call CAST.
Comment 7 Timo Westkämper CLA 2010-07-18 05:29:51 EDT 
Any progress on the issue?
Comment 8 Timo Westkämper CLA 2010-08-25 08:49:39 EDT 
I believe that are many other function usage cases that cannot be expressed with the func support.

Both CAST and CONVERT from MySQL cannot be used :

CAST(expr AS type)

CONVERT(expr, type)

With CAST, the AS and type causes problems and with CONVERT the type.

If I try for example func('CONVERT',columnX, date) then the JPQL treats date as a SQL column reference and doesn't treat it as a type symbol.

Maybe some template system would work better?

e.g.

FUNC('CONVERT($0, date)', columnX)
Comment 9 dimo.velev CLA 2011-08-12 07:18:30 EDT 
So what could be the workaround in the mean time if I need to convert a varchar to a  decimal?
Comment 10 Janne Kytömäki CLA 2012-02-24 08:43:40 EST 
I'm trying to build a Criteria API query that would group its results by the truncated date part of a SQL Server 2008 datetime2 datatype, i.e. in the effect of the SQL query:

select cast(created as Date), count(*) from table group by cast(created as Date)

AFAIK there is no other way of doing this in EclipseLink Criteria API except to use the CriteriaBuilder's function method, except that it doesn't support the CAST(.. AS ..) syntax.

(Another SQL server function that could be used is "dateadd(dd, datediff(dd,0, getDate()), 0)", but last time I checked it didn't work either with EclipseLink because of the constant "dd". An oldish thread about the subject: http://www.eclipse.org/forums/index.php/m/676855/)
Comment 11 James Sutherland CLA 2012-03-01 15:17:30 EST 
See patch in bug#305187

This adds support for the SQL JPQL operator (see sql() in Expressions and JpaCriteriaBuilder.fromExpression() for Criteria API).

i.e.
Select SQL("? as int", e.name) from Employee e

It allows for CAST and CONVERT and almost anything else to be done in JPQL and Criteria.

However, I am still looking at adding CAST, CONVERT and EXTRACT to JPQL as first class functions.
Comment 12 James Sutherland CLA 2012-04-04 15:03:14 EDT 
Added support for CAST and EXTRACT on Hermes.
Comment 13 James Sutherland CLA 2012-04-05 11:29:36 EDT 
Created attachment 213670 [details]
adds support fo CAST and EXTRACT
Comment 14 James Sutherland CLA 2012-04-11 10:47:21 EDT 
Fixed in 2.4
Comment 15 Eclipse Webmaster CLA 2022-06-09 10:23:08 EDT 
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink