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

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

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • 3.0.0.CR3
    • 3.0.0.CR2
    • JCR, Query
    • None
    • Workaround Exists
    • 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.

      Attachments

        Activity

          People

            rhauch Randall Hauch (Inactive)
            ewittman@redhat.com Eric Wittmann
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: