Uploaded image for project: 'OpenJPA'
  1. OpenJPA
  2. OPENJPA-2793

Stray table reference in Postgres SQL

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 2.4.2
    • Fix Version/s: None
    • Component/s: kernel
    • Labels:
      None

      Description

      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 rewrites 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:

      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)
      

      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 = ?))
      

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              pveselov Pawel Veselov
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: