public void testInnerOuterJoin() {
AbstractPlanNode pn =
compile("select * FROM R1 INNER JOIN R2 ON R1.A = R2.A LEFT JOIN R3 ON R3.C = R2.C");
AbstractPlanNode n = pn.getChild(0).getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
NestLoopPlanNode nlj = (NestLoopPlanNode) n;
assertTrue(JoinType.LEFT == nlj.getJoinType());
assertTrue(nlj.getJoinPredicate() != null);
n = nlj.getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
nlj = (NestLoopPlanNode) n;
assertTrue(JoinType.INNER == nlj.getJoinType());
assertTrue(nlj.getJoinPredicate() != null);
pn = compile("select * FROM R1, R2 LEFT JOIN R3 ON R3.C = R2.C WHERE R1.A = R2.A");
n = pn.getChild(0).getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
nlj = (NestLoopPlanNode) n;
assertTrue(JoinType.LEFT == nlj.getJoinType());
assertTrue(nlj.getJoinPredicate() != null);
n = nlj.getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
nlj = (NestLoopPlanNode) n;
assertTrue(JoinType.INNER == nlj.getJoinType());
assertTrue(nlj.getJoinPredicate() != null);
}
protected String buildExplainPlan(List<AbstractPlanNode> planNodes) {
String explain = "";
for (AbstractPlanNode apn : planNodes) {
explain += apn.toExplainPlanString() + '\n';
}
return explain;
}
/// Extract a sorted de-duped vector of all the bound parameter indexes in a plan. Or null if
// none.
public int[] boundParamIndexes() {
if (parameters.length == 0) {
return null;
}
BitSet ints = new BitSet();
ArrayList<AbstractPlanNode> ixscans = rootPlanGraph.findAllNodesOfType(PlanNodeType.INDEXSCAN);
if (subPlanGraph != null) {
ixscans.addAll(subPlanGraph.findAllNodesOfType(PlanNodeType.INDEXSCAN));
}
for (AbstractPlanNode apn : ixscans) {
assert (apn instanceof IndexScanPlanNode);
IndexScanPlanNode ixs = (IndexScanPlanNode) apn;
setParamIndexes(ints, ixs.getBindings());
}
ArrayList<AbstractPlanNode> ixcounts =
rootPlanGraph.findAllNodesOfType(PlanNodeType.INDEXCOUNT);
if (subPlanGraph != null) {
ixcounts.addAll(subPlanGraph.findAllNodesOfType(PlanNodeType.INDEXCOUNT));
}
for (AbstractPlanNode apn : ixcounts) {
assert (apn instanceof IndexCountPlanNode);
IndexCountPlanNode ixc = (IndexCountPlanNode) apn;
setParamIndexes(ints, ixc.getBindings());
}
return bitSetToIntVector(ints);
}
@Override
protected AbstractPlanNode recursivelyApply(AbstractPlanNode planNode) {
assert (planNode != null);
// breadth first:
// find AggregatePlanNode with exactly one child
// where that child is an AbstractScanPlanNode.
// Inline any qualifying AggregatePlanNode to its AbstractScanPlanNode.
Queue<AbstractPlanNode> children = new LinkedList<AbstractPlanNode>();
children.add(planNode);
while (!children.isEmpty()) {
AbstractPlanNode plan = children.remove();
AbstractPlanNode newPlan = inlineAggregationApply(plan);
if (newPlan != plan) {
if (plan == planNode) {
planNode = newPlan;
} else {
planNode.replaceChild(plan, newPlan);
}
}
for (int i = 0; i < newPlan.getChildCount(); i++) {
children.add(newPlan.getChild(i));
}
}
return planNode;
}
/**
* Check if the aggregate node is pushed-down in the given plan. If the pushDownTypes is null, it
* assumes that the aggregate node should NOT be pushed-down.
*
* @param np The generated plan
* @param isMultiPart Whether or not the plan is distributed
* @param aggTypes The expected aggregate types for the original aggregate node.
* @param pushDownTypes The expected aggregate types for the top aggregate node after pushing the
* original aggregate node down.
*/
private void checkPushedDown(
List<AbstractPlanNode> pn,
boolean isMultiPart,
ExpressionType[] aggTypes,
ExpressionType[] pushDownTypes) {
assertTrue(pn.size() > 0);
AbstractPlanNode p = pn.get(0).getChild(0);
assertTrue(p instanceof AggregatePlanNode);
String fragmentString = p.toJSONString();
ExpressionType[] topTypes = (pushDownTypes != null) ? pushDownTypes : aggTypes;
for (ExpressionType type : topTypes) {
assertTrue(fragmentString.contains("\"AGGREGATE_TYPE\":\"" + type.toString() + "\""));
}
if (isMultiPart) {
assertTrue(pn.size() == 2);
p = pn.get(1).getChild(0);
} else {
p = p.getChild(0);
}
if (pushDownTypes == null) {
assertTrue(p instanceof AbstractScanPlanNode);
return;
}
assertTrue(p instanceof AggregatePlanNode);
fragmentString = p.toJSONString();
for (ExpressionType type : aggTypes) {
assertTrue(fragmentString.contains("\"AGGREGATE_TYPE\":\"" + type.toString() + "\""));
}
}
public void testEng585Plan() {
AbstractPlanNode pn = null;
pn =
compile(
"select max(s.int2) as foo from s, t where s.s_pk = t.s_pk and t.t_pk1 = ?;", 1, true);
if (pn != null) {
System.out.println(pn.toJSONString());
}
}
public void testMultiPartLimitPushdownByOne() {
List<AbstractPlanNode> pn =
compileToFragments("select A1, count(*) as tag from T1 group by A1 order by 1 limit 1");
for (AbstractPlanNode nd : pn) {
System.out.println("PlanNode Explain string:\n" + nd.toExplainPlanString());
assertTrue(nd.toExplainPlanString().contains("LIMIT"));
}
}
/**
* Find all the aggregate nodes in a fragment, whether they are hash, serial or partial.
*
* @param fragment Fragment to search for aggregate plan nodes
* @return a list of all the nodes we found
*/
protected static List<AbstractPlanNode> findAllAggPlanNodes(AbstractPlanNode fragment) {
List<AbstractPlanNode> aggNodes = fragment.findAllNodesOfType(PlanNodeType.AGGREGATE);
List<AbstractPlanNode> hashAggNodes = fragment.findAllNodesOfType(PlanNodeType.HASHAGGREGATE);
List<AbstractPlanNode> partialAggNodes =
fragment.findAllNodesOfType(PlanNodeType.PARTIALAGGREGATE);
aggNodes.addAll(hashAggNodes);
aggNodes.addAll(partialAggNodes);
return aggNodes;
}
public void testMultiTableJoinExpressions() {
AbstractPlanNode pn =
compile(
"select * FROM R1, R2 LEFT JOIN R3 ON R3.A = R2.C OR R3.A = R1.A WHERE R1.C = R2.C");
AbstractPlanNode n = pn.getChild(0).getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
NestLoopPlanNode nlj = (NestLoopPlanNode) n;
assertTrue(JoinType.LEFT == nlj.getJoinType());
assertTrue(nlj.getJoinPredicate() != null);
AbstractExpression p = nlj.getJoinPredicate();
assertEquals(ExpressionType.CONJUNCTION_OR, p.getExpressionType());
}
/**
* Check if the distinct node is pushed-down in the given plan.
*
* @param np The generated plan
* @param isMultiPart Whether or not the plan is distributed
*/
private void checkPushedDownDistinct(List<AbstractPlanNode> pn, boolean isMultiPart) {
assertTrue(pn.size() > 0);
AbstractPlanNode p = pn.get(0).getChild(0).getChild(0);
assertTrue(p instanceof DistinctPlanNode);
assertTrue(p.toJSONString().contains("\"DISTINCT\""));
if (isMultiPart) {
assertTrue(pn.size() == 2);
p = pn.get(1).getChild(0);
assertTrue(p instanceof DistinctPlanNode);
assertTrue(p.toJSONString().contains("\"DISTINCT\""));
}
}
private int resetPlanNodeIds(AbstractPlanNode node, int nextId) {
nextId = node.overrideId(nextId);
for (AbstractPlanNode inNode : node.getInlinePlanNodes().values()) {
// Inline nodes also need their ids to be overridden to make sure
// the subquery node ids are also globaly unique
nextId = resetPlanNodeIds(inNode, nextId);
}
for (int i = 0; i < node.getChildCount(); i++) {
AbstractPlanNode child = node.getChild(i);
assert (child != null);
nextId = resetPlanNodeIds(child, nextId);
}
return nextId;
}
@Override
public String toString() {
if (rootPlanGraph != null) {
return "CompiledPlan: \n" + rootPlanGraph.toExplainPlanString();
} else {
return "CompiledPlan: [null plan graph]";
}
}
AbstractPlanNode inlineAggregationApply(AbstractPlanNode plan) {
// check for an aggregation of the right form
if ((plan instanceof AggregatePlanNode) == false || (plan instanceof WindowFunctionPlanNode)) {
return plan;
}
assert (plan.getChildCount() == 1);
AggregatePlanNode aggplan = (AggregatePlanNode) plan;
// Assuming all AggregatePlanNode has not been inlined before this microoptimization
AbstractPlanNode child = aggplan.getChild(0);
// EE Currently support: seqscan + indexscan
if (child.getPlanNodeType() != PlanNodeType.SEQSCAN
&& child.getPlanNodeType() != PlanNodeType.INDEXSCAN
&& child.getPlanNodeType() != PlanNodeType.NESTLOOP
&& child.getPlanNodeType() != PlanNodeType.NESTLOOPINDEX) {
return plan;
}
if (child.getPlanNodeType() == PlanNodeType.INDEXSCAN) {
// Currently do not conflict with the optimized MIN/MAX
// because of the big amount of tests changed.
IndexScanPlanNode isp = (IndexScanPlanNode) child;
LimitPlanNode limit = (LimitPlanNode) isp.getInlinePlanNode(PlanNodeType.LIMIT);
if (limit != null && (aggplan.isTableMin() || aggplan.isTableMax())) {
// Optimized MIN/MAX
if (limit.getLimit() == 1 && limit.getOffset() == 0) {
return plan;
}
}
}
// Inline aggregate node
AbstractPlanNode parent = null;
if (aggplan.getParentCount() == 1) {
parent = aggplan.getParent(0);
}
child.addInlinePlanNode(aggplan);
child.clearParents();
if (parent != null) {
parent.replaceChild(aggplan, child);
}
return child;
}
/**
* Given a list of Class objects for plan node subclasses, asserts if the given plan doesn't
* contain instances of those classes.
*/
protected static void assertClassesMatchNodeChain(
List<Class<? extends AbstractPlanNode>> expectedClasses, AbstractPlanNode actualPlan) {
AbstractPlanNode pn = actualPlan;
for (Class<? extends AbstractPlanNode> c : expectedClasses) {
assertFalse("Actual plan shorter than expected", pn == null);
assertTrue(
"Expected plan to contain an instance of "
+ c.getSimpleName()
+ ", "
+ "instead found "
+ pn.getClass().getSimpleName(),
c.isInstance(pn));
if (pn.getChildCount() > 0) pn = pn.getChild(0);
else pn = null;
}
assertTrue("Actual plan longer than expected", pn == null);
}
public int countSeqScans() {
int total = rootPlanGraph.findAllNodesOfType(PlanNodeType.SEQSCAN).size();
if (subPlanGraph != null) {
total += subPlanGraph.findAllNodesOfType(PlanNodeType.SEQSCAN).size();
}
// add full index scans
ArrayList<AbstractPlanNode> indexScanNodes =
rootPlanGraph.findAllNodesOfType(PlanNodeType.INDEXSCAN);
if (subPlanGraph != null) {
indexScanNodes.addAll(subPlanGraph.findAllNodesOfType(PlanNodeType.INDEXSCAN));
}
for (AbstractPlanNode node : indexScanNodes) {
if (((IndexScanPlanNode) node).getSearchKeyExpressions().isEmpty()) {
total++;
}
}
return total;
}
public void testOuterSimplificationJoin() {
// NULL_rejection simplification is the first transformation -
// before the LEFT-to-RIGHT and the WHERE expressions push down
AbstractPlanNode pn =
compile("select * FROM R1, R3 RIGHT JOIN R2 ON R1.A = R2.A WHERE R3.C = R1.C");
AbstractPlanNode n = pn.getChild(0).getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
NestLoopPlanNode nlj = (NestLoopPlanNode) n;
assertTrue(JoinType.INNER == nlj.getJoinType());
assertTrue(nlj.getJoinPredicate() != null);
n = nlj.getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
nlj = (NestLoopPlanNode) n;
assertTrue(JoinType.INNER == nlj.getJoinType());
assertTrue(nlj.getJoinPredicate() != null);
// The second R3.C = R2.C join condition is NULL-rejecting for the first LEFT join
pn = compile("select * FROM R1 LEFT JOIN R2 ON R1.A = R2.A LEFT JOIN R3 ON R3.C = R2.C");
n = pn.getChild(0).getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
nlj = (NestLoopPlanNode) n;
assertTrue(JoinType.LEFT == nlj.getJoinType());
assertTrue(nlj.getJoinPredicate() != null);
n = nlj.getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
nlj = (NestLoopPlanNode) n;
assertTrue(JoinType.LEFT == nlj.getJoinType());
assertTrue(nlj.getJoinPredicate() != null);
// The second R3.C = R2.C join condition is NULL-rejecting for the first LEFT join
pn = compile("select * FROM R1 LEFT JOIN R2 ON R1.A = R2.A RIGHT JOIN R3 ON R3.C = R2.C");
n = pn.getChild(0).getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
nlj = (NestLoopPlanNode) n;
assertTrue(JoinType.LEFT == nlj.getJoinType());
assertTrue(nlj.getJoinPredicate() != null);
n = nlj.getChild(1);
assertTrue(n instanceof NestLoopPlanNode);
nlj = (NestLoopPlanNode) n;
assertTrue(JoinType.INNER == nlj.getJoinType());
assertTrue(nlj.getJoinPredicate() != null);
}
public static AbstractPlanNode replaceInsertPlanNodeWithUpsert(AbstractPlanNode root) {
if (root == null) {
return null;
}
List<AbstractPlanNode> inserts = root.findAllNodesOfType(PlanNodeType.INSERT);
if (inserts.size() == 1) {
InsertPlanNode insertNode = (InsertPlanNode) inserts.get(0);
insertNode.setUpsert(true);
}
return root;
}
public static boolean hasInlineLimit(AbstractPlanNode node) {
AbstractPlanNode inlineNode = node.getInlinePlanNode(PlanNodeType.LIMIT);
if (inlineNode != null) {
assert (inlineNode instanceof LimitPlanNode);
// Table count with limit greater than 0 will not make a difference.
// The better way is to check m_limit and return true ONLY for m_limit == 0.
// However, the parameterized plan make is more complicated.
// Be conservative about the silly query without wrong answers now.
return true;
}
return false;
}
/**
* Parse a SQL literal statement into an unplanned, intermediate representation. This is normally
* followed by a call to {@link this#plan(AbstractCostModel, String, String, String, String, int,
* ScalarValueHints[]) }, but splitting these two affords an opportunity to check a cache for a
* plan matching the auto-parameterized parsed statement.
*/
public void parse() throws PlanningErrorException {
// reset any error message
m_recentErrorMsg = null;
// Reset plan node ids to start at 1 for this plan
AbstractPlanNode.resetPlanNodeIds();
// determine the type of the query
//
// (Hmmm... seems like this pre-processing of the SQL text
// and subsequent placement of UPSERT_TAG should be pushed down
// into getXMLCompiledStatement)
m_sql = m_sql.trim();
if (m_sql.length() > 6
&& m_sql.substring(0, 6).toUpperCase().startsWith("UPSERT")) { // ENG-7395
m_isUpsert = true;
m_sql = "INSERT" + m_sql.substring(6);
}
// use HSQLDB to get XML that describes the semantics of the statement
// this is much easier to parse than SQL and is checked against the catalog
try {
m_xmlSQL = m_HSQL.getXMLCompiledStatement(m_sql);
} catch (HSQLParseException e) {
// XXXLOG probably want a real log message here
throw new PlanningErrorException(e.getMessage());
}
if (m_isUpsert) {
assert (m_xmlSQL.name.equalsIgnoreCase("INSERT"));
// for AdHoc cache distinguish purpose which is based on the XML
m_xmlSQL.attributes.put(UPSERT_TAG, "true");
}
m_planSelector.outputCompiledStatement(m_xmlSQL);
}
@Override
protected AbstractPlanNode recursivelyApply(AbstractPlanNode plan) {
assert (plan != null);
// depth first:
// find AggregatePlanNode with exactly one child
// where that child is an AbstractScanPlanNode.
// Replace any qualifying AggregatePlanNode / AbstractScanPlanNode pair
// with an IndexCountPlanNode or TableCountPlanNode
ArrayList<AbstractPlanNode> children = new ArrayList<AbstractPlanNode>();
for (int i = 0; i < plan.getChildCount(); i++) children.add(plan.getChild(i));
for (AbstractPlanNode child : children) {
// TODO this will break when children feed multiple parents
AbstractPlanNode newChild = recursivelyApply(child);
// Do a graft into the (parent) plan only if a replacement for a child was found.
if (newChild == child) {
continue;
}
boolean replaced = plan.replaceChild(child, newChild);
assert (true == replaced);
}
// check for an aggregation of the right form
if ((plan instanceof AggregatePlanNode) == false) return plan;
assert (plan.getChildCount() == 1);
AggregatePlanNode aggplan = (AggregatePlanNode) plan;
// ENG-6131 fixed here.
if (!(aggplan.isTableCountStar()
|| aggplan.isTableNonDistinctCountConstant()
|| aggplan.isTableCountNonDistinctNullableColumn())) {
return plan;
}
AbstractPlanNode child = plan.getChild(0);
// A table count can replace a seq scan only if it has no predicates.
if (child instanceof SeqScanPlanNode) {
if (((SeqScanPlanNode) child).getPredicate() != null) {
return plan;
}
AbstractExpression postPredicate = aggplan.getPostPredicate();
if (postPredicate != null) {
List<AbstractExpression> aggList =
postPredicate.findAllSubexpressionsOfClass(AggregateExpression.class);
boolean allCountStar = true;
for (AbstractExpression expr : aggList) {
if (expr.getExpressionType() != ExpressionType.AGGREGATE_COUNT_STAR) {
allCountStar = false;
break;
}
}
if (allCountStar) {
return plan;
}
}
if (hasInlineLimit(aggplan)) {
// table count EE executor does not handle inline limit stuff
return plan;
}
return new TableCountPlanNode((AbstractScanPlanNode) child, aggplan);
}
// Otherwise, optimized counts only replace particular cases of index scan.
if ((child instanceof IndexScanPlanNode) == false) return plan;
IndexScanPlanNode isp = (IndexScanPlanNode) child;
// Guard against (possible future?) cases of indexable subquery.
if (((IndexScanPlanNode) child).isSubQuery()) {
return plan;
}
// An index count or table count can replace an index scan only if it has no (post-)predicates
// except those (post-)predicates are artifact predicates we added for reverse scan purpose only
if (isp.getPredicate() != null && !isp.isPredicatesOptimizableForAggregate()) {
return plan;
}
// With no start or end keys, there's not much a counting index can do.
if (isp.getEndExpression() == null && isp.getSearchKeyExpressions().size() == 0) {
// An indexed query without a where clause can fall back to a plain old table count.
// This can only happen when a confused query like
// "select count(*) from table order by index_key;"
// meets a naive planner that doesn't just cull the no-op ORDER BY. Who, us?
if (hasInlineLimit(aggplan)) {
return plan;
}
return new TableCountPlanNode(isp, aggplan);
}
// check for the index's support for counting
Index idx = isp.getCatalogIndex();
if (!idx.getCountable()) {
return plan;
}
// The core idea is that counting index needs to know the start key and end key to
// jump to to get counts instead of actually doing any scanning.
// Options to be determined are:
// - whether each of the start/end keys is missing, partial (a prefix of a compund key), or
// complete,
// - whether the count should include or exclude entries exactly matching each of the start/end
// keys.
// Not all combinations of these options are supported;
// unsupportable cases cause the factory method to return null.
IndexCountPlanNode countingPlan = IndexCountPlanNode.createOrNull(isp, aggplan);
if (countingPlan == null) {
return plan;
}
return countingPlan;
}
protected void printExplainPlan(List<AbstractPlanNode> planNodes) {
for (AbstractPlanNode apn : planNodes) {
System.out.println(apn.toExplainPlanString());
}
}
public void testBasicUpdateAndDelete() {
// select * with ON clause should return all columns from all tables
AbstractPlanNode n;
AbstractPlanNode pn;
pns = compileToFragments("UPDATE R1 SET C = 1 WHERE C = 0");
pn = pns.get(0);
System.out.println(pn.toExplainPlanString());
n = pn.getChild(0).getChild(0);
assertTrue(n instanceof ReceivePlanNode);
pn = pns.get(1);
n = pn.getChild(0);
assertTrue(n instanceof UpdatePlanNode);
pns = compileToFragments("DELETE FROM R1 WHERE C = 0");
pn = pns.get(0);
System.out.println(pn.toExplainPlanString());
n = pn.getChild(0).getChild(0);
assertTrue(n instanceof ReceivePlanNode);
pn = pns.get(1);
n = pn.getChild(0);
assertTrue(n instanceof DeletePlanNode);
pns = compileToFragments("INSERT INTO R1 VALUES (1, 2, 3)");
pn = pns.get(0);
System.out.println(pn.toExplainPlanString());
n = pn.getChild(0).getChild(0);
assertTrue(n instanceof ReceivePlanNode);
pn = pns.get(1);
n = pn.getChild(0);
assertTrue(n instanceof InsertPlanNode);
pns = compileToFragments("UPDATE P1 SET C = 1 WHERE C = 0");
pn = pns.get(0);
System.out.println(pn.toExplainPlanString());
n = pn.getChild(0).getChild(0);
assertTrue(n instanceof ReceivePlanNode);
pn = pns.get(1);
n = pn.getChild(0);
assertTrue(n instanceof UpdatePlanNode);
pns = compileToFragments("DELETE FROM P1 WHERE C = 0");
pn = pns.get(0);
System.out.println(pn.toExplainPlanString());
n = pn.getChild(0).getChild(0);
assertTrue(n instanceof ReceivePlanNode);
pn = pns.get(1);
n = pn.getChild(0);
assertTrue(n instanceof DeletePlanNode);
pns = compileToFragments("UPDATE P1 SET C = 1 WHERE A = 0");
pn = pns.get(0);
System.out.println(pn.toExplainPlanString());
// n = pn.getChild(0);
assertTrue(pn instanceof UpdatePlanNode);
pns = compileToFragments("DELETE FROM P1 WHERE A = 0");
pn = pns.get(0);
System.out.println(pn.toExplainPlanString());
// n = pn.getChild(0);
assertTrue(pn instanceof DeletePlanNode);
pns = compileToFragments("INSERT INTO P1 VALUES (1, 2)");
pn = pns.get(0);
System.out.println(pn.toExplainPlanString());
// n = pn.getChild(0).getChild(0);
assertTrue(pn instanceof InsertPlanNode);
}
public void testPushDownExprJoin() {
// R3.A > 0 gets pushed down all the way to the R3 scan node and used as an index
AbstractPlanNode pn =
compile("select * FROM R3, R2 LEFT JOIN R1 ON R1.C = R2.C WHERE R3.C = R2.C AND R3.A > 0");
AbstractPlanNode n = pn.getChild(0).getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
NestLoopPlanNode nlj = (NestLoopPlanNode) n;
assertTrue(JoinType.LEFT == nlj.getJoinType());
assertTrue(nlj.getJoinPredicate() != null);
n = nlj.getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
nlj = (NestLoopPlanNode) n;
assertTrue(JoinType.INNER == nlj.getJoinType());
assertTrue(nlj.getJoinPredicate() != null);
n = nlj.getChild(0);
assertTrue(n instanceof IndexScanPlanNode);
// R3.A > 0 is now outer join expresion and must stay at the LEF join
pn =
compile("select * FROM R3, R2 LEFT JOIN R1 ON R1.C = R2.C AND R3.A > 0 WHERE R3.C = R2.C");
n = pn.getChild(0).getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
nlj = (NestLoopPlanNode) n;
assertTrue(JoinType.LEFT == nlj.getJoinType());
assertTrue(nlj.getJoinPredicate() != null);
n = nlj.getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
nlj = (NestLoopPlanNode) n;
assertTrue(JoinType.INNER == nlj.getJoinType());
assertTrue(nlj.getJoinPredicate() != null);
n = nlj.getChild(0);
assertTrue(n instanceof SeqScanPlanNode);
pn =
compile(
"select * FROM R3 JOIN R2 ON R3.C = R2.C RIGHT JOIN R1 ON R1.C = R2.C AND R3.A > 0");
n = pn.getChild(0).getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
nlj = (NestLoopPlanNode) n;
assertTrue(JoinType.LEFT == nlj.getJoinType());
assertTrue(nlj.getJoinPredicate() != null);
n = nlj.getChild(1);
assertTrue(n instanceof NestLoopPlanNode);
nlj = (NestLoopPlanNode) n;
assertTrue(JoinType.INNER == nlj.getJoinType());
assertTrue(nlj.getJoinPredicate() != null);
n = nlj.getChild(0);
assertTrue(n instanceof SeqScanPlanNode);
// R3.A > 0 gets pushed down all the way to the R3 scan node and used as an index
pn = compile("select * FROM R2, R3 LEFT JOIN R1 ON R1.C = R2.C WHERE R3.C = R2.C AND R3.A > 0");
n = pn.getChild(0).getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
nlj = (NestLoopPlanNode) n;
assertTrue(JoinType.LEFT == nlj.getJoinType());
assertTrue(nlj.getJoinPredicate() != null);
n = nlj.getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
nlj = (NestLoopPlanNode) n;
assertTrue(JoinType.INNER == nlj.getJoinType());
assertTrue(nlj.getJoinPredicate() != null);
n = nlj.getChild(1);
assertTrue(n instanceof IndexScanPlanNode);
// R3.A = R2.C gets pushed down to the R2, R3 join node scan node and used as an index
pn = compile("select * FROM R2, R3 LEFT JOIN R1 ON R1.C = R2.C WHERE R3.A = R2.C");
n = pn.getChild(0).getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
nlj = (NestLoopPlanNode) n;
assertTrue(JoinType.LEFT == nlj.getJoinType());
assertTrue(nlj.getJoinPredicate() != null);
n = nlj.getChild(0);
assertTrue(n instanceof NestLoopIndexPlanNode);
NestLoopIndexPlanNode nlij = (NestLoopIndexPlanNode) n;
assertTrue(JoinType.INNER == nlij.getJoinType());
}
public void testMultitableDistributedJoin() {
// One distributed table
List<AbstractPlanNode> lpn =
compileToFragments("select * FROM R3,R1 LEFT JOIN P2 ON R3.A = P2.A WHERE R3.A=R1.A ");
assertTrue(lpn.size() == 2);
AbstractPlanNode n = lpn.get(0).getChild(0).getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
assertTrue(JoinType.LEFT == ((NestLoopPlanNode) n).getJoinType());
AbstractPlanNode c = n.getChild(0);
assertTrue(c instanceof NestLoopIndexPlanNode);
// R3.A and P2.A have an index. P2,R1 is NLIJ/inlined IndexScan because it's an inner join even
// P2 is distributed
lpn = compileToFragments("select * FROM P2,R1 LEFT JOIN R3 ON R3.A = P2.A WHERE P2.A=R1.A ");
assertTrue(lpn.size() == 2);
n = lpn.get(0).getChild(0).getChild(0);
assertTrue(n instanceof ReceivePlanNode);
n = lpn.get(1).getChild(0);
assertTrue(n instanceof NestLoopIndexPlanNode);
assertTrue(JoinType.LEFT == ((NestLoopIndexPlanNode) n).getJoinType());
c = n.getChild(0);
assertTrue(c instanceof NestLoopIndexPlanNode);
// R3.A has an index. R3,P2 is NLJ because it's an outer join and P2 is distributed
lpn = compileToFragments("select * FROM R3,R1 LEFT JOIN P2 ON R3.A = P2.A WHERE R3.A=R1.A ");
assertTrue(lpn.size() == 2);
// to debug */ System.out.println("DEBUG 0.0: " + lpn.get(0).toExplainPlanString());
// to debug */ System.out.println("DEBUG 0.1: " + lpn.get(1).toExplainPlanString());
n = lpn.get(0).getChild(0).getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
assertTrue(JoinType.LEFT == ((NestLoopPlanNode) n).getJoinType());
c = n.getChild(0);
assertTrue(c instanceof NestLoopIndexPlanNode);
assertTrue(JoinType.INNER == ((NestLoopIndexPlanNode) c).getJoinType());
c = n.getChild(1);
assertTrue(c instanceof ReceivePlanNode);
n = lpn.get(1).getChild(0);
// For determinism reason
assertTrue(n instanceof IndexScanPlanNode);
// R3.A has an index. P2,R1 is NLJ because P2 is distributed and it's an outer join
lpn = compileToFragments("select * FROM R1 LEFT JOIN P2 ON R1.A = P2.A, R3 WHERE R1.A=R3.A ");
assertTrue(lpn.size() == 2);
// to debug */ System.out.println("DEBUG 1.0: " + lpn.get(0).toExplainPlanString());
// to debug */ System.out.println("DEBUG 1.1: " + lpn.get(1).toExplainPlanString());
n = lpn.get(0).getChild(0).getChild(0);
assertTrue(n instanceof NestLoopIndexPlanNode);
assertTrue(JoinType.INNER == ((NestLoopIndexPlanNode) n).getJoinType());
n = n.getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
c = n.getChild(0);
assertTrue(c instanceof SeqScanPlanNode);
c = n.getChild(1);
assertTrue(c instanceof ReceivePlanNode);
n = lpn.get(1).getChild(0);
// For determinism reason
assertTrue(n instanceof IndexScanPlanNode);
// Two distributed table
lpn = compileToFragments("select * FROM R3,P1 LEFT JOIN P2 ON R3.A = P2.A WHERE R3.A=P1.A ");
assertTrue(lpn.size() == 2);
n = lpn.get(0).getChild(0).getChild(0);
assertTrue(n instanceof ReceivePlanNode);
n = lpn.get(1).getChild(0);
assertTrue(JoinType.LEFT == ((NestLoopIndexPlanNode) n).getJoinType());
c = n.getChild(0);
assertTrue(c instanceof NestLoopIndexPlanNode);
assertTrue(JoinType.INNER == ((NestLoopIndexPlanNode) c).getJoinType());
}
/**
* Accessor for description of plan non-determinism. Note that we prefer the content determinism
* message to the rootPlanGraph's message.
*
* @return the corresponding value from the first fragment
*/
public String nondeterminismDetail() {
if (!isContentDeterministic()) {
return m_contentDeterminismDetail;
}
return rootPlanGraph.nondeterminismDetail();
}
/**
* Accessor for flag marking the plan as guaranteeing an identical result/effect when "replayed"
* against the same database state, such as during replication or CL recovery.
*
* @return the corresponding value from the first fragment
*/
public boolean isOrderDeterministic() {
if (m_statementIsOrderDeterministic) {
return true;
}
return rootPlanGraph.isOrderDeterministic();
}
