Index and Joins not working as expected / Index and subselects not working

SELECT A.* FROM [nt:typeWithReference] AS A
WHERE A.referenceId  IN (

SELECT B.[jcr:uuid] FROM [nt:typeWithSysName] AS B 
WHERE B.sysName = $sysName 
)

SELECT A.* FROM [nt:typeWithReference] AS A
JOIN [nt:typeWithSysName] AS B ON A.referenceId  = B.[jcr:uuid] 
WHERE B.sysName = $sysName 

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package org.erratic.rm.test;

import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.enterprise.context.RequestScoped;
import javax.inject.Inject;
import javax.jcr.Node;
import javax.jcr.PropertyType;
import javax.jcr.RepositoryException;
import javax.jcr.Session;
import javax.jcr.nodetype.NodeTypeManager;
import javax.jcr.nodetype.NodeTypeTemplate;
import javax.jcr.query.Query;
import static org.apache.deltaspike.core.api.projectstage.ProjectStage.UnitTest;
import org.apache.deltaspike.testcontrol.api.TestControl;
import org.apache.deltaspike.testcontrol.api.junit.CdiTestRunner;
import org.junit.Assert;
import org.junit.FixMethodOrder;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.junit.runners.MethodSorters;
import org.modeshape.jcr.api.index.IndexColumnDefinition;
import org.modeshape.jcr.api.index.IndexDefinition;
import org.modeshape.jcr.api.index.IndexDefinitionTemplate;
import org.modeshape.jcr.api.index.IndexManager;
import javax.jcr.query.QueryResult;
import org.erratic.cr.commons.CrConstants;
import org.slf4j.Logger;

/**
 *
 * @author bschmidt
 */
@RunWith(CdiTestRunner.class)
@TestControl(startScopes = RequestScoped.class, projectStage = UnitTest.class)
@FixMethodOrder(MethodSorters.NAME_ASCENDING)
//@Ignore
public class WmModeshapeIndex4Test {

	
	@Inject
	private Session session;

	@Inject
	protected transient Logger logger;

	@Test
	public void test001CheckSomethingWithIndexes() throws RepositoryException, InterruptedException {
		
		regNodeType(session,"nt:typeWithReference");
		ensureIndex(indexManager(), "referenceId", IndexDefinition.IndexKind.VALUE, "local", "nt:typeWithReference", "", null, "referenceId", PropertyType.STRING);
		
		regNodeType(session,"nt:typeWithSysName");
		ensureIndex(indexManager(), "sysName", IndexDefinition.IndexKind.VALUE, "local", "nt:typeWithSysName", "", null, "sysName", PropertyType.STRING);
		
		Node newNode1 = session.getRootNode().addNode("nodeWithSysName","nt:typeWithSysName");
		newNode1.setProperty("sysName", "X");
		newNode1.addMixin("mix:referenceable");
		
		Node newNode2 = session.getRootNode().addNode("nodeWithReference","nt:typeWithReference");
		newNode2.setProperty("referenceId", newNode1.getIdentifier());
		
		session.save();
		
		Thread.sleep(500);

		
		Query q = session.getWorkspace().getQueryManager().createQuery(
		"SELECT A.* FROM [nt:typeWithReference] AS A\n" +
		"WHERE A.referenceId  IN (\n" +
		"\n" +
		"SELECT B.[jcr:uuid] FROM [nt:typeWithSysName] AS B \n" +
		"WHERE B.sysName = $sysName \n" +
		")",Query.JCR_SQL2);
		q.bindValue("sysName", session.getValueFactory().createValue("X"));
		
		logger.info(q.getStatement());
		
		QueryResult result = q.execute();
		
		String plan = ((org.modeshape.jcr.api.query.QueryResult)result).getPlan();
		logger.info(plan);
		
		Assert.assertEquals(2,countIndexes(plan));
		Assert.assertEquals(result.getNodes().hasNext(),true); // FAILS, so nothing found using subselect and indexes
		
		
		Query q2 = session.getWorkspace().getQueryManager().createQuery(
		"SELECT A.* FROM [nt:typeWithReference] AS A\n" +
		"JOIN [nt:typeWithSysName] AS B ON A.referenceId  = B.[jcr:uuid] \n" +
		"WHERE B.sysName = $sysName \n"
		,Query.JCR_SQL2);
		q2.bindValue("sysName", session.getValueFactory().createValue("X"));
		
		logger.info(q2.getStatement());
		
		QueryResult result2 = q2.execute();
		
		String plan2 = ((org.modeshape.jcr.api.query.QueryResult)result2).getPlan();
		logger.info(plan2);
		
		Assert.assertEquals(2,countIndexes(plan2)); // FAILS, so index that should be used is not because of wrong access order
		Assert.assertEquals(true,result2.getNodes().hasNext());
	}
	
	private int countIndexes(String plan){
		
		Pattern p = Pattern.compile("INDEX_USED=true");
		Matcher m = p.matcher(plan);
		int count = 0;
		while (m.find()){
			count +=1;
		}
		return count;
	}
	
	protected IndexManager indexManager(){
		
		if (!(session instanceof org.modeshape.jcr.api.Session)){
			return null;
		}
		
		try {
			return ((org.modeshape.jcr.api.Session) session).getWorkspace().getIndexManager();
		} catch (RepositoryException ex) {
			logger.error(ex.toString(), ex);
		}
		return null;
	}
	
	protected void ensureIndex(IndexManager manager, String indexName, IndexDefinition.IndexKind kind, String providerName, String indexedNodeType, String desc, String workspaceNamePattern, String propertyName, int propertyType) throws RepositoryException {


		if (manager.getIndexDefinitions().containsKey(indexName)){
			return;
		}
		
		logger.info("registering index on property "+propertyName+", type "+indexedNodeType);
		
		// Create the index template ...
		IndexDefinitionTemplate template = manager.createIndexDefinitionTemplate();
		template.setName(indexName);
		template.setKind(kind);
		template.setNodeTypeName(indexedNodeType);
		template.setProviderName(providerName);
		if (workspaceNamePattern != null) {
			template.setWorkspaceNamePattern(workspaceNamePattern);
		} else {
			template.setAllWorkspaces();
		}
		if (desc != null) {
			template.setDescription(desc);
		}
		
		// Set up the columns ...
		IndexColumnDefinition colDefn = manager.createIndexColumnDefinitionTemplate().setPropertyName(propertyName).setColumnType(propertyType);
		template.setColumnDefinitions(colDefn);

		// Register the index ...
		manager.registerIndex(template, true);
	}
	
	private void regNodeType(Session repoSession, String typeName) throws RepositoryException{
			NodeTypeManager mgr = repoSession.getWorkspace().getNodeTypeManager();

			// Create a template for the node type ...
			NodeTypeTemplate type = mgr.createNodeTypeTemplate();
			type.setName(typeName);
			type.setDeclaredSuperTypeNames(new String[]{CrConstants.JCR_NT_UNSTRUCTURED});
			type.setAbstract(false);
			type.setOrderableChildNodes(true);
			type.setMixin(false);
			type.setQueryable(true);
			mgr.registerNodeType(type, true);
	}
}