Is the Order of join columns important in SQL2?
bes82 Aug 5, 2014 9:09 AMHi 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());
}
}