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

Invalid query results for sql with outer join and OR join criteria

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Resolved (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 3.5.0.Final
    • Fix Version/s: 4.0.0.Beta1
    • Component/s: Query
    • Labels:
      None

      Description

      Hi,
      I'm trying to do a fulltext search in JCR SQL2.The query should return all nodes which have at least one property containing the search string or they have child nodes with properties containing the same string.

      Query:

      SELECT *  
      FROM [test:Parent] as p   
      LEFT OUTER JOIN [test:Child] as c ON ISCHILDNODE(c, p)   
      WHERE   
       contains(p.*, 'name2') or contains(c.*, 'name2')  
      

      Repository data:

      /p1  
      /p1/jcr:primaryType = test:Parent  
      /p1/test:parentName = name1  
      /p1/c1  
      /p1/c1/jcr:primaryType = test:Child  
      /p1/c1/test:childName = name2  
        
      /p2  
      /p2/jcr:primaryType = test:Parent  
      /p2/test:parentName = name2  
        
      /p3  
      /p3/jcr:primaryType = test:Parent  
      /p3/test:parentName = name3  
      

      Expected result:

      /p1 with c1/ (child node contains string 'name2')
      /p2 (this node contains string  'name2')
      

      Actual result:

      /p2 {jcr:primaryType=test:Parent, test:parentName=name2}
      /p1 {jcr:primaryType=test:Parent, test:parentName=name1}
      /p3 {jcr:primaryType=test:Parent, test:parentName=name3}
      

      Another Query (nodes does not contains search text):

      SELECT *  
      FROM [test:Parent] as p   
      LEFT OUTER JOIN [test:Child] as c ON ISCHILDNODE(c, p)   
      WHERE   
       contains(p.*, 'iddqd') or contains(c.*, 'iddqd')  
      

      Expected result:
      nothing

      Actual result:

      /p2 {jcr:primaryType=test:Parent, test:parentName=name2}
      /p1 {jcr:primaryType=test:Parent, test:parentName=name1}
      /p3 {jcr:primaryType=test:Parent, test:parentName=name3}
      

      more tests on this problem: modeshap-test

      I think the problem is here: SelectComponent.java

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  rhauch Randall Hauch
                  Reporter:
                  ybrig Yury Brigadirenko
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  2 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: