You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "George Hongell (JIRA)" <ji...@apache.org> on 2006/09/19 03:09:22 UTC
[jira] Created: (OPENJPA-50) bad sql pushdown, cast changes
datatype
bad sql pushdown, cast changes datatype
---------------------------------------
Key: OPENJPA-50
URL: http://issues.apache.org/jira/browse/OPENJPA-50
Project: OpenJPA
Issue Type: Bug
Components: query
Environment: Windows xp, db2, derby
Reporter: George Hongell
444 - bad sql pushdown, cast changes datatype
TEST444; select e from EmpBean e where e.salary > 5 and abs(e.salary) > 12
28203 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 295440796> [0 ms] executing prepstmnt 1712481810 SELECT t0.empid, t0.bonus, t1.deptno, t1.budget, t1.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t2.street, t2.city, t2.state, t2.zip, t0.isManager, t0.name, t0.salary, t3.street, t3.city, t3.state, t3.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno LEFT OUTER JOIN AddressBean t2 ON t0.home_street = t2.street LEFT OUTER JOIN AddressBean t3 ON t0.work_street = t3.street WHERE (CAST(t0.salary AS DOUBLE) > CAST(? AS DOUBLE) AND CAST(ABS(t0.salary) AS BIGINT) > CAST(? AS BIGINT)) [params=(long) 5, (long) 12]
select t0.empid, t0.salary FROM EmpBean t0 LEFT OUTER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno WHERE (CAST(t0.salary AS DOUBLE) > ?) AND (CAST(ABS(t0.salary) AS BIGINT) > ?) {long 5, long 12}
why CAST(ABS(t0.salary) AS BIGINT)?????????
select t0.empid, t0.salary FROM EmpBean t0 WHERE (CAST(t0.salary AS DOUBLE) > ?) AND (CAST(ABS(t0.salary) AS BIGINT) > ?) {long 5, long 12}
s/b
select t0.empid, t0.salary FROM EmpBean t0 LEFT OUTER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno WHERE (CAST(t0.salary AS DOUBLE) > ?) AND (CAST(ABS(t0.salary) AS DOUBLE) > ?) {long 5, long 12}
[ FAILED 444- bucket = fvtfull, query = select e from EmpBean e where e.salary > 5 and abs(e.salary) > 12 :
EXPECTED(
TEST444; select e from EmpBean e where e.salary > 5 and abs(e.salary) > 12
EmpBean
~~~~~~~
[1]
[2]
[3]
TEST444; 3 tuples)
ACTUAL(
TEST444; select e from EmpBean e where e.salary > 5 and abs(e.salary) > 12
EmpBean
~~~~~~~
[2]
[3]
TEST444; 2 tuples) ]
--
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] Updated: (OPENJPA-50) bad sql pushdown, cast changes
datatype
Posted by "George Hongell (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OPENJPA-50?page=all ]
George Hongell updated OPENJPA-50:
----------------------------------
Attachment: failureEntities.jar
entity java files and derby ddl script to use to recreate this issue
> bad sql pushdown, cast changes datatype
> ---------------------------------------
>
> Key: OPENJPA-50
> URL: http://issues.apache.org/jira/browse/OPENJPA-50
> Project: OpenJPA
> Issue Type: Bug
> Components: query
> Environment: Windows xp, db2, derby
> Reporter: George Hongell
> Attachments: failureEntities.jar
>
>
> 444 - bad sql pushdown, cast changes datatype
> TEST444; select e from EmpBean e where e.salary > 5 and abs(e.salary) > 12
> 28203 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 295440796> [0 ms] executing prepstmnt 1712481810 SELECT t0.empid, t0.bonus, t1.deptno, t1.budget, t1.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t2.street, t2.city, t2.state, t2.zip, t0.isManager, t0.name, t0.salary, t3.street, t3.city, t3.state, t3.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno LEFT OUTER JOIN AddressBean t2 ON t0.home_street = t2.street LEFT OUTER JOIN AddressBean t3 ON t0.work_street = t3.street WHERE (CAST(t0.salary AS DOUBLE) > CAST(? AS DOUBLE) AND CAST(ABS(t0.salary) AS BIGINT) > CAST(? AS BIGINT)) [params=(long) 5, (long) 12]
> select t0.empid, t0.salary FROM EmpBean t0 LEFT OUTER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno WHERE (CAST(t0.salary AS DOUBLE) > ?) AND (CAST(ABS(t0.salary) AS BIGINT) > ?) {long 5, long 12}
> why CAST(ABS(t0.salary) AS BIGINT)?????????
> select t0.empid, t0.salary FROM EmpBean t0 WHERE (CAST(t0.salary AS DOUBLE) > ?) AND (CAST(ABS(t0.salary) AS BIGINT) > ?) {long 5, long 12}
> s/b
> select t0.empid, t0.salary FROM EmpBean t0 LEFT OUTER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno WHERE (CAST(t0.salary AS DOUBLE) > ?) AND (CAST(ABS(t0.salary) AS DOUBLE) > ?) {long 5, long 12}
> [ FAILED 444- bucket = fvtfull, query = select e from EmpBean e where e.salary > 5 and abs(e.salary) > 12 :
> EXPECTED(
> TEST444; select e from EmpBean e where e.salary > 5 and abs(e.salary) > 12
> EmpBean
> ~~~~~~~
> [1]
> [2]
> [3]
> TEST444; 3 tuples)
> ACTUAL(
> TEST444; select e from EmpBean e where e.salary > 5 and abs(e.salary) > 12
> EmpBean
> ~~~~~~~
> [2]
> [3]
> TEST444; 2 tuples) ]
--
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] Commented: (OPENJPA-50) bad sql pushdown, cast changes
datatype
Posted by "George Hongell (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OPENJPA-50?page=comments#action_12436062 ]
George Hongell commented on OPENJPA-50:
---------------------------------------
removed testcase references.
run on version 443432.
EJBQL:
select e from EmpBean e where e.salary > 5 and abs(e.salary) > 12
OPENJPA ERROR OR SQL PUSHDOWN:
returns 2 entitie instances but should return 3
28203 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 295440796> [0 ms] executing prepstmnt 1712481810 SELECT t0.empid, t0.bonus, t1.deptno, t1.budget, t1.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t2.street, t2.city, t2.state, t2.zip, t0.isManager, t0.name, t0.salary, t3.street, t3.city, t3.state, t3.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno LEFT OUTER JOIN AddressBean t2 ON t0.home_street = t2.street LEFT OUTER JOIN AddressBean t3 ON t0.work_street = t3.street WHERE (CAST(t0.salary AS DOUBLE) > CAST(? AS DOUBLE) AND CAST(ABS(t0.salary) AS BIGINT) > CAST(? AS BIGINT)) [params=(long) 5, (long) 12]
select t0.empid, t0.salary FROM EmpBean t0 LEFT OUTER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno WHERE (CAST(t0.salary AS DOUBLE) > ?) AND (CAST(ABS(t0.salary) AS BIGINT) > ?) {long 5, long 12}
why CAST(ABS(t0.salary) AS BIGINT)?????????
SUGGESTED SQL PUSHDOWN:
select t0.empid, t0.bonus, t1.deptno, t1.budget, t1.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t2.street, t2.city, t2.state, t2.zip, t0.isManager, t0.name, t0.salary, t3.street, t3.city, t3.state, t3.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno LEFT OUTER JOIN AddressBean t2 ON t0.home_street = t2.street LEFT OUTER JOIN AddressBean t3 ON t0.work_street = t3.street WHERE (CAST(t0.salary AS DOUBLE) > ?) AND (CAST(ABS(t0.salary) AS DOUBLE) > ?) {long 5, long 12}
> bad sql pushdown, cast changes datatype
> ---------------------------------------
>
> Key: OPENJPA-50
> URL: http://issues.apache.org/jira/browse/OPENJPA-50
> Project: OpenJPA
> Issue Type: Bug
> Components: query
> Environment: Windows xp, db2, derby
> Reporter: George Hongell
>
> 444 - bad sql pushdown, cast changes datatype
> TEST444; select e from EmpBean e where e.salary > 5 and abs(e.salary) > 12
> 28203 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 295440796> [0 ms] executing prepstmnt 1712481810 SELECT t0.empid, t0.bonus, t1.deptno, t1.budget, t1.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t2.street, t2.city, t2.state, t2.zip, t0.isManager, t0.name, t0.salary, t3.street, t3.city, t3.state, t3.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno LEFT OUTER JOIN AddressBean t2 ON t0.home_street = t2.street LEFT OUTER JOIN AddressBean t3 ON t0.work_street = t3.street WHERE (CAST(t0.salary AS DOUBLE) > CAST(? AS DOUBLE) AND CAST(ABS(t0.salary) AS BIGINT) > CAST(? AS BIGINT)) [params=(long) 5, (long) 12]
> select t0.empid, t0.salary FROM EmpBean t0 LEFT OUTER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno WHERE (CAST(t0.salary AS DOUBLE) > ?) AND (CAST(ABS(t0.salary) AS BIGINT) > ?) {long 5, long 12}
> why CAST(ABS(t0.salary) AS BIGINT)?????????
> select t0.empid, t0.salary FROM EmpBean t0 WHERE (CAST(t0.salary AS DOUBLE) > ?) AND (CAST(ABS(t0.salary) AS BIGINT) > ?) {long 5, long 12}
> s/b
> select t0.empid, t0.salary FROM EmpBean t0 LEFT OUTER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno WHERE (CAST(t0.salary AS DOUBLE) > ?) AND (CAST(ABS(t0.salary) AS DOUBLE) > ?) {long 5, long 12}
> [ FAILED 444- bucket = fvtfull, query = select e from EmpBean e where e.salary > 5 and abs(e.salary) > 12 :
> EXPECTED(
> TEST444; select e from EmpBean e where e.salary > 5 and abs(e.salary) > 12
> EmpBean
> ~~~~~~~
> [1]
> [2]
> [3]
> TEST444; 3 tuples)
> ACTUAL(
> TEST444; select e from EmpBean e where e.salary > 5 and abs(e.salary) > 12
> EmpBean
> ~~~~~~~
> [2]
> [3]
> TEST444; 2 tuples) ]
--
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] Closed: (OPENJPA-50) bad sql pushdown, cast changes datatype
Posted by "David Wisneski (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-50?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
David Wisneski closed OPENJPA-50.
---------------------------------
Resolution: Fixed
fixed in recent code
> bad sql pushdown, cast changes datatype
> ---------------------------------------
>
> Key: OPENJPA-50
> URL: https://issues.apache.org/jira/browse/OPENJPA-50
> Project: OpenJPA
> Issue Type: Bug
> Components: query
> Environment: Windows xp, db2, derby
> Reporter: George Hongell
> Attachments: failureEntities.jar
>
>
> 444 - bad sql pushdown, cast changes datatype
> TEST444; select e from EmpBean e where e.salary > 5 and abs(e.salary) > 12
> 28203 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 295440796> [0 ms] executing prepstmnt 1712481810 SELECT t0.empid, t0.bonus, t1.deptno, t1.budget, t1.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t2.street, t2.city, t2.state, t2.zip, t0.isManager, t0.name, t0.salary, t3.street, t3.city, t3.state, t3.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno LEFT OUTER JOIN AddressBean t2 ON t0.home_street = t2.street LEFT OUTER JOIN AddressBean t3 ON t0.work_street = t3.street WHERE (CAST(t0.salary AS DOUBLE) > CAST(? AS DOUBLE) AND CAST(ABS(t0.salary) AS BIGINT) > CAST(? AS BIGINT)) [params=(long) 5, (long) 12]
> select t0.empid, t0.salary FROM EmpBean t0 LEFT OUTER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno WHERE (CAST(t0.salary AS DOUBLE) > ?) AND (CAST(ABS(t0.salary) AS BIGINT) > ?) {long 5, long 12}
> why CAST(ABS(t0.salary) AS BIGINT)?????????
> select t0.empid, t0.salary FROM EmpBean t0 WHERE (CAST(t0.salary AS DOUBLE) > ?) AND (CAST(ABS(t0.salary) AS BIGINT) > ?) {long 5, long 12}
> s/b
> select t0.empid, t0.salary FROM EmpBean t0 LEFT OUTER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno WHERE (CAST(t0.salary AS DOUBLE) > ?) AND (CAST(ABS(t0.salary) AS DOUBLE) > ?) {long 5, long 12}
> [ FAILED 444- bucket = fvtfull, query = select e from EmpBean e where e.salary > 5 and abs(e.salary) > 12 :
> EXPECTED(
> TEST444; select e from EmpBean e where e.salary > 5 and abs(e.salary) > 12
> EmpBean
> ~~~~~~~
> [1]
> [2]
> [3]
> TEST444; 3 tuples)
> ACTUAL(
> TEST444; select e from EmpBean e where e.salary > 5 and abs(e.salary) > 12
> EmpBean
> ~~~~~~~
> [2]
> [3]
> TEST444; 2 tuples) ]
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.