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.
