[
https://issues.apache.org/jira/browse/OPENJPA-22?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
David Wisneski resolved OPENJPA-22.
-----------------------------------
Resolution: Fixed
Assignee: David Wisneski
fixed in r516624
> locate & substring queries fail both db2 & derby, cannot use parameter
> markers in expression ?-?
> ------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-22
> URL: https://issues.apache.org/jira/browse/OPENJPA-22
> Project: OpenJPA
> Issue Type: Bug
> Components: query
> Reporter: David Wisneski
> Assigned To: David Wisneski
> Fix For: 0.9.7
>
>
> 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.
-
You can reply to this email to add a comment to the issue online.
