openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "David Wisneski (JIRA)" <j...@apache.org>
Subject [jira] Created: (OPENJPA-22) locate & substring queries fail both db2 & derby, cannot use parameter markers in expression ?-?
Date Wed, 16 Aug 2006 23:33:15 GMT
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

        

Mime
View raw message