openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Martin Dirichs (JIRA)" <j...@apache.org>
Subject [jira] Updated: (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 15:25:49 GMT

     [ https://issues.apache.org/jira/browse/OPENJPA-1584?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Martin Dirichs updated OPENJPA-1584:
------------------------------------

    Attachment: OpenJPA-2.0.0_OJ1584.patch

A modification to SQLBuffer has been committed in revision 925036 which supposedly corrected
this issue. However, the committed change also has problems. The problem arises during the
construction of a prepared statement when sub-selects are added to the main statement. In
particular, the indexes of user parameters have to be adapted to their new positions within
the resulting statement (which consists of the main statement and the sub-select). File org.apache.openjpa.jdbc.sql.SQLBuffer
contains the following lines, which were committed with revision 925036 (lines 170++):

            // modify this buffer's user parameter index
            int otherSize = buf._userIndex.size()/2;
            for (int i = 0; i < _userIndex.size(); i+=2) {
                int newIndex = ((Integer)_userIndex.get(i)).intValue() + otherSize;
                _userIndex.set(i, newIndex);
            }
            // append the other buffer's user parameters to this one
            for (int i = 0; i < buf._userIndex.size(); i+=2) {
                Object otherIndex = buf._userIndex.get(i);
                Object otherParam = buf._userIndex.get(i+1);
                _userIndex.add(otherIndex);
                _userIndex.add(otherParam);
            }            

This code assumes that the sub-select user parameters, which are stored in buf._userIndex,
always reside at positions in the combined statement before all the parameters of the main
statement. If this is the case, the above code works (almost) correctly: All user parameters
of the main statement get their indexes adapted, all sub-select user parameters are just copied
without change. The code is only almost correct in this case, because the index offset calculation
done with
int otherSize = buf._userIndex.size()/2;
is misguided - the offset should really be the number of all parameters (including non-user
parameters) of the sub-select, not only the user parameters.

The correct code is given below:

            // modify this buffer's user parameter index for all parameters
            // at or behind paramIndex, which is the insertion position
            int otherSize = buf._params.size();
            for (int i = 0; i < _userIndex.size(); i+=2) {
                int oldIndex = ((Integer)_userIndex.get(i)).intValue();
                if (oldIndex >= paramIndex)
                    _userIndex.set(i, oldIndex + otherSize);
            }
            // append the other buffer's user parameters to this one, their
            // position adapted according to the insertion position
            for (int i = 0; i < buf._userIndex.size(); i+=2) {
                int otherIndex = ((Integer) buf._userIndex.get(i)).intValue();
                Object otherParam = buf._userIndex.get(i+1);
                _userIndex.add(otherIndex + paramIndex);
                _userIndex.add(otherParam);
            }            

Here, the insertion position at where the sub-select parameters get inserted into the main
statement is propertly taken into account (paramIndex). User parameters of the main statement
which reside before the insertion position remain unchanged, the position of all others gets
offset by the number of parameters of the sub-select. The sub-select user parameters in turn
are moved by an amount equal to the insertion position.

A corresponding patch OpenJPA-2.0.0_OJ1584.patch containing the correct version of the code
is attached. It would be better though to also add a new test method to TestPreparedQueryCache.
If my help is welcome, I can create such a test case and supply it as well.

> 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