5 Replies Latest reply on Aug 6, 2014 2:42 AM by Bjoern Schmidt

    Is the Order of join columns important in SQL2?

    Bjoern Schmidt Newbie

      Hi again,

       

      Please see the test below. I create 3 nodeTypes, 3 Objects somewhere in the tree and then want to join all three over some properties.

      Now it's important in which order I place the components of the ON equation, the last assert.equals fails.

       

      If I change the order in one of the two first tests, they still work, it only fails if I construct more than one join.

       

      Is this the desired behaviour or a bug?

      @RunWith(CdiTestRunner.class)

      @TestControl(startScopes = ApplicationScoped.class)

      @FixMethodOrder(MethodSorters.NAME_ASCENDING)

      public class CDITest {

       

       

       

        @Inject

        private Session session;

       

       

        @Inject

        protected transient Logger logger;

       

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

        }

        private void fireQuery(Session repoSession, String typeName) throws RepositoryException{

        Query query = repoSession.getWorkspace().getQueryManager().createQuery(

        "SELECT BASE.* FROM [" + typeName + "] AS BASE ", Query.JCR_SQL2);

        query.execute();

        }

       

        private void makePkgNode(Session repoSession) throws RepositoryException{

        Node root = repoSession.getNode("/");

        Node x = root.addNode("dab:org.some.packet","nt:unstructured");

        repoSession.save();

       

       

        }

       

        private Node saveNode(Session repoSession, String typeName, String name) throws RepositoryException{

        Node root = repoSession.getNode("/dab:org.some.packet");

        Node x = root.addNode(typeName+"s","nt:unstructured").addNode(typeName, typeName);

        x.setProperty("sysName", name);

        repoSession.save();

        return x;

        }

        private Node saveNodeWithRefMK(Session repoSession, String typeName) throws RepositoryException{

        Node root = repoSession.getNode("/dab:org.some.packet");

        Node x = root.addNode(typeName+"s","nt:unstructured").addNode(typeName, typeName);

        x.setProperty("sysName", "pommes1");

        x.setProperty("prop1", "mayo1");

        x.setProperty("prop2", "ketchup1");

        repoSession.save();

        return x;

        }

       

        private void regNs(Session repoSession) throws RepositoryException{

        NamespaceRegistry reg = repoSession.getWorkspace().getNamespaceRegistry();

        reg.registerNamespace("dab", "org://some");

        }

       

        private NodeIterator fireJoinQueryB(Session repoSession, String typeName ,String joinTypeName2) throws RepositoryException{

        Query query = repoSession.getWorkspace().getQueryManager().createQuery(

        "SELECT BASE.* FROM [" + typeName + "] AS BASE "

        + " JOIN [" + joinTypeName2 + "] AS B ON B.sysName = BASE.prop2  "

        , Query.JCR_SQL2);

        logger.info(query.getStatement());

        QueryResult re =  query.execute();

        return re.getNodes();

        }

        private NodeIterator fireJoinQueryA(Session repoSession, String typeName, String joinTypeName1) throws RepositoryException{

        Query query = repoSession.getWorkspace().getQueryManager().createQuery(

        "SELECT BASE.* FROM [" + typeName + "] AS BASE "

        + " JOIN [" + joinTypeName1 + "] AS A ON A.sysName = BASE.prop1  "

        , Query.JCR_SQL2);

        logger.info(query.getStatement());

        QueryResult re =  query.execute();

        return re.getNodes();

        }

        private NodeIterator fireJoinQueryBothA(Session repoSession, String typeName, String joinTypeName1 ,String joinTypeName2) throws RepositoryException{

        Query query = repoSession.getWorkspace().getQueryManager().createQuery(

        "SELECT BASE.* FROM [" + typeName + "] AS BASE "

        + " JOIN [" + joinTypeName1 + "] AS A ON A.sysName = BASE.prop1 "

        + " JOIN [" + joinTypeName2 + "] AS B ON B.sysName = BASE.prop2"

        , Query.JCR_SQL2);

        logger.info(query.getStatement());

        QueryResult re =  query.execute();

        return re.getNodes();

        }

       

        private NodeIterator fireJoinQueryBothB(Session repoSession, String typeName, String joinTypeName1 ,String joinTypeName2) throws RepositoryException{

        Query query = repoSession.getWorkspace().getQueryManager().createQuery(

        "SELECT BASE.* FROM [" + typeName + "] AS BASE "

        + " JOIN [" + joinTypeName1 + "] AS A ON BASE.prop1 = A.sysName "

        + " JOIN [" + joinTypeName2 + "] AS B ON BASE.prop2 = B.sysName "

        , Query.JCR_SQL2);

        logger.info(query.getStatement());

        QueryResult re =  query.execute();

        return re.getNodes();

        }

       

        @Test

        public void test001() throws RepositoryException {

      regNs(session);

      makePkgNode(session);

       

      regNodeType(session, "dab:RmPommes");

      regNodeType(session, "dab:RmMayo");

      regNodeType(session, "dab:RmKetchup");

       

      Node m = saveNode(session, "dab:RmMayo", "mayo1");

      Node k = saveNode(session, "dab:RmKetchup","ketchup1");

       

      Node p = saveNodeWithRefMK(session, "dab:RmPommes");

       

        NodeIterator it = fireJoinQueryB(session, "dab:RmPommes","dab:RmKetchup");

        Assert.assertEquals(it.hasNext(), true);

       

       

        it = fireJoinQueryA(session, "dab:RmPommes","dab:RmMayo");

        Assert.assertEquals(it.hasNext(), true);

       

        it = fireJoinQueryBothB(session, "dab:RmPommes","dab:RmMayo","dab:RmKetchup");

        Assert.assertEquals("pommes1",it.nextNode().getProperty("sysName").getString());

       

        it = fireJoinQueryBothA(session, "dab:RmPommes","dab:RmMayo","dab:RmKetchup");

        Assert.assertEquals("pommes1",it.nextNode().getProperty("sysName").getString());

        }

       

       

       

       

      }