use of org.voltdb.plannodes.SeqScanPlanNode in project voltdb by VoltDB.
the class PlanAssembler method switchToIndexScanForGroupBy.
/**
* For a seqscan feeding a GROUP BY, consider substituting an IndexScan
* that pre-sorts by the GROUP BY keys.
* If a candidate is already an indexscan,
* simply calculate GROUP BY column coverage
*
* @param candidate
* @param gbInfo
* @return true when planner can switch to index scan
* from a sequential scan, and when the index scan
* has no parent plan node or the candidate is already
* an indexscan and covers all or some GROUP BY columns
*/
private boolean switchToIndexScanForGroupBy(AbstractPlanNode candidate, IndexGroupByInfo gbInfo) {
if (!m_parsedSelect.isGrouped()) {
return false;
}
if (candidate instanceof IndexScanPlanNode) {
calculateIndexGroupByInfo((IndexScanPlanNode) candidate, gbInfo);
if (gbInfo.m_coveredGroupByColumns != null && !gbInfo.m_coveredGroupByColumns.isEmpty()) {
// The candidate index does cover all or some
// of the GROUP BY columns and can be serialized
gbInfo.m_indexAccess = candidate;
return true;
}
return false;
}
AbstractPlanNode sourceSeqScan = findSeqScanCandidateForGroupBy(candidate);
if (sourceSeqScan == null) {
return false;
}
assert (sourceSeqScan instanceof SeqScanPlanNode);
AbstractPlanNode parent = null;
if (sourceSeqScan.getParentCount() > 0) {
parent = sourceSeqScan.getParent(0);
}
AbstractPlanNode indexAccess = indexAccessForGroupByExprs((SeqScanPlanNode) sourceSeqScan, gbInfo);
if (indexAccess.getPlanNodeType() != PlanNodeType.INDEXSCAN) {
// does not find proper index to replace sequential scan
return false;
}
gbInfo.m_indexAccess = indexAccess;
if (parent != null) {
// have a parent and would like to replace
// the sequential scan with an index scan
indexAccess.clearParents();
// For two children join node, index 0 is its outer side
parent.replaceChild(0, indexAccess);
return false;
}
// parent is null and switched to index scan from sequential scan
return true;
}
use of org.voltdb.plannodes.SeqScanPlanNode in project voltdb by VoltDB.
the class TestPlansGroupBy method testInlineSerialAgg_noGroupBy_special.
// AVG is optimized with SUM / COUNT, generating extra projection node
// In future, inline projection for aggregation.
public void testInlineSerialAgg_noGroupBy_special() {
AbstractPlanNode p;
List<AbstractPlanNode> pns;
pns = compileToFragments("SELECT AVG(A1) from T1");
//* enable to debug */ printExplainPlan(pns);
p = pns.get(0).getChild(0);
assertTrue(p instanceof ProjectionPlanNode);
assertTrue(p.getChild(0) instanceof AggregatePlanNode);
assertTrue(p.getChild(0).getChild(0) instanceof ReceivePlanNode);
p = pns.get(1).getChild(0);
assertTrue(p instanceof SeqScanPlanNode);
assertNotNull(p.getInlinePlanNode(PlanNodeType.PROJECTION));
assertNotNull(p.getInlinePlanNode(PlanNodeType.AGGREGATE));
}
use of org.voltdb.plannodes.SeqScanPlanNode in project voltdb by VoltDB.
the class TestPlansJoin method perJoinOpTestScanJoinConditions.
private void perJoinOpTestScanJoinConditions(JoinOp joinOp) {
String query;
AbstractPlanNode pn;
AbstractPlanNode node;
NestLoopPlanNode nlj;
SeqScanPlanNode seqScan;
AbstractExpression predicate;
boolean theOpIsOnTheLeft;
query = "SELECT * FROM R1, R2 WHERE R1.A" + joinOp + "R2.A AND R1.C > 0";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
seqScan = (SeqScanPlanNode) node.getChild(0);
assertEquals("R1", seqScan.getTargetTableName());
predicate = seqScan.getPredicate();
assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
query = "SELECT * FROM R1, R2 WHERE R1.A" + joinOp + "R2.A AND R1.C > R2.C";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
theOpIsOnTheLeft = (predicate != null) && (predicate.getLeft() != null) && predicate.getLeft().getExpressionType() == joinOp.toOperator();
assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, (theOpIsOnTheLeft ? joinOp.toOperator() : ExpressionType.COMPARE_LESSTHAN), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, (theOpIsOnTheLeft ? ExpressionType.COMPARE_LESSTHAN : joinOp.toOperator()), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(0);
assertNull(seqScan.getPredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertNull(seqScan.getPredicate());
query = "SELECT * FROM R1 JOIN R2 ON R1.A" + joinOp + "R2.A WHERE R1.C > 0";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(0);
assertEquals("R1", seqScan.getTargetTableName());
predicate = seqScan.getPredicate();
assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertEquals("R2", seqScan.getTargetTableName());
assertNull(seqScan.getPredicate());
query = "SELECT * FROM R1 JOIN R2 ON R1.A" + joinOp + "R2.A WHERE R1.C > R2.C";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
theOpIsOnTheLeft = (predicate != null) && (predicate.getLeft() != null) && predicate.getLeft().getExpressionType() == joinOp.toOperator();
assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, (theOpIsOnTheLeft ? joinOp.toOperator() : ExpressionType.COMPARE_LESSTHAN), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, (theOpIsOnTheLeft ? ExpressionType.COMPARE_LESSTHAN : joinOp.toOperator()), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(0);
assertNull(seqScan.getPredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertNull(seqScan.getPredicate());
query = "SELECT * FROM R1, R2, R3 WHERE R1.A" + joinOp + "R2.A AND R1.C" + joinOp + "R3.C AND R1.A > 0";
pn = compileToTopDownTree(query, 7, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
// Validate trivial child 1 before child 0 to free up local variable nlj.
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertEquals("R3", seqScan.getTargetTableName());
assertNull(seqScan.getPredicate());
nlj = (NestLoopPlanNode) nlj.getChild(0);
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(0);
assertEquals("R1", seqScan.getTargetTableName());
predicate = seqScan.getPredicate();
assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertEquals("R2", seqScan.getTargetTableName());
assertNull(seqScan.getPredicate());
query = "SELECT * FROM R1 JOIN R2 ON R1.A" + joinOp + "R2.A AND R1.C" + joinOp + "R2.C WHERE R1.A > 0";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(0);
assertEquals("R1", seqScan.getTargetTableName());
predicate = seqScan.getPredicate();
assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertEquals("R2", seqScan.getTargetTableName());
assertNull(seqScan.getPredicate());
query = "SELECT A, C FROM R1 JOIN R2 USING (A, C)";
pn = compileToTopDownTree(query, 2, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
query = "SELECT A, C FROM R1 JOIN R2 USING (A, C) WHERE A > 0";
pn = compileToTopDownTree(query, 2, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(0);
predicate = seqScan.getPredicate();
assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertNull(seqScan.getPredicate());
query = "SELECT * FROM R1 JOIN R2 ON R1.A" + joinOp + "R2.A JOIN R3 ON R1.C" + joinOp + "R3.C WHERE R1.A > 0";
pn = compileToTopDownTree(query, 7, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
// Validate trivial child 1 before child 0 to free up local variable nlj.
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertEquals("R3", seqScan.getTargetTableName());
assertNull(seqScan.getPredicate());
nlj = (NestLoopPlanNode) nlj.getChild(0);
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(0);
assertEquals("R1", seqScan.getTargetTableName());
predicate = seqScan.getPredicate();
assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertEquals("R2", seqScan.getTargetTableName());
assertNull(seqScan.getPredicate());
}
use of org.voltdb.plannodes.SeqScanPlanNode in project voltdb by VoltDB.
the class TestPlansJoin method perJoinOpTestBasicOuterJoin.
private void perJoinOpTestBasicOuterJoin(JoinOp joinOp) {
String query;
AbstractPlanNode pn;
AbstractPlanNode node;
NestLoopPlanNode nlj;
SeqScanPlanNode seqScan;
AbstractExpression predicate;
// SELECT * with ON clause should return all columns from all tables
query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertEquals(JoinType.LEFT, nlj.getJoinType());
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(0);
assertEquals("R1", seqScan.getTargetTableName());
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertEquals("R2", seqScan.getTargetTableName());
query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C AND R1.A = 5";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertEquals(JoinType.LEFT, nlj.getJoinType());
predicate = nlj.getPreJoinPredicate();
assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(0);
assertEquals("R1", seqScan.getTargetTableName());
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertEquals("R2", seqScan.getTargetTableName());
}
use of org.voltdb.plannodes.SeqScanPlanNode in project voltdb by VoltDB.
the class TestPlansJoin method perJoinOpTestOuterJoinSimplification.
private void perJoinOpTestOuterJoinSimplification(JoinOp joinOp) {
String query;
AbstractPlanNode pn;
AbstractPlanNode node;
NestLoopPlanNode nlj;
SeqScanPlanNode seqScan;
IndexScanPlanNode indexScan;
AbstractExpression predicate;
NestLoopIndexPlanNode nlij;
query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE R2.C IS NOT NULL";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertEquals(nlj.getJoinType(), JoinType.INNER);
query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE R2.C > 0";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertEquals(nlj.getJoinType(), JoinType.INNER);
query = "SELECT * FROM R1 RIGHT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE R1.C > 0";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertEquals(nlj.getJoinType(), JoinType.INNER);
query = "SELECT * FROM R1 LEFT JOIN R3 ON R1.C" + joinOp + "R3.C WHERE R3.A > 0";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.INDEXSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertEquals(nlj.getJoinType(), JoinType.INNER);
query = "SELECT * FROM R1 LEFT JOIN R3 ON R1.C" + joinOp + "R3.A WHERE R3.A > 0";
if (joinOp == JoinOp.EQUAL) {
// weaken test for now
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
nlij = (NestLoopIndexPlanNode) node;
assertEquals(nlij.getJoinType(), JoinType.INNER);
}
query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R2.C) < 10";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertEquals(nlj.getJoinType(), JoinType.INNER);
query = "SELECT * FROM R1 RIGHT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R1.C) < 10";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertEquals(nlj.getJoinType(), JoinType.INNER);
query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R1.C) < 10";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertEquals(nlj.getJoinType(), JoinType.LEFT);
query = "SELECT * FROM R1 RIGHT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R2.C) < 10";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertEquals(nlj.getJoinType(), JoinType.LEFT);
query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R2.C) < 10 AND R1.C = 3";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertEquals(nlj.getJoinType(), JoinType.INNER);
query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R2.C) < 10 OR R2.C IS NOT NULL";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertEquals(nlj.getJoinType(), JoinType.INNER);
query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R1.C) < 10 AND R1.C > 3";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertEquals(nlj.getJoinType(), JoinType.LEFT);
query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R1.C) < 10 OR R2.C IS NOT NULL";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertEquals(nlj.getJoinType(), JoinType.LEFT);
// Test with seqscan with different filers.
query = "SELECT R2.A, R1.* FROM R1 LEFT OUTER JOIN R2 ON R2.A" + joinOp + "R1.A WHERE R2.A > 3";
pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
//* enable for debug */ System.out.println(pn.toExplainPlanString());
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertEquals(nlj.getJoinType(), JoinType.INNER);
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
query = "SELECT R2.A, R1.* FROM R1 LEFT OUTER JOIN R2 ON R2.A" + joinOp + "R1.A WHERE R2.A IS NULL";
pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertEquals(nlj.getJoinType(), JoinType.LEFT);
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
predicate = nlj.getWherePredicate();
assertExprTopDownTree(predicate, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE);
seqScan = (SeqScanPlanNode) nlj.getChild(0);
assertNull(seqScan.getPredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertNull(seqScan.getPredicate());
if (joinOp != JoinOp.EQUAL) {
// weaken test for now
return;
}
query = "SELECT b.A, a.* FROM R1 a LEFT OUTER JOIN R4 b ON b.A" + joinOp + "a.A AND b.C " + joinOp + " a.C AND a.D " + joinOp + " b.D WHERE b.A IS NULL";
pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
//* enable for debug */ System.out.println(pn.toExplainPlanString());
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
nlij = (NestLoopIndexPlanNode) node;
assertEquals(nlij.getJoinType(), JoinType.LEFT);
assertNull(nlij.getPreJoinPredicate());
assertNull(nlij.getJoinPredicate());
predicate = nlij.getWherePredicate();
assertExprTopDownTree(predicate, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE);
indexScan = nlij.getInlineIndexScan();
assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
predicate = indexScan.getEndExpression();
assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, ExpressionType.CONJUNCTION_AND, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(indexScan.getPredicate());
query = "SELECT b.A, a.* FROM R1 a LEFT OUTER JOIN R4 b ON b.A" + joinOp + "a.A AND b.C " + joinOp + " a.C AND a.D " + joinOp + " b.D WHERE b.B + b.A IS NULL";
pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
nlij = (NestLoopIndexPlanNode) node;
assertEquals(nlij.getJoinType(), JoinType.LEFT);
assertNull(nlij.getPreJoinPredicate());
assertNull(nlij.getJoinPredicate());
predicate = nlij.getWherePredicate();
assertExprTopDownTree(predicate, ExpressionType.OPERATOR_IS_NULL, ExpressionType.OPERATOR_PLUS, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
query = "SELECT a.* FROM R1 a LEFT OUTER JOIN R5 b ON b.A" + joinOp + "a.A WHERE b.A IS NULL";
pn = compileToTopDownTree(query, 3, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
nlij = (NestLoopIndexPlanNode) node;
assertEquals(nlij.getJoinType(), JoinType.LEFT);
assertNull(nlij.getPreJoinPredicate());
assertNull(nlij.getJoinPredicate());
predicate = nlij.getWherePredicate();
assertExprTopDownTree(predicate, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE);
}
Aggregations