7 Replies Latest reply on Jan 3, 2012 2:38 PM by lexsoto

    Join not returning results

    lexsoto

      Hello:

       

      I am having trouble with this query not returning expected results:

       

                          final Session session = repository.login(new SimpleCredentials(getUserName(), getPassword().toCharArray()));
        
                          final String friendlyNodeType = "tt:friendly";
                          final String friendPropertyName = "tt:friend";
        
                          final NamespaceRegistry registry = session.getWorkspace().getNamespaceRegistry();
                          registry.registerNamespace("tt", "http://test.com/tt");
        
                          final NodeTypeManager manager = session.getWorkspace().getNodeTypeManager();
                          final NodeTypeTemplate nodeType = manager.createNodeTypeTemplate();
                          nodeType.setMixin(true);
                          nodeType.setName(friendlyNodeType);
                          nodeType.setQueryable(true);
                          nodeType.setDeclaredSuperTypeNames(new String[]{"mix:referenceable"});
        
                          final PropertyDefinitionTemplate propertyDef = manager.createPropertyDefinitionTemplate();
                          propertyDef.setName(friendPropertyName);
                          propertyDef.setMultiple(true);
                          propertyDef.setRequiredType(PropertyType.REFERENCE);
                          propertyDef.setOnParentVersion(OnParentVersionAction.COPY);
                          propertyDef.setProtected(false);
        
                          nodeType.getPropertyDefinitionTemplates().add(propertyDef);
                          manager.registerNodeType(nodeType, true);
        
                          final Node granteeNode = session.getRootNode().addNode("Paul", "nt:folder");
                          granteeNode.addMixin(friendlyNodeType);
      
                          final Node grantorNode = session.getRootNode().addNode("Pedro", "nt:folder");
                          grantorNode.addMixin(friendlyNodeType);
        
                          final ValueFactory valueFactory = session.getValueFactory();
                          final Value granteeValue = valueFactory.createValue(granteeNode);
                          grantorNode.setProperty(friendPropertyName, new Value[]{granteeValue});
      
                          session.save();
      
                          final QueryManager queryManager = session.getWorkspace().getQueryManager();
                          final String expression = 
        "SELECT grantee.* FROM [tt:friendly] as grantee " +
        "INNER JOIN [tt:friendly] as grantor " +
        "ON grantee.[jcr:uuid] = grantor.[tt:friendly]";
        
                          final Query query = queryManager.createQuery(expression, "JCR-SQL2");
                          final QueryResult queryResult = query.execute();
                          Assert.assertEquals(1, queryResult.getRows().getSize());
      
      

       

      I am using version 2.7.0.Final

      I expect the query to rerturn Paul's node.

      (It works with JackRabbit)

       

      Thanks

        • 1. Re: Join not returning results
          rhauch

          I haven't tested whether this works, and will try to check this later today.

           

          However, because of the way indexing works (and how the read-write locks in the indexes work), sometimes the results are not immediately visible to queries. You can temporarily get around this by adding a Thread.sleep(2000) call to the code right after the 'session.save()' call.

          • 2. Re: Join not returning results
            lexsoto

            Hmm...

            Unfortunately still doesn't work, even with 30 sec of delay after the save() call.

             

            Thanks

            • 3. Re: Join not returning results
              rhauch

              K. Thanks for checking. Will get back to you in a bit.

              • 4. Re: Join not returning results
                lexsoto

                I am sorry Radall, something is wrong with my test case. 

                I had it working with JackRabitt but it no longer works with JR.  Must has been something I changed while preparing the sample code for posting.

                Checking....

                • 5. Re: Join not returning results
                  rhauch

                  I think the query has an error; the last line is:

                   

                  "ON grantee.[jcr:uuid] = grantor.[tt:friendly]";

                   

                  but should be

                   

                  "ON grantee.[jcr:uuid] = grantor.[tt:friend]";

                   

                  Anyway, even after changing this, I'm getting the same zero-results that you are, and have tracked this to a bug in our indexing code. It's limited to joins with reference properties, but unfortunately that's pretty important in this case. I already have a fix tested and will be committing it shortly.

                  • 6. Re: Join not returning results
                    rhauch

                    I created MODE-1364 to track this issue. I should have a fix ready to commit shortly.

                     

                    Unfortunately, I don't know of a workaround. Normally, the ModeShape-specific REFERENCE(...) function could be used to make this a bit easier, but the equi-join condition grammar rule does not allow dynamic operands, so the REFERENCE(...) function can't be used in a join condition.

                    • 7. Re: Join not returning results
                      lexsoto

                      I made a mistake while preparing the sample code (the actual code where I first noticed the problem is too fragmented for posting)

                      I am glad to hear that you were able to identify the problem.

                       

                      Thank you very much for you help!

                       

                      (No worries about a workaround, I am still on the prototyping phase so there is no rush)