Mailing-List: contact open-jpa-dev-help@incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: open-jpa-dev@incubator.apache.org
Message-ID: <15171761.1155771195215.JavaMail.jira@brutus>
Date: Wed, 16 Aug 2006 16:33:15 -0700 (PDT)
From: "David Wisneski (JIRA)" <jira@apache.org>
To: open-jpa-dev@incubator.apache.org
Subject: [jira] Created: (OPENJPA-22) locate & substring queries fail both
 db2 & derby, cannot use parameter markers in expression ?-?
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

locate & substring queries fail both db2 & derby, cannot use parameter markers in expression ?-?
------------------------------------------------------------------------------------------------

                 Key: OPENJPA-22
                 URL: http://issues.apache.org/jira/browse/OPENJPA-22
             Project: OpenJPA
          Issue Type: Bug
          Components: query
            Reporter: David Wisneski


EJB Q::   SELECT d.name FROM DeptBean d WHERE SUBSTRING(d.name,1,10) = 'Dept' 
                                                                                                                   
DB2 SQL error: SQLCODE: -417, SQLSTATE: 42609, SQLERRMC: null {prepstmnt 1662018320 SELECT t0.name FROM DeptBean t0
WHERE (SUBSTR(CAST((t0.name) AS VARCHAR(1000)), CAST(((? - ?)) AS INTEGER) + 1, CAST(((? + (? - ?))) AS INTEGER) - CAST(((?
- ?)) AS INTEGER)) = ?) [params=(long) 1, (int) 1, (long) 10, (long) 1, (int) 1, (long) 1, (int) 1, (String) Dept]}
[code=-417, state=42609]


EJB QL SELECT e.name FROM EmpBean e WHERE LOCATE('10',e.name,5) > 0 
            DB2 SQL error: SQLCODE: -417, SQLSTATE: 42609, SQLERRMC: null {prepstmnt 1217808534 SELECT t0.name FROM EmpBean t0 WHERE
(((LOCATE(CAST((?) AS VARCHAR(1000)), CAST((t0.name) AS VARCHAR(1000)), CAST(((? - ?)) AS INTEGER) + 1) - 1) + ?) >
?) [params=(String) 10, (long) 5, (int) 1, (int) 1, (long) 0]} [code=-417, state=42609]
 TEST411; 1 tuple


I am not sure why OpenJPA generates the sql expression ?-?.  The proper SQL should be 

SELECT t0.name FROM DeptBean t0  WHERE  substr (to.name, CAST ((?) as INTEGER),  CAST ((?) as INTEGER)   with parm values ( Integer 1,  Integer 10)

Although it helps query reuse to replace literals with parm markers and then pass the literal values as parm values,  this is not always 
best in a system like DB2 which uses distribution statistics and cost based optimization to compute the sql access path.  The better sql would 
simply be 
SELECT t0.name FROM DeptBean t0  WHERE  substr (to.name,1, 10)


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