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

Example 11 with NestLoopPlanNode

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

the class TestPlansJoin method testUsingColumns.

public void testUsingColumns() {
    String query;
    AbstractPlanNode pn;
    OrderByPlanNode orderBy;
    NestLoopPlanNode nlj;
    AggregatePlanNode aggr;
    List<SchemaColumn> selectColumns;
    SchemaColumn col;
    AbstractExpression colExp;
    AbstractExpression predicate;
    // Test USING column
    query = "SELECT MAX(R1.A), C FROM R1 FULL JOIN R2 USING (C) " + "WHERE C > 0 GROUP BY C ORDER BY C";
    pn = compileToTopDownTree(query, 2, PlanNodeType.SEND, PlanNodeType.ORDERBY, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    // ORDER BY column
    orderBy = (OrderByPlanNode) pn.getChild(0);
    List<AbstractExpression> s = orderBy.getSortExpressions();
    assertEquals(1, s.size());
    assertEquals(ExpressionType.VALUE_TUPLE, s.get(0).getExpressionType());
    // WHERE
    nlj = (NestLoopPlanNode) orderBy.getChild(0);
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    predicate = nlj.getWherePredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.OPERATOR_CASE_WHEN, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE, ExpressionType.OPERATOR_ALTERNATIVE, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    // GROUP BY
    aggr = (AggregatePlanNode) nlj.getInlinePlanNode(PlanNodeType.HASHAGGREGATE);
    assertNotNull(aggr);
    List<AbstractExpression> g = aggr.getGroupByExpressions();
    assertEquals(1, g.size());
    assertExprTopDownTree(g.get(0), ExpressionType.OPERATOR_CASE_WHEN, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE, ExpressionType.OPERATOR_ALTERNATIVE, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    // Test three table full join
    query = "SELECT C FROM R1 FULL JOIN R2 USING (C) FULL JOIN R3 USING (C)";
    pn = compileToTopDownTree(query, 1, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    selectColumns = pn.getOutputSchema().getColumns();
    col = selectColumns.get(0);
    assertEquals("C", col.getColumnAlias());
    colExp = col.getExpression();
    assertEquals(ExpressionType.VALUE_TUPLE, colExp.getExpressionType());
    // Test three table INNER join. USING C column should be resolved
    query = "SELECT C FROM R1 JOIN R2 USING (C) JOIN R3 USING (C)";
    pn = compileToTopDownTree(query, 1, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    selectColumns = pn.getOutputSchema().getColumns();
    assertEquals(1, selectColumns.size());
    col = selectColumns.get(0);
    assertEquals("C", col.getColumnAlias());
    colExp = col.getExpression();
    assertEquals(ExpressionType.VALUE_TUPLE, colExp.getExpressionType());
    // Test two table LEFT join. USING C column should be resolved
    query = "SELECT C FROM R1 LEFT JOIN R2 USING (C)";
    pn = compileToTopDownTree(query, 1, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    selectColumns = pn.getOutputSchema().getColumns();
    assertEquals(1, selectColumns.size());
    col = selectColumns.get(0);
    assertEquals("C", col.getColumnAlias());
    colExp = col.getExpression();
    assertEquals(ExpressionType.VALUE_TUPLE, colExp.getExpressionType());
    // Test two table RIGHT join. USING C column should be resolved
    query = "SELECT C FROM R1 RIGHT JOIN R2 USING (C)";
    pn = compileToTopDownTree(query, 1, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    selectColumns = pn.getOutputSchema().getColumns();
    assertEquals(1, selectColumns.size());
    col = selectColumns.get(0);
    assertEquals("C", col.getColumnAlias());
    colExp = col.getExpression();
    assertEquals(ExpressionType.VALUE_TUPLE, colExp.getExpressionType());
}

Example 12 with NestLoopPlanNode

use of org.voltdb.plannodes.NestLoopPlanNode 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());
}

Example 13 with NestLoopPlanNode

use of org.voltdb.plannodes.NestLoopPlanNode 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);
}

Example 14 with NestLoopPlanNode

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

the class TestPlansInExistsSubQueries method testExistsSimplification.

public void testExistsSimplification() {
    AbstractPlanNode pn;
    AbstractJoinPlanNode jpn;
    // LIMIT is 0 EXISTS => FALSE
    pn = compile("select a from r1 where exists " + " (select a, c  from r2 limit 0) ");
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    verifyCVEPredicate(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), false);
    // LIMIT is 0 EXISTS => FALSE
    pn = compile("select a from r1 where exists " + " (select count(*)  from r2 limit 0) ");
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    verifyCVEPredicate(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), false);
    //EXISTS => TRUE, join predicate is TRUE or EXPR = > TRUE and dropped
    pn = compile("select r1.a from r1 join r2 on (exists " + " (select max(a)  from r2) or r2.a > 0)");
    assertTrue(pn.getChild(0).getChild(0) instanceof AbstractJoinPlanNode);
    jpn = (AbstractJoinPlanNode) pn.getChild(0).getChild(0);
    assertTrue(jpn.getWherePredicate() == null);
    //EXISTS => FALSE, join predicate is retained
    pn = compile("select r1.a from r1 join r2 on exists " + " (select max(a)  from r2 offset 1) ");
    assertTrue(pn.getChild(0).getChild(0) instanceof NestLoopPlanNode);
    jpn = (NestLoopPlanNode) pn.getChild(0).getChild(0);
    verifyCVEPredicate(jpn.getJoinPredicate(), false);
    // table-agg-without-having-groupby OFFSET > 0 => FALSE
    pn = compile("select a from r1 where exists " + " (select count(*)  from r2 offset 1) ");
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    verifyCVEPredicate(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), false);
    // table-agg-without-having-groupby  => TRUE
    pn = compile("select a from r1 where exists " + " (select max(a)  from r2) ");
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    assertTrue(((SeqScanPlanNode) pn.getChild(0)).getPredicate() == null);
    // table-agg-without-having-groupby by limit is a parameter => EXISTS
    pn = compile("select a from r1 where exists " + " (select max(a)  from r2 limit ?) ");
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    AbstractExpression pred = ((SeqScanPlanNode) pn.getChild(0)).getPredicate();
    assertNotNull(pred);
    assertEquals(ExpressionType.OPERATOR_EXISTS, pred.getExpressionType());
    // Subquery => select 1 from r2 limit 1 offset 2
    pn = compile("select a from r1 where exists " + " (select a, c  from r2 order by a offset 2) ");
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    verifyTrivialSchemaLimitOffset(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), 1, 2);
    // User's limit ?
    // Subquery => EXISTS (select 1 from r2 limit ?)
    pn = compile("select a from r1 where exists " + " (select a, c  from r2 order by a limit ?) ");
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    verifyTrivialSchemaLimitOffset(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), -1, 0);
    // Subquery subquery-without-having with group by and no limit
    //   => select a, max(c) from r2 group by a limit 1
    pn = compile("select a from r1 where exists " + " (select a, max(c) from r2 group by a order by max(c))");
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    verifyAggregateSubquery(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), 2, 1, false);
    // Subquery subquery-without-having with group by and offset 3 => subquery-without-having with group by and offset 3
    pn = compile("select a from r1 where exists " + " (select a, max(c) from r2 group by a order by max(c) offset 2)");
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    verifyAggregateSubquery(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), 2, 1, false);
    // Subquery subquery-with-having with group by => subquery-with-having with group by
    pn = compile("select a from r1 where exists " + " (select a, max(c) from r2 group by a having max(c) > 2 order by max(c))");
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    // weakened for now around the unification of the input column to the HAVING clause:
    verifyAggregateSubquery(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), 2, 1, true);
//verifyAggregateSubquery(((SeqScanPlanNode)pn.getChild(0)).getPredicate(), 3, 1, true);
}

Example 15 with NestLoopPlanNode

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

the class TestPlansJoin method perJoinOpTestBasicIndexOuterJoin.

private void perJoinOpTestBasicIndexOuterJoin(JoinOp joinOp) {
    // R3 is indexed but it's the outer table and the join expression
    // must stay at the NLJ so the index can't be used
    String query;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    NestLoopIndexPlanNode nlij;
    NestLoopPlanNode nlj;
    SeqScanPlanNode seqScan;
    IndexScanPlanNode indexScan;
    AbstractExpression predicate;
    query = "SELECT * FROM R3 LEFT JOIN R2 ON R3.A" + joinOp + "R2.C";
    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(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("R3", seqScan.getTargetTableName());
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertEquals("R2", seqScan.getTargetTableName());
    // R3 is indexed but it's the outer table so index can't be used
    query = "SELECT * FROM R2 RIGHT JOIN R3 ON R3.A" + joinOp + "R2.C";
    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(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("R3", seqScan.getTargetTableName());
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertEquals("R2", seqScan.getTargetTableName());
    if (joinOp != JoinOp.EQUAL) {
        // weaken test for now
        return;
    }
    query = "SELECT * FROM R2 LEFT JOIN R3 ON R2.C" + joinOp + "R3.A";
    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(JoinType.LEFT, nlij.getJoinType());
    seqScan = (SeqScanPlanNode) nlij.getChild(0);
    assertEquals("R2", seqScan.getTargetTableName());
    indexScan = nlij.getInlineIndexScan();
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(indexScan.getPredicate());
    assertEquals("R3", indexScan.getTargetTableName());
}