Is the Order of join columns important in SQL2?

[bes82]

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




}

[rhauch]

IIRC, you're using ModeShape 4.0.0.Alpha4. I don't believe that our current query optimization rules will reorder joins, so ModeShape will execute the query's joins in the order you specify.

There does seem to be a problem, because it with inner joins I don't think the order should matter and the results should be the same. Please log an issue with a good description (including your node structure and queries).

[hchiorean]

In the latest master there is at least one fix (submitted for [MODE-2178] Limitations and problems in the new query engine - JBoss Issue Tracker)  around the order of the columns in a JOIN sequence, so this might already be fixed. The easiest way to check this (if possible) is to write your query/test in modeshape/JcrQueryManagerTest.java at master · ModeShape/modeshape · GitHub and run it.

[bes82]

I can't compile it: All kind of tests fail, I disabled them, but now:

Could not find artifact smartgwt:smartgwt:jar:2.4 can you tell me which maven repo u use for that?

If I try to compile only modeshape-jcr, its complaining about some missing dependencies.

[rhauch]

Please follow the instructions at https://github.com/ModeShape/modeshape/blob/master/README.md. You can compile with "mvn clean install -s settings.xml", and it should find all of the repositories it needs.

[bes82]

Thanks, I tested it with the current 4.0-SNAPSHOT from master, works.

Will stay at master now so I don't waste a day finding a bug that has already been fixed