Pawel Veselov created OPENJPA-2793:
--------------------------------------
Summary: Stray table reference in Postgres SQL
Key: OPENJPA-2793
URL: https://issues.apache.org/jira/browse/OPENJPA-2793
Project: OpenJPA
Issue Type: Bug
Components: kernel
Affects Versions: 2.4.2
Reporter: Pawel Veselov
Implementation inserts a stray table reference into produce update query. For
us, the stray table has a lot of records, so this causes immediate performance
hit, and we don't quite see how to easily rewrite the JPQL query to avoid the
problem.
AFAIU, the stray table insertion is caused because OpenJPA rewrite the query to
use a second nested IN, but probably keeps the outer table reference in the
inner query by mistake.
If absolutely needed, I can post relevant entities, but their contents aren't
important here.
JPQL:
{code:sql}
update E_CustomObjectNotUnique cd2 set cd2.numValue = cd2.numValue + 1
where
cd2.object in (
select fd.customData from
E_CampaignDevice cd,
E_FontanaDevice fd
join cd.device sd where
( ( cd.campaign = :campaign ) AND ( cd.enabled = false ) )
AND ( fd.retired = false ) AND ( sd.id = fd.id ) )
AND ( cd2.indexName = :indexName)
{code}
SQL:
{code:sql}
UPDATE crud_non_unique
SET _number = (_number + 0)
WHERE id IN (SELECT DISTINCT t0.id
FROM crud_non_unique t0
WHERE (t0.object_ref IN (SELECT t5.id
FROM vehicle_campaigns t1
INNER JOIN vehicles t2 ON
t1.vehicle_id = t2.id AND t1.vehicle_tenancy = t2.tenancy
CROSS JOIN faw_devices t4
LEFT OUTER JOIN crud t5 ON
t4.CUSTOMDATA_ID = t5.id,
crud_non_unique t3
WHERE (t1.campaign = ? AND t1.enabled = ?
AND t4.retired = ? AND t2.id = t4.id)) AND
t0.index_name = ?))
{code}
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
