Community
Participate
Working Groups
Build Identifier: 2.0.2.v20100323-r6872 Please refer to the forum posting http://myforums.oracle.com/jive3/thread.jspa?threadID=651978&tstart=0 When I run the following JPQL SELECT COUNT(o) FROM Equipment o WHERE o.id is not null AND Exists ( Select chars0var from o.characteristics chars0var where (chars0var.name = :pCharName0Param AND UPPER(SUBSTRING(chars0var.value, 1, LENGTH(:pCharValue0Param))) = UPPER(:pCharValue0Param) )) AND (o.objectState = oracle.communications.inventory.api.ObjectState.ACTIVE OR o.objectState = oracle.communications.inventory.api.ObjectState.INACTIVE) where I have two parameters 1. pCharValue0Param is a String with value "a" 2. pCharName0Param a a String with value "vendor" I get the following QueryException Caused by: Exception EclipseLink-6078 (Eclipse Persistence Services - 2.0.2.v20100323-r6872): org.eclipse.persistence.exceptions.QueryException Exception Description: The class of the argument for the object comparison is incorrect. Expression: null Mapping: [org.eclipse.persistence.mappings.OneToManyMappingcharacteristics] Argument: [a] Query: ReportQuery(referenceClass=EquipmentDAO sql="SELECT COUNT(t0.ENTITYID) FROM Equipment t0 WHERE (((NOT ((t0.ID IS NULL)) AND EXISTS (SELECT ? FROM Equipment t2, EQUIPMENT_CHAR t1 WHERE (((((t1.NAME = ?) AND (UPPER(SUBSTR(t1.VALUE, ?, LENGTH(?))) = UPPER(?))) AND (t0.ENTITYID = t2.ENTITYID)) AND (t2.ENTITYCLASS = ?)) AND ((t1.charOwner = t2.ENTITYID) AND (t1.ENTITYCLASS = ?)))) ) AND ((t0.OBJECTSTATE = ?) OR (t0.OBJECTSTATE = ?))) AND (t0.ENTITYCLASS = ?))") The SQL looks right based on the JPQL. But the exception is unexpected. Note, that when I replace the substring in my subquery with =, the JPQL runs fine. Similarly if I add similar substring in the main query, it works fine. the Reproducible: Always Steps to Reproduce: 1. Run a subquery like given above. 2. 3.
Setting target and priority. See the following page for details about these fields: http://wiki.eclipse.org/EclipseLink/Development/Bugs/Guidelines
Problem seems to be isolated to the "UPPER(SUBSTRING(chars0var.value, 1, LENGTH(:pCharValue0Param))) = UPPER(:pCharValue0Param)" in the subquery. Validation is attempting to get the type from the functions, but is going to its root expression builder - so the problem is in JPQL parsing (not setting the function type as string) or in expression validation itself as upper should know it returns a string type. Only workaround is to not put function expressions that use parameters on both sides of an operator in an exists subquery. This particular query could be simplified if upper was called on the parameter before being passed in,and like was used instead: chars0var.value LIKE :pCharValue0Param where the parameter is set as: query.setParameter("pCharValue0Param", (stringValue.toUpperCase() +"%"));
I am running into this issue again with the following JPQL SELECT COUNT(o) FROM CustomObject o WHERE Exists ( Select chars0var from o.characteristics chars0var where (chars0var.name = :pCharName0Param AND ( FUNC(to_number, chars0var.value ) > FUNC(to_number, :pCharValue0Param ) ))) AND (o.objectState = oracle.communications.inventory.api.ObjectState.ACTIVE OR o.objectState = oracle.communications.inventory.api.ObjectState.INACTIVE) with the following error Exception Description: The class of the argument for the object comparison is incorrect. Expression: [null] Mapping: [org.eclipse.persistence.mappings.OneToManyMapping[characteristics]] Argument: [3] Query: ReportQuery(referenceClass=CustomObjectDAO sql="SELECT COUNT(t0.ENTITYID) FROM CustomObject t0 WHERE ((EXISTS (SELECT ? FROM CustomObject t2, CUSTOMOBJECT_CHAR t1 WHERE (((((t1.NAME = ?) AND (to_number(t1.VALUE) > to_number(?))) AND (t0.ENTITYID = t2.ENTITYID)) AND (t2.ENTITYCLASS = ?)) AND ((t1.CHAROWNER = t2.ENTITYID) AND (t1.ENTITYCLASS = ?)))) AND ((t0.OBJECTSTATE = ?) OR (t0.OBJECTSTATE = ?))) AND (t0.ENTITYCLASS = ?))") I need to compare the values as numbers this time. I noticed that this bug has not been addressed yet. Can you suggest any workarounds?
Do you need to call FUNC(to_number, :pCharValue0Param ) or can you just pass in a number instead? ie: just use :pCharValue0Param and query.setParameter("pCharValue0Param", Integer.parseInt(stringValue));
I tried your suggestion of not doing a FUNC on the value and that worked. Thanks!
The issue is the, from o.characteristics This create a sub query that is not based on an ExpressionBuilder, this confuses the parameter expression into thinking it has a local base, but it doesn't. Just need to ensure the builder is always the base of parameter expressions.
So the easiest workaround is, from Characteristic chars0var where chars0var.equipment = 0
See, Bug#314025
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
