You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "David Wisneski (JIRA)" <ji...@apache.org> on 2006/08/17 01:33:15 UTC
[jira] Created: (OPENJPA-22) locate & substring queries fail both
db2 & derby, cannot use parameter markers in expression ?-?
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
[jira] Resolved: (OPENJPA-22) locate & substring queries fail both
db2 & derby, cannot use parameter markers in expression ?-?
Posted by "David Wisneski (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (OPENJPA-22) locate & substring queries fail both
db2 & derby, cannot use parameter markers in expression ?-?
Posted by "Patrick Linskey (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-22?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Patrick Linskey updated OPENJPA-22:
-----------------------------------
Fix Version/s: 0.9.7
> 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
> 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.