Examples with AbstractExpression org.voltdb.expressions.AbstractExpression used on opensource projects

Example 66 with AbstractExpression

use of org.voltdb.expressions.AbstractExpression in project voltdb by VoltDB.

the class TestPlansJoin method perJoinOpTestJoinOrders.

private void perJoinOpTestJoinOrders(JoinOp joinOp) {
    String query;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    SeqScanPlanNode seqScan;
    NestLoopIndexPlanNode nlij;
    IndexScanPlanNode indexScan;
    AbstractExpression predicate;
    if (joinOp != JoinOp.EQUAL) {
        // weaken test for now
        return;
    }
    // Index Join (R3.A) still has a lower cost compare to a Loop Join
    // despite the R3.C = 0 equality filter on the inner node
    query = "SELECT * FROM R1 JOIN R3 ON R3.A" + joinOp + "R1.A WHERE R3.C = 0";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    seqScan = (SeqScanPlanNode) node;
    assertEquals("R1", seqScan.getTargetTableName());
    // R3.A is an INDEX. Both children are IndexScans. With everything being equal,
    // the Left table (L) has fewer filters and should be an inner node
    query = "SELECT L.A, R.A FROM R3 L JOIN R3 R ON L.A" + joinOp + "R.A WHERE R.A > 3 AND R.C  = 3 AND L.A > 2 ;";
    pn = compileToTopDownTree(query, 2, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.INDEXSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.INDEXSCAN);
    indexScan = (IndexScanPlanNode) node;
    assertEquals("R", indexScan.getTargetTableAlias());
    // NLIJ with inline inner IndexScan over R2 using its partial index is a winner
    // over the NLJ with R2 on the outer side
    query = "SELECT * FROM R3 JOIN R2 ON R3.C" + joinOp + "R2.C WHERE R2.C > 100;";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    nlij = (NestLoopIndexPlanNode) node;
    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("PARTIAL_IND2", indexScan.getTargetIndexName());
    seqScan = (SeqScanPlanNode) nlij.getChild(0);
    assertEquals("R3", seqScan.getTargetTableName());
}

Example 67 with AbstractExpression

use of org.voltdb.expressions.AbstractExpression in project voltdb by VoltDB.

the class TestPlansInExistsSubQueries method testInHaving.

public void testInHaving() {
    String sql;
    AbstractPlanNode pn;
    AggregatePlanNode aggNode;
    sql = "select a from r1 group by a having max(c) in (select c from r2 )";
    failToCompile(sql, HavingErrorMsg);
    // ENG-8306: Uncomment next block when HAVING with subquery is supported
    //            AbstractPlanNode pn = compile(sql);
    //            pn = pn.getChild(0);
    //            assertTrue(pn instanceof ProjectionPlanNode);
    //            pn = pn.getChild(0);
    //            assertTrue(pn instanceof SeqScanPlanNode);
    //            AggregatePlanNode aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
    //            assertNotNull(aggNode);
    //            NodeSchema ns = aggNode.getOutputSchema();
    //            assertEquals(2, ns.size());
    //            SchemaColumn aggColumn = ns.getColumns().get(1);
    //            assertEquals("$$_MAX_$$_1", aggColumn.getColumnAlias());
    //            AbstractExpression having = aggNode.getPostPredicate();
    //            assertEquals(ExpressionType.OPERATOR_EXISTS, having.getExpressionType());
    //            AbstractExpression se = having.getLeft();
    //            assertEquals(1, se.getArgs().size());
    //            assertTrue(se.getArgs().get(0) instanceof TupleValueExpression);
    //            TupleValueExpression argTve = (TupleValueExpression) se.getArgs().get(0);
    //            assertEquals(1, argTve.getColumnIndex());
    //            assertEquals("$$_MAX_$$_1", argTve.getColumnAlias());
    // HAVING expression evaluates to TRUE and dropped
    sql = "select a from r1 " + " group by a " + " having exists (select max(a) from r2) or max(c) > 0";
    pn = compile(sql);
    pn = pn.getChild(0);
    assertTrue(pn instanceof SeqScanPlanNode);
    aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
    assertNotNull(aggNode);
    assertTrue(aggNode.getPostPredicate() == null);
    // HAVING expression evaluates to FALSE and retained
    sql = "select a from r1 " + " group by a " + " having exists (select max(a) from r2 limit 0) and max(c) > 0";
    pn = compile(sql);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof SeqScanPlanNode);
    aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
    assertNotNull(aggNode);
    AbstractExpression having = aggNode.getPostPredicate();
    assertTrue(ConstantValueExpression.isBooleanFalse(having));
}

Example 68 with AbstractExpression

use of org.voltdb.expressions.AbstractExpression in project voltdb by VoltDB.

the class TestPlansInExistsSubQueries method testInAggregated.

public void testInAggregated() {
    AbstractPlanNode pn = compile("select a, sum(c) as sc1 from r1 where (a, c) in " + "( SELECT a, count(c) as sc2 " + "from  r1  GROUP BY a ORDER BY a DESC) GROUP BY A;");
    pn = pn.getChild(0);
    assertTrue(pn instanceof AbstractScanPlanNode);
    AbstractExpression e = ((AbstractScanPlanNode) pn).getPredicate();
    assertEquals(ExpressionType.OPERATOR_EXISTS, e.getExpressionType());
    AbstractSubqueryExpression subExpr = (AbstractSubqueryExpression) e.getLeft();
    AbstractPlanNode sn = subExpr.getSubqueryNode();
    // Added LIMIT 1
    assertTrue(sn instanceof LimitPlanNode);
    assertEquals(1, ((LimitPlanNode) sn).getLimit());
    sn = sn.getChild(0);
    assertTrue(sn instanceof SeqScanPlanNode);
    AggregatePlanNode aggNode = AggregatePlanNode.getInlineAggregationNode(sn);
    assertNotNull(aggNode.getPostPredicate());
}

Example 69 with AbstractExpression

use of org.voltdb.expressions.AbstractExpression 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 70 with AbstractExpression

use of org.voltdb.expressions.AbstractExpression in project voltdb by VoltDB.

the class TestPlansInExistsSubQueries method testInToExist.

public void testInToExist() {
    AbstractPlanNode pn = compile("select r2.c from r2 where r2.a in (select c from r1)");
    pn = pn.getChild(0);
    assertTrue(pn instanceof AbstractScanPlanNode);
    AbstractScanPlanNode spl = (AbstractScanPlanNode) pn;
    // Check param indexes
    AbstractExpression e = spl.getPredicate();
    assertEquals(ExpressionType.OPERATOR_EXISTS, e.getExpressionType());
    AbstractSubqueryExpression subExpr = (AbstractSubqueryExpression) e.getLeft();
    assertEquals(1, subExpr.getArgs().size());
    assertEquals(1, subExpr.getParameterIdxList().size());
    assertEquals(Integer.valueOf(0), subExpr.getParameterIdxList().get(0));
    AbstractExpression tve = subExpr.getArgs().get(0);
    assertTrue(tve instanceof TupleValueExpression);
    assertEquals("R2", ((TupleValueExpression) tve).getTableName());
    assertEquals("A", ((TupleValueExpression) tve).getColumnName());
}