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

Bug 327848

Summary:	Unexpected QueryException 6078 when doing substring in a subquery
Product:	z_Archived	Reporter:	Poonam Kapur <poonam.kapur>
Component:	Eclipselink	Assignee:	James Sutherland <jamesssss>
Status:	RESOLVED FIXED	QA Contact:
Severity:	normal
Priority:	P2	CC:	christopher.delahunt, jamesssss, tom.ware
Version:	unspecified
Target Milestone:	---
Hardware:	PC
OS:	Windows XP
Whiteboard:

Description Poonam Kapur

2010-10-14 23:31:15 EDT

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.

Comment 1 Tom Ware

2010-11-01 11:27:32 EDT

Setting target and priority.  See the following page for details about these fields:

http://wiki.eclipse.org/EclipseLink/Development/Bugs/Guidelines

Comment 2 Chris Delahunt

2011-03-29 11:19:24 EDT

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() +"%"));

Comment 3 Poonam Kapur

2011-10-25 14:20:57 EDT

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?

Comment 4 Chris Delahunt

2011-10-25 15:10:17 EDT

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));

Comment 5 Poonam Kapur

2011-10-25 15:34:26 EDT

I tried your suggestion of not doing a FUNC on the value and that worked. Thanks!

Comment 6 James Sutherland

2011-11-09 13:56:02 EST

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.

Comment 7 James Sutherland

2011-11-09 13:57:53 EST

So the easiest workaround is,

from Characteristic chars0var where chars0var.equipment = 0

Comment 8 James Sutherland

2011-11-16 11:22:56 EST

See, Bug#314025

Comment 9 Eclipse Webmaster

2022-06-09 10:09:09 EDT

The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink