Uploaded image for project: 'Nuxeo Platform'
  1. Nuxeo Platform
  2. NXP-8514

Incorrect SQL generation for query with JOIN and ACLs on H2

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 5.4.2
    • Component/s: CMIS

      Description

      With H2 dialect:

      SELECT Ent.cmis:objectId FROM Person Ent JOIN Occurrence Occ ON Occ.relation:target = Ent.cmis:objectId WHERE Occ.relation:source = 'fec8e975-53df-47cd-bc14-0744dd648912' ORDER BY Ent.dc:title
      

      Generates:

      SELECT "_Ent_HIERARCHY"."ID", "_Ent_HIERARCHY"."PRIMARYTYPE", "_Occ_HIERARCHY"."ID", "_Occ_HIERARCHY"."PRIMARYTYPE"
       FROM "HIERARCHY" "_Ent_HIERARCHY"
       LEFT JOIN "DUBLINCORE" "_Ent_DUBLINCORE" ON "_Ent_DUBLINCORE"."ID" = "_Ent_HIERARCHY"."ID"
       LEFT JOIN "MISC" "_Ent_MISC" ON "_Ent_MISC"."ID" = "_Ent_HIERARCHY"."ID"
       JOIN hierarchy_read_acl nxr ON "_Ent_HIERARCHY"."ID" = nxr.id
       JOIN "RELATION" "_Occ_RELATION" ON "_Occ_RELATION"."TARGET" = "_Ent_HIERARCHY"."ID"
       LEFT JOIN "HIERARCHY" "_Occ_HIERARCHY" ON "_Occ_HIERARCHY"."ID" = "_Occ_RELATION"."ID"
       LEFT JOIN "MISC" "_Occ_MISC" ON "_Occ_MISC"."ID" = "_Occ_RELATION"."ID"
       JOIN hierarchy_read_acl nxr0 ON "_Occ_RELATION"."ID" = nxr0.id
       WHERE "_Ent_HIERARCHY"."PRIMARYTYPE" IN (?)
        AND "_Ent_MISC"."LIFECYCLESTATE" <> ?
        AND nxr.acl_id IN (SELECT * FROM nx_get_read_acls_for(?, ','))
        AND "_Occ_HIERARCHY"."PRIMARYTYPE" IN (?)
        AND "_Occ_MISC"."LIFECYCLESTATE" <> ?
        AND nxr0.acl_id IN (SELECT * FROM nx_get_read_acls_for(?, ','))
        AND "_Occ_RELATION"."SOURCE" = ?
       ORDER BY "_Ent_DUBLINCORE"."TITLE"
      

      Which throws exception:

      org.h2.jdbc.JdbcSQLException. message: Column _Occ_HIERARCHY.ID not found
      

      Actually the culprit is the nxr-1 which is an invalid identifier.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                fguillaume Florent Guillaume
                Reporter:
                fguillaume Florent Guillaume
                Participants:
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: