Examples with AbstractPlanNode org.voltdb.plannodes.AbstractPlanNode used on opensource projects

Example 11 with AbstractPlanNode

use of org.voltdb.plannodes.AbstractPlanNode in project voltdb by VoltDB.

the class TestPlansGroupBy method testGroupByWithLimit.

// GROUP BY With LIMIT without ORDER BY
public void testGroupByWithLimit() {
    List<AbstractPlanNode> pns;
    // replicated table with serial aggregation and inlined limit
    pns = compileToFragments("SELECT F_PKEY FROM RF GROUP BY F_PKEY LIMIT 5");
    checkGroupByOnlyPlanWithLimit(pns, false, false, true, true);
    pns = compileToFragments("SELECT F_D1 FROM RF GROUP BY F_D1 LIMIT 5");
    checkGroupByOnlyPlanWithLimit(pns, false, false, true, true);
    // partitioned table with serial aggregation and inlined limit
    // group by columns contain the partition key is the only case allowed
    pns = compileToFragments("SELECT F_PKEY FROM F GROUP BY F_PKEY LIMIT 5");
    checkGroupByOnlyPlanWithLimit(pns, true, false, true, true);
    // Explain plan for the above query
    /*
           RETURN RESULTS TO STORED PROCEDURE
            LIMIT 5
             RECEIVE FROM ALL PARTITIONS

           RETURN RESULTS TO STORED PROCEDURE
            INDEX SCAN of "F" using its primary key index (for optimized grouping only)
             inline Serial AGGREGATION ops
              inline LIMIT 5
        */
    String expectedStr = "  inline Serial AGGREGATION ops: \n" + "   inline LIMIT 5";
    String explainPlan = "";
    for (AbstractPlanNode apn : pns) {
        explainPlan += apn.toExplainPlanString();
    }
    assertTrue(explainPlan.contains(expectedStr));
    pns = compileToFragments("SELECT A3, COUNT(*) FROM T3 GROUP BY A3 LIMIT 5");
    checkGroupByOnlyPlanWithLimit(pns, true, false, true, true);
    pns = compileToFragments("SELECT A3, B3, COUNT(*) FROM T3 GROUP BY A3, B3 LIMIT 5");
    checkGroupByOnlyPlanWithLimit(pns, true, false, true, true);
    pns = compileToFragments("SELECT A3, B3, COUNT(*) FROM T3 WHERE A3 > 1 GROUP BY A3, B3 LIMIT 5");
    checkGroupByOnlyPlanWithLimit(pns, true, false, true, true);
    //
    // negative tests
    //
    pns = compileToFragments("SELECT F_VAL2 FROM RF GROUP BY F_VAL2 LIMIT 5");
    checkGroupByOnlyPlanWithLimit(pns, false, true, true, false);
// Limit should not be pushed down for case like:
// Group by non-partition without partition key and order by.
// ENG-6485
}

Example 12 with AbstractPlanNode

use of org.voltdb.plannodes.AbstractPlanNode in project voltdb by VoltDB.

the class TestPlansGroupBy method checkGroupByOnlyPlanWithLimit.

// check group by query with limit
// Query has group by from partition column and limit, does not have order by
private void checkGroupByOnlyPlanWithLimit(List<AbstractPlanNode> pns, boolean twoFragments, boolean isHashAggregator, boolean isIndexScan, boolean inlineLimit) {
    // 'inlineLimit' means LIMIT gets pushed down for partition table and
    // inlined with aggregate.
    AbstractPlanNode apn = pns.get(0).getChild(0);
    if (!inlineLimit || twoFragments) {
        assertEquals(PlanNodeType.LIMIT, apn.getPlanNodeType());
        apn = apn.getChild(0);
    }
    // Group by partition column does not need top group by node.
    if (twoFragments) {
        apn = pns.get(1).getChild(0);
        if (!inlineLimit) {
            assertEquals(PlanNodeType.LIMIT, apn.getPlanNodeType());
            apn = apn.getChild(0);
        }
    }
    // For a single table aggregate, it is inline always.
    assertEquals((isIndexScan ? PlanNodeType.INDEXSCAN : PlanNodeType.SEQSCAN), apn.getPlanNodeType());
    if (isHashAggregator) {
        assertNotNull(apn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE));
    } else {
        assertNotNull(apn.getInlinePlanNode(PlanNodeType.AGGREGATE));
        if (inlineLimit) {
            AbstractPlanNode p = apn.getInlinePlanNode(PlanNodeType.AGGREGATE);
            assertNotNull(p.getInlinePlanNode(PlanNodeType.LIMIT));
        }
    }
}

Example 13 with AbstractPlanNode

use of org.voltdb.plannodes.AbstractPlanNode in project voltdb by VoltDB.

the class TestMultipleOuterJoinPlans method testFullJoinExpressions.

public void testFullJoinExpressions() {
    AbstractPlanNode pn;
    AbstractPlanNode n;
    // WHERE outer and inner expressions stay at the FULL NLJ node
    pn = compile("select * FROM  " + "R1 FULL JOIN R2 ON R1.A = R2.A WHERE R2.C IS NULL AND R1.C is NULL");
    n = pn.getChild(0).getChild(0);
    verifyJoinNode(n, PlanNodeType.NESTLOOP, JoinType.FULL, null, ExpressionType.COMPARE_EQUAL, ExpressionType.CONJUNCTION_AND, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    // WHERE outer and inner expressions stay at the FULL NLJ node
    // The outer node is a join itself
    pn = compile("select * FROM  " + "R1 JOIN R2 ON R1.A = R2.A FULL JOIN R3 ON R3.C = R2.C WHERE R1.C is NULL");
    n = pn.getChild(0).getChild(0);
    verifyJoinNode(n, PlanNodeType.NESTLOOP, JoinType.FULL, null, ExpressionType.COMPARE_EQUAL, ExpressionType.OPERATOR_IS_NULL, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN);
    // WHERE outer-inner expressions stay at the FULL NLJ node
    pn = compile("select * FROM  " + "R1 FULL JOIN R2 ON R1.A = R2.A WHERE R2.C IS NULL OR R1.C is NULL");
    n = pn.getChild(0).getChild(0);
    verifyJoinNode(n, PlanNodeType.NESTLOOP, JoinType.FULL, null, ExpressionType.COMPARE_EQUAL, ExpressionType.CONJUNCTION_OR, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    // WHERE outer and inner expressions push down process stops at the FULL join (R1,R2) node -
    // FULL join is itself an outer node
    pn = compile("select * FROM  " + "R1 FULL JOIN R2 ON R1.A = R2.A LEFT JOIN R3 ON R3.C = R2.C WHERE R1.C is NULL");
    n = pn.getChild(0).getChild(0);
    verifyJoinNode(n, PlanNodeType.NESTLOOP, JoinType.LEFT, null, ExpressionType.COMPARE_EQUAL, null, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN);
    n = n.getChild(0);
    verifyJoinNode(n, PlanNodeType.NESTLOOP, JoinType.FULL, null, ExpressionType.COMPARE_EQUAL, ExpressionType.OPERATOR_IS_NULL, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    // OUTER JOIN expression (R1.A > 0) is pre-predicate, inner and inner - outer expressions R3.C = R2.C AND R3.C < 0 are predicate
    pn = compile("select * FROM R1 JOIN R2 ON R1.A = R2.C FULL JOIN R3 ON R3.C = R2.C  AND R1.A > 0 AND R3.C < 0");
    n = pn.getChild(0).getChild(0);
    verifyJoinNode(n, PlanNodeType.NESTLOOP, JoinType.FULL, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.CONJUNCTION_AND, null, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, null, "R3");
    n = n.getChild(0);
    verifyJoinNode(n, PlanNodeType.NESTLOOP, JoinType.INNER, null, ExpressionType.COMPARE_EQUAL, null, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN, "R1", "R2");
    // NLJ JOIN outer expression is pre-join expression, NLJ JOIN inner expression together with
    // JOIN inner-outer one are part of the join predicate
    pn = compile("select * FROM  " + "R1 FULL JOIN R2 ON R1.A = R2.A AND R1.C = R2.C");
    n = pn.getChild(0).getChild(0);
    verifyJoinNode(n, PlanNodeType.NESTLOOP, JoinType.FULL, null, ExpressionType.CONJUNCTION_AND, null, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    // NLJ JOIN outer expression is pre-join expression, NLJ JOIN inner expression together with
    // JOIN inner-outer one are part of the join predicate
    pn = compile("select * FROM  " + "R1 FULL JOIN R2 ON R1.A = R2.A AND R1.C < 0 AND R2.C > 0");
    n = pn.getChild(0).getChild(0);
    verifyJoinNode(n, PlanNodeType.NESTLOOP, JoinType.FULL, ExpressionType.COMPARE_LESSTHAN, ExpressionType.CONJUNCTION_AND, null, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    // NLJ JOIN outer expression is pre-join expression, NLJ JOIN inner expression together with
    // JOIN inner-outer one are part of the join predicate
    pn = compile("select * FROM  " + "R1 JOIN R2 ON R1.A = R2.A FULL JOIN R3 ON R1.A = R3.C AND R1.C is NULL");
    n = pn.getChild(0).getChild(0);
    verifyJoinNode(n, PlanNodeType.NESTLOOP, JoinType.FULL, ExpressionType.OPERATOR_IS_NULL, ExpressionType.COMPARE_EQUAL, null, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN);
}

Example 14 with AbstractPlanNode

use of org.voltdb.plannodes.AbstractPlanNode in project voltdb by VoltDB.

the class TestMultipleOuterJoinPlans method testFullIndexJoinExpressions.

public void testFullIndexJoinExpressions() {
    AbstractPlanNode pn;
    AbstractPlanNode n;
    // Simple FULL NLIJ
    pn = compile("select * FROM  " + "R3 FULL JOIN R1 ON R3.A = R1.A WHERE R3.C IS NULL");
    n = pn.getChild(0).getChild(0);
    verifyJoinNode(n, PlanNodeType.NESTLOOPINDEX, JoinType.FULL, null, null, ExpressionType.OPERATOR_IS_NULL, PlanNodeType.SEQSCAN, PlanNodeType.INDEXSCAN);
    String json = (new PlanNodeTree(pn)).toJSONString();
    // Same Join as above but using FULL OUTER JOIN syntax
    pn = compile("select * FROM  " + "R3 FULL OUTER JOIN R1 ON R3.A = R1.A WHERE R3.C IS NULL");
    String json1 = (new PlanNodeTree(pn)).toJSONString();
    assertEquals(json, json1);
    // FULL NLJ. R3.A is an index column but R3.A > 0 expression is used as a PREDICATE only
    pn = compile("select * FROM  " + "R1 FULL JOIN R3 ON R3.C = R1.A AND R3.A > 0");
    n = pn.getChild(0).getChild(0);
    verifyJoinNode(n, PlanNodeType.NESTLOOP, JoinType.FULL, null, ExpressionType.CONJUNCTION_AND, null, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN, "R1", "R3");
    // FULL NLIJ, inner join R3.A > 0 is added as a post-predicate to the inline Index scan
    pn = compile("select * FROM R1 FULL JOIN R3 ON R3.A = R1.A AND R3.A > 55");
    n = pn.getChild(0).getChild(0);
    verifyJoinNode(n, PlanNodeType.NESTLOOPINDEX, JoinType.FULL, null, null, null, PlanNodeType.SEQSCAN, PlanNodeType.INDEXSCAN, "R1", "R3");
    verifyIndexScanNode(n.getInlinePlanNode(PlanNodeType.INDEXSCAN), IndexLookupType.EQ, ExpressionType.COMPARE_GREATERTHAN);
    // FULL NLIJ, inner join L.A > 0 is added as a pre-predicate to the NLIJ
    pn = compile("select * FROM R3 L FULL JOIN R3 R ON L.A = R.A AND L.A > 55");
    n = pn.getChild(0).getChild(0);
    verifyJoinNode(n, PlanNodeType.NESTLOOPINDEX, JoinType.FULL, ExpressionType.COMPARE_GREATERTHAN, null, null, PlanNodeType.SEQSCAN, PlanNodeType.INDEXSCAN, "L", "R");
    verifyIndexScanNode(n.getInlinePlanNode(PlanNodeType.INDEXSCAN), IndexLookupType.EQ, null);
    // FULL NLIJ, inner-outer join R3.c = R1.c is a post-predicate for the inline Index scan
    pn = compile("select * FROM R1 FULL JOIN R3 ON R3.A = R1.A AND R3.C = R1.C");
    n = pn.getChild(0).getChild(0);
    verifyJoinNode(n, PlanNodeType.NESTLOOPINDEX, JoinType.FULL, null, null, null, PlanNodeType.SEQSCAN, PlanNodeType.INDEXSCAN, "R1", "R3");
    verifyIndexScanNode(n.getInlinePlanNode(PlanNodeType.INDEXSCAN), IndexLookupType.EQ, ExpressionType.COMPARE_EQUAL);
    // FULL NLIJ, outer join (R1, R2) expression R1.A > 0 is a pre-predicate
    pn = compile("select * FROM R1 JOIN R2 ON R1.A = R2.C FULL JOIN R3 ON R3.A = R2.C  AND R1.A > 0");
    n = pn.getChild(0).getChild(0);
    verifyJoinNode(n, PlanNodeType.NESTLOOPINDEX, JoinType.FULL, ExpressionType.COMPARE_GREATERTHAN, null, null, PlanNodeType.NESTLOOP, PlanNodeType.INDEXSCAN, null, "R3");
    verifyIndexScanNode(n.getInlinePlanNode(PlanNodeType.INDEXSCAN), IndexLookupType.EQ, null);
    n = n.getChild(0);
    verifyJoinNode(n, PlanNodeType.NESTLOOP, JoinType.INNER, null, ExpressionType.COMPARE_EQUAL, null, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
}

Example 15 with AbstractPlanNode

use of org.voltdb.plannodes.AbstractPlanNode in project voltdb by VoltDB.

the class TestIndexSelection method testPartialIndexPredicateOnly.

public void testPartialIndexPredicateOnly() {
    AbstractPlanNode pn;
    // Partial index can be used solely to eliminate a post-filter
    // even when the indexed columns are irrelevant
    // CREATE INDEX partial_idx_3 ON c (b)  where d > 0;
    pn = compile("select * from c where d > 0");
    checkScanUsesIndex(pn, "PARTIAL_IDX_3");
    // CREATE UNIQUE INDEX z_full_idx_a ON c (a); takes precedence over the partial_idx_3
    // because indexed column (A) is part of the WHERE expressions
    pn = compile("select * from c where d > 0 and a < 0");
    checkScanUsesIndex(pn, "Z_FULL_IDX_A");
    // CREATE INDEX partial_idx_3 ON c (b)  where d > 0;
    pn = compile("select c.d from a join c on a.id = c.e and d > 0");
    pn = pn.getChild(0).getChild(0);
    assertEquals(PlanNodeType.NESTLOOPINDEX, pn.getPlanNodeType());
    checkScanUsesIndex(pn, "PARTIAL_IDX_3");
}