Repeating parameter name causes all kinds of problems?

[Pawel Veselov <pa...@gmail.com>]

Hello.

I can't find much references to problems like this, which is
surprising. May be I'm doing something that is prohibited by the spec?

I want to re-use the same parameter twice. However, I'm seeing all
kinds of behavior except for the one I want. This is with OpenJPA
2.4.2.

This code (just the fragment that sets up most of the where clause):

Subquery<String> allowedGroups = cq.subquery(String.class);
Root<E_SotaDeviceGroupBlock> block =
allowedGroups.from(E_SotaDeviceGroupBlock.class);
allowedGroups.select(block.get(E_SotaDeviceGroupBlock_.groupName));
allowedGroups.where(
        cb.and(
                cb.equal(block.get(E_SotaDeviceGroupBlock_.strHash),
cb.parameter(String.class, P_DEVICE_GROUP_ACCESS_HASH)),
                cb.equal(block.get(E_SotaDeviceGroupBlock_.tenancy),
cb.parameter(String.class, P_TENANCY))
        )
);
Join<E_SotaDevice, E_SotaDeviceGroup> dJoin =
deviceSrc.join(E_SotaDevice_.groups);
soFar = cb.and(
        soFar,
        cb.in(dJoin.get(E_SotaDeviceGroup_.group)).value(allowedGroups),
        cb.equal(deviceSrc.get(E_SotaDevice_.tenancy),
cb.parameter(String.class, P_TENANCY);
));

(parameter values are '1' for P_TENANCY and some longish string for
P_DEVICE_GROUP_ACCESS_HASH):

Generates:
SELECT SUM($1) FROM vehicle_campaigns t0 INNER JOIN vehicles t1 ON
t0.vehicle_id = t1.id AND t0.vehicle_tenancy = t1.tenancy INNER JOIN
device_groups t2 ON t1.id = t2.device_id AND t1.tenancy =
t2.device_tenancy WHERE (t0.campaign = $2 AND t2.group_id IN (SELECT
t3.groupName FROM device_group_block t3 WHERE (t3.strHash = $3 AND
t3.tenancy = $4)) AND t1.tenancy IS NULL AND 1 = 1) GROUP BY t1.vin2
DETAIL:  parameters: $1 = '1', $2 = '1700', $3 =
'yFYLwbSPHm69bTX99+f8Mcjh1EVEfpFk9Zmz9L+TpIc=', $4 =
'yFYLwbSPHm69bTX99+f8Mcjh1EVEfpFk9Zmz9L+TpIc='

But if I use the same value of the cb.parameter():

Expression<String> tenancyParam = cb.parameter(String.class, P_TENANCY);
Subquery<String> allowedGroups = cq.subquery(String.class);
Root<E_SotaDeviceGroupBlock> block =
allowedGroups.from(E_SotaDeviceGroupBlock.class);
allowedGroups.select(block.get(E_SotaDeviceGroupBlock_.groupName));
allowedGroups.where(
        cb.and(
                cb.equal(block.get(E_SotaDeviceGroupBlock_.strHash),
cb.parameter(String.class, P_DEVICE_GROUP_ACCESS_HASH)),
                cb.equal(block.get(E_SotaDeviceGroupBlock_.tenancy),
tenancyParam)
        )
);
Join<E_SotaDevice, E_SotaDeviceGroup> dJoin =
deviceSrc.join(E_SotaDevice_.groups);
soFar = cb.and(
        soFar,
        cb.in(dJoin.get(E_SotaDeviceGroup_.group)).value(allowedGroups),
        cb.equal(deviceSrc.get(E_SotaDevice_.tenancy), tenancyParam)
        );

SELECT t1.vin2, t1.make, t1.model, t1.year, t1.location FROM
vehicle_campaigns t0 INNER JOIN vehicles t1 ON t0.vehicle_id = t1.id
AND t0.vehicle_tenancy = t1.tenancy INNER JOIN device_groups t2 ON
t1.id = t2.device_id AND t1.tenancy = t2.device_tenancy WHERE
(t0.campaign = $1 AND t2.group_id IN (SELECT t3.groupName FROM
device_group_block t3 WHERE (t3.strHash = $2 AND t3.tenancy = $3)) AND
t1.tenancy = $4 AND 1 = 1) GROUP BY t1.vin2, t1.make, t1.model,
t1.year, t1.location ORDER BY t1.vin2 DESC LIMIT $5
DETAIL:  parameters: $1 = '1700', $2 =
'yFYLwbSPHm69bTX99+f8Mcjh1EVEfpFk9Zmz9L+TpIc=', $3 =
'yFYLwbSPHm69bTX99+f8Mcjh1EVEfpFk9Zmz9L+TpIc=', $4 =
'yFYLwbSPHm69bTX99+f8Mcjh1EVEfpFk9Zmz9L+TpIc=', $5 = '10'

I double checked that I set P_TENANCY to "1", it seems to disappear
all together. The log output is from Postgres.

It's gonna be rather tough to set up a separate project to just
reproduce the problem, I guess, is there a place in the source code
where I can dig?

Thank you,
  Pawel.

Re: Repeating parameter name causes all kinds of problems?

[Pawel Veselov <pa...@gmail.com>]

On Mon, Apr 2, 2018 at 1:03 AM, Pawel Veselov <pa...@gmail.com> wrote:
> On Sun, Apr 1, 2018 at 2:51 PM, Pawel Veselov <pa...@gmail.com> wrote:
>> I want to re-use the same parameter twice. However, I'm seeing all
>> kinds of behavior except for the one I want. This is with OpenJPA
>> 2.4.2.
> This seems to be broken in OpenJPA though. Especially if sub-queries
> are involved. I traced this as much as I could, but I don't understand
> the intend of the code.

I've opened https://issues.apache.org/jira/browse/OPENJPA-2733, that
contains the description, the test case, and a fix, though the fix
still leaves some problems.

Re: Repeating parameter name causes all kinds of problems?

[Pawel Veselov <pa...@gmail.com>]

On Sun, Apr 1, 2018 at 2:51 PM, Pawel Veselov <pa...@gmail.com> wrote:
> Hello.
>
> I can't find much references to problems like this, which is
> surprising. May be I'm doing something that is prohibited by the spec?

The spec OKs to use the same parameter more than once (4.6.4.2 of 2.0)

> I want to re-use the same parameter twice. However, I'm seeing all
> kinds of behavior except for the one I want. This is with OpenJPA
> 2.4.2.

This seems to be broken in OpenJPA though. Especially if sub-queries
are involved. I traced this as much as I could, but I don't understand
the intend of the code. There are a few parameter representations.
org.apache.openjpa.jdbc.kernel.exps.Param, that has an "_idx" field,
defining which parameter value is actually going to be used. There is
a ParameterExpressionImpl that is constructed during query evaluation,
that has the "_index" field that is later used to populate the
Param._idx. While stepping through things I can see:

Considering query Select t1 from t1 t1 where t1.id in ( select t2.ref
from t2 t2 where t2.hash = :hash_value and t2.group = :tenancy) and
t1.group = :tenancy

I see:
1) ParameterExpressionImpl for tenancy is assigned _index 0
2) ParameterExpressionImpl for hash_value is assigned _index 1
3) Param for hash_value (sub-query) is assigned _idx 1
4) ParameterExpressionImpl for hash_value is re-assigned to index 0
(because now it's being processed within sub-query, and index is
assigned based on the current size of the parameters in the
CriteriaQueryImpl object)
5) ParameterExpressionImpl for tenancy is reassigned to index 1
6) Param for tenancy (sub-query) is assigned _idx 1 (because of step #5)
7) Param for tenancy (outer query) is assigned _idx 1 (because of step #5)

Param instances are one per parameter placement.
ParameterExpressionImpl instances are one per parameter

I wonder if the fix is just to copy the parameters into the sub-query
object when it's created. Then the parameters would be found, and
there indices not modified.

It also seems that if I use cb.parameter() twice, the second parameter
is not considered the same, even if the name is the same. I think
that's also a bug.

[skipped]


-- 
With best of best regards
Pawel S. Veselov