Uploaded image for project: 'ModeShape'
  1. ModeShape
  2. MODE-1679

JCR-SQL2 JOIN doesn't work for multi-value reference properties

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 3.0.0.CR2
    • Fix Version/s: 3.0.0.CR3
    • Component/s: JCR, Query
    • Labels:
      None
    • Workaround:
      Workaround Exists
    • Workaround Description:
      Hide

      Use a subquery for the third JOIN. For example:

      SELECT artifact.*
      FROM [sramp:baseArtifactType] AS artifact
      JOIN [sramp:relationship] AS relationship ON ISCHILDNODE(relationship, artifact)
      WHERE relationship.[sramp:relationshipType] = 'element'
        AND artifact.[sramp:artifactType] = 'Part'
        AND relationship.[sramp:relationshipTarget] IN (
              SELECT [jcr:uuid] FROM [sramp:baseArtifactType] AS target
              WHERE target.[ncName] = 'find'
            )
      

      rather than

      
      SELECT artifact.*
         FROM [sramp:baseArtifactType] AS artifact
       JOIN [sramp:relationship] AS relationship ON ISCHILDNODE(relationship, artifact)
       JOIN [sramp:baseArtifactType] AS target ON relationship.[sramp:relationshipTarget] = target.[jcr:uuid]
        WHERE artifact.[sramp:artifactType] = 'Part'
         AND relationship.[sramp:relationshipType] = 'element'
         AND target.[ncName] = 'find'
      
      Show
      Use a subquery for the third JOIN. For example: SELECT artifact.* FROM [sramp:baseArtifactType] AS artifact JOIN [sramp:relationship] AS relationship ON ISCHILDNODE(relationship, artifact) WHERE relationship.[sramp:relationshipType] = ' element ' AND artifact.[sramp:artifactType] = 'Part' AND relationship.[sramp:relationshipTarget] IN ( SELECT [jcr:uuid] FROM [sramp:baseArtifactType] AS target WHERE target.[ncName] = 'find' ) rather than SELECT artifact.* FROM [sramp:baseArtifactType] AS artifact JOIN [sramp:relationship] AS relationship ON ISCHILDNODE(relationship, artifact) JOIN [sramp:baseArtifactType] AS target ON relationship.[sramp:relationshipTarget] = target.[jcr:uuid] WHERE artifact.[sramp:artifactType] = 'Part' AND relationship.[sramp:relationshipType] = ' element ' AND target.[ncName] = 'find'

      Description

      Trying to do a JOIN using a multi-value reference property as part of the join condition. Will attach a Maven project that illustrates the problem.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                rhauch Randall Hauch
                Reporter:
                eric.wittmann Eric Wittman
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: