openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Donald Woods (JIRA)" <j...@apache.org>
Subject [jira] Commented: (OPENJPA-1584) PreparedQuery gives wrong result if query has subquery and parameters are used in both main select and subselect
Date Thu, 22 Jul 2010 17:20:49 GMT

    [ https://issues.apache.org/jira/browse/OPENJPA-1584?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12891251#action_12891251
] 

Donald Woods commented on OPENJPA-1584:
---------------------------------------

Additional tests are always welcomed :-)


> PreparedQuery gives wrong result if query has subquery and parameters are used in both
main select and subselect
> ----------------------------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-1584
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-1584
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: performance
>    Affects Versions: 2.0.0
>            Reporter: Catalina Wei
>            Assignee: Catalina Wei
>         Attachments: OpenJPA-2.0.0_OJ1584.patch
>
>
> a back-to-back of the following JPQL query providing different set of parameter values,

> the second execution gives wrong answer.
>  "select o from OrderJPA o where o.OrderId in (select max(o1.OrderId) from OrderJPA o1
where ((o1.CustomerId = :customerId) and (o1.DistrictId = :districtId) and (o1.WarehouseId
= :warehouseId))) and (o.CustomerId = :customerId) and (o.DistrictId = :districtId) and (o.WarehouseId
= :warehouseId)"
> SQL trace shown the first time query execution, let say customerId=339, districtId=3,
warehouseId=23, then query returns 1 row:
> the SQL trace looked fine:
> [3/16/10 17:40:36:831 CDT] 00000045 OpenJPA       3   openjpa.jdbc.SQL: Trace: <t
241897067, conn 1981117973> executing prepstmnt 1547852866 SELECT t0.O_D_ID, t0.O_ID, t0.O_W_ID,
t0.VERSION, t0.O_ALL_LOCAL, t0.O_CARRIER_ID, t0.O_C_ID, t0.O_ENTRY_D, t0.O_OL_CNT FROM ORDERS
t0 WHERE (t0.O_ID IN (SELECT MAX(t1.O_ID) FROM ORDERS t1 WHERE (t1.O_C_ID = ? AND t1.O_D_ID
= ? AND t1.O_W_ID = ?) ) AND t0.O_C_ID = ? AND t0.O_D_ID = ? AND t0.O_W_ID = ?)  optimize
for 1 row [params=(short) 339, (short) 3, (short) 23, (short) 339, (short) 3, (short) 23]
> On the next execution of the same JPQL, the PreparedQueryImpl (which is cached before)
gets reused.
> In processing user provided parameters, for example, customerId=2967, districtId=5, warehouseId=22,
> It is observed that the parameter values are incorrect: the last 3 values were incorrectly
copied from the previously cached version.
> [3/16/10 17:45:42:411 CDT] 00000043 OpenJPA       3   openjpa.jdbc.SQL: Trace: <t
195496871, conn 1706649017> executing prepstmnt 1531796301 SELECT t0.O_D_ID, t0.O_ID, t0.O_W_ID,
t0.VERSION, t0.O_ALL_LOCAL, t0.O_CARRIER_ID, t0.O_C_ID, t0.O_ENTRY_D, t0.O_OL_CNT FROM ORDERS
t0 WHERE (t0.O_ID IN (SELECT MAX(t1.O_ID) FROM ORDERS t1 WHERE (t1.O_C_ID = ? AND t1.O_D_ID
= ? AND t1.O_W_ID = ?) ) AND t0.O_C_ID = ? AND t0.O_D_ID = ? AND t0.O_W_ID = ?)  optimize
for 1 row [params=(short) 2967, (short) 5, (short) 22, (short) 339, (short) 3, (short) 23]

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message