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

[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.

[lexsoto]

Hmm...

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

Thanks

[rhauch]

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

[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....

[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.

[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.

[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)