Examples with ScanPlan org.apache.phoenix.execute.ScanPlan used on opensource projects

Example 1 with ScanPlan

use of org.apache.phoenix.execute.ScanPlan in project phoenix by apache.

the class QueryCompiler method compileSingleFlatQuery.

protected QueryPlan compileSingleFlatQuery(StatementContext context, SelectStatement select, List<Object> binds, boolean asSubquery, boolean allowPageFilter, QueryPlan innerPlan, TupleProjector innerPlanTupleProjector, boolean isInRowKeyOrder) throws SQLException {
    PTable projectedTable = null;
    if (this.projectTuples) {
        projectedTable = TupleProjectionCompiler.createProjectedTable(select, context);
        if (projectedTable != null) {
            context.setResolver(FromCompiler.getResolverForProjectedTable(projectedTable, context.getConnection(), select.getUdfParseNodes()));
        }
    }
    ColumnResolver resolver = context.getResolver();
    TableRef tableRef = context.getCurrentTable();
    PTable table = tableRef.getTable();
    ParseNode viewWhere = null;
    if (table.getViewStatement() != null) {
        viewWhere = new SQLParser(table.getViewStatement()).parseQuery().getWhere();
    }
    Integer limit = LimitCompiler.compile(context, select);
    Integer offset = OffsetCompiler.compile(context, select);
    GroupBy groupBy = GroupByCompiler.compile(context, select, isInRowKeyOrder);
    // Optimize the HAVING clause by finding any group by expressions that can be moved
    // to the WHERE clause
    select = HavingCompiler.rewrite(context, select, groupBy);
    Expression having = HavingCompiler.compile(context, select, groupBy);
    // expressions as group by key expressions since they're pre, not post filtered.
    if (innerPlan == null && !tableRef.equals(resolver.getTables().get(0))) {
        context.setResolver(FromCompiler.getResolver(context.getConnection(), tableRef, select.getUdfParseNodes()));
    }
    Set<SubqueryParseNode> subqueries = Sets.<SubqueryParseNode>newHashSet();
    Expression where = WhereCompiler.compile(context, select, viewWhere, subqueries);
    // Recompile GROUP BY now that we've figured out our ScanRanges so we know
    // definitively whether or not we'll traverse in row key order.
    groupBy = groupBy.compile(context, innerPlanTupleProjector);
    // recover resolver
    context.setResolver(resolver);
    RowProjector projector = ProjectionCompiler.compile(context, select, groupBy, asSubquery ? Collections.<PDatum>emptyList() : targetColumns, where);
    OrderBy orderBy = OrderByCompiler.compile(context, select, groupBy, limit, offset, projector, groupBy == GroupBy.EMPTY_GROUP_BY ? innerPlanTupleProjector : null, isInRowKeyOrder);
    context.getAggregationManager().compile(context, groupBy);
    // Final step is to build the query plan
    if (!asSubquery) {
        int maxRows = statement.getMaxRows();
        if (maxRows > 0) {
            if (limit != null) {
                limit = Math.min(limit, maxRows);
            } else {
                limit = maxRows;
            }
        }
    }
    if (projectedTable != null) {
        TupleProjector.serializeProjectorIntoScan(context.getScan(), new TupleProjector(projectedTable));
    }
    QueryPlan plan = innerPlan;
    if (plan == null) {
        ParallelIteratorFactory parallelIteratorFactory = asSubquery ? null : this.parallelIteratorFactory;
        plan = select.getFrom() == null ? new LiteralResultIterationPlan(context, select, tableRef, projector, limit, offset, orderBy, parallelIteratorFactory) : (select.isAggregate() || select.isDistinct() ? new AggregatePlan(context, select, tableRef, projector, limit, offset, orderBy, parallelIteratorFactory, groupBy, having) : new ScanPlan(context, select, tableRef, projector, limit, offset, orderBy, parallelIteratorFactory, allowPageFilter));
    }
    if (!subqueries.isEmpty()) {
        int count = subqueries.size();
        WhereClauseSubPlan[] subPlans = new WhereClauseSubPlan[count];
        int i = 0;
        for (SubqueryParseNode subqueryNode : subqueries) {
            SelectStatement stmt = subqueryNode.getSelectNode();
            subPlans[i++] = new WhereClauseSubPlan(compileSubquery(stmt, false), stmt, subqueryNode.expectSingleRow());
        }
        plan = HashJoinPlan.create(select, plan, null, subPlans);
    }
    if (innerPlan != null) {
        if (LiteralExpression.isTrue(where)) {
            // we do not pass "true" as filter
            where = null;
        }
        plan = select.isAggregate() || select.isDistinct() ? new ClientAggregatePlan(context, select, tableRef, projector, limit, offset, where, orderBy, groupBy, having, plan) : new ClientScanPlan(context, select, tableRef, projector, limit, offset, where, orderBy, plan);
    }
    return plan;
}

Example 2 with ScanPlan

use of org.apache.phoenix.execute.ScanPlan in project phoenix by apache.

the class QueryCompilerTest method testSortMergeJoinSubQueryOrderByOverrideBug3745.

@Test
public void testSortMergeJoinSubQueryOrderByOverrideBug3745() throws Exception {
    Connection conn = null;
    try {
        conn = DriverManager.getConnection(getUrl());
        String tableName1 = "MERGE1";
        String tableName2 = "MERGE2";
        conn.createStatement().execute("DROP TABLE if exists " + tableName1);
        String sql = "CREATE TABLE IF NOT EXISTS " + tableName1 + " ( " + "AID INTEGER PRIMARY KEY," + "AGE INTEGER" + ")";
        conn.createStatement().execute(sql);
        conn.createStatement().execute("DROP TABLE if exists " + tableName2);
        sql = "CREATE TABLE IF NOT EXISTS " + tableName2 + " ( " + "BID INTEGER PRIMARY KEY," + "CODE INTEGER" + ")";
        conn.createStatement().execute(sql);
        //test for simple scan
        sql = "select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from (select aid,age from " + tableName1 + " where age >=11 and age<=33 order by age limit 3) a inner join " + "(select bid,code from " + tableName2 + " order by code limit 1) b on a.aid=b.bid ";
        QueryPlan queryPlan = getQueryPlan(conn, sql);
        SortMergeJoinPlan sortMergeJoinPlan = (SortMergeJoinPlan) ((ClientScanPlan) queryPlan).getDelegate();
        ClientScanPlan lhsOuterPlan = (ClientScanPlan) ((TupleProjectionPlan) (sortMergeJoinPlan.getLhsPlan())).getDelegate();
        OrderBy orderBy = lhsOuterPlan.getOrderBy();
        assertTrue(orderBy.getOrderByExpressions().size() == 1);
        assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("AID"));
        ScanPlan innerScanPlan = (ScanPlan) ((TupleProjectionPlan) lhsOuterPlan.getDelegate()).getDelegate();
        orderBy = innerScanPlan.getOrderBy();
        assertTrue(orderBy.getOrderByExpressions().size() == 1);
        assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("AGE"));
        assertTrue(innerScanPlan.getLimit().intValue() == 3);
        ClientScanPlan rhsOuterPlan = (ClientScanPlan) ((TupleProjectionPlan) (sortMergeJoinPlan.getRhsPlan())).getDelegate();
        orderBy = rhsOuterPlan.getOrderBy();
        assertTrue(orderBy.getOrderByExpressions().size() == 1);
        assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("BID"));
        innerScanPlan = (ScanPlan) ((TupleProjectionPlan) rhsOuterPlan.getDelegate()).getDelegate();
        orderBy = innerScanPlan.getOrderBy();
        assertTrue(orderBy.getOrderByExpressions().size() == 1);
        assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("CODE"));
        assertTrue(innerScanPlan.getLimit().intValue() == 1);
        //test for aggregate
        sql = "select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.codesum from (select aid,sum(age) agesum from " + tableName1 + " where age >=11 and age<=33 group by aid order by agesum limit 3) a inner join " + "(select bid,sum(code) codesum from " + tableName2 + " group by bid order by codesum limit 1) b on a.aid=b.bid ";
        queryPlan = getQueryPlan(conn, sql);
        sortMergeJoinPlan = (SortMergeJoinPlan) ((ClientScanPlan) queryPlan).getDelegate();
        lhsOuterPlan = (ClientScanPlan) ((TupleProjectionPlan) (sortMergeJoinPlan.getLhsPlan())).getDelegate();
        orderBy = lhsOuterPlan.getOrderBy();
        assertTrue(orderBy.getOrderByExpressions().size() == 1);
        assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("AID"));
        AggregatePlan innerAggregatePlan = (AggregatePlan) ((TupleProjectionPlan) lhsOuterPlan.getDelegate()).getDelegate();
        orderBy = innerAggregatePlan.getOrderBy();
        assertTrue(orderBy.getOrderByExpressions().size() == 1);
        assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("SUM(AGE)"));
        assertTrue(innerAggregatePlan.getLimit().intValue() == 3);
        rhsOuterPlan = (ClientScanPlan) ((TupleProjectionPlan) (sortMergeJoinPlan.getRhsPlan())).getDelegate();
        orderBy = rhsOuterPlan.getOrderBy();
        assertTrue(orderBy.getOrderByExpressions().size() == 1);
        assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("BID"));
        innerAggregatePlan = (AggregatePlan) ((TupleProjectionPlan) rhsOuterPlan.getDelegate()).getDelegate();
        orderBy = innerAggregatePlan.getOrderBy();
        assertTrue(orderBy.getOrderByExpressions().size() == 1);
        assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("SUM(CODE)"));
        assertTrue(innerAggregatePlan.getLimit().intValue() == 1);
        String tableName3 = "merge3";
        conn.createStatement().execute("DROP TABLE if exists " + tableName3);
        sql = "CREATE TABLE IF NOT EXISTS " + tableName3 + " ( " + "CID INTEGER PRIMARY KEY," + "REGION INTEGER" + ")";
        conn.createStatement().execute(sql);
        //test for join
        sql = "select t1.aid,t1.code,t2.region from " + "(select a.aid,b.code from " + tableName1 + " a inner join " + tableName2 + " b on a.aid=b.bid where b.code >=44 and b.code<=66 order by b.code limit 3) t1 inner join " + "(select a.aid,c.region from " + tableName1 + " a inner join " + tableName3 + " c on a.aid=c.cid where c.region>=77 and c.region<=99 order by c.region desc limit 1) t2 on t1.aid=t2.aid";
        PhoenixPreparedStatement phoenixPreparedStatement = conn.prepareStatement(sql).unwrap(PhoenixPreparedStatement.class);
        queryPlan = phoenixPreparedStatement.optimizeQuery(sql);
        sortMergeJoinPlan = (SortMergeJoinPlan) ((ClientScanPlan) queryPlan).getDelegate();
        lhsOuterPlan = (ClientScanPlan) ((TupleProjectionPlan) (sortMergeJoinPlan.getLhsPlan())).getDelegate();
        orderBy = lhsOuterPlan.getOrderBy();
        assertTrue(orderBy.getOrderByExpressions().size() == 1);
        assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("AID"));
        innerScanPlan = (ScanPlan) ((HashJoinPlan) ((TupleProjectionPlan) lhsOuterPlan.getDelegate()).getDelegate()).getDelegate();
        orderBy = innerScanPlan.getOrderBy();
        assertTrue(orderBy.getOrderByExpressions().size() == 1);
        assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("B.CODE"));
        assertTrue(innerScanPlan.getLimit().intValue() == 3);
        rhsOuterPlan = (ClientScanPlan) ((TupleProjectionPlan) (sortMergeJoinPlan.getRhsPlan())).getDelegate();
        orderBy = rhsOuterPlan.getOrderBy();
        assertTrue(orderBy.getOrderByExpressions().size() == 1);
        assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("AID"));
        innerScanPlan = (ScanPlan) ((HashJoinPlan) ((TupleProjectionPlan) rhsOuterPlan.getDelegate()).getDelegate()).getDelegate();
        orderBy = innerScanPlan.getOrderBy();
        assertTrue(orderBy.getOrderByExpressions().size() == 1);
        assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("C.REGION DESC"));
        assertTrue(innerScanPlan.getLimit().intValue() == 1);
        //test for join and aggregate
        sql = "select t1.aid,t1.codesum,t2.regionsum from " + "(select a.aid,sum(b.code) codesum from " + tableName1 + " a inner join " + tableName2 + " b on a.aid=b.bid where b.code >=44 and b.code<=66 group by a.aid order by codesum limit 3) t1 inner join " + "(select a.aid,sum(c.region) regionsum from " + tableName1 + " a inner join " + tableName3 + " c on a.aid=c.cid where c.region>=77 and c.region<=99 group by a.aid order by regionsum desc limit 2) t2 on t1.aid=t2.aid";
        phoenixPreparedStatement = conn.prepareStatement(sql).unwrap(PhoenixPreparedStatement.class);
        queryPlan = phoenixPreparedStatement.optimizeQuery(sql);
        sortMergeJoinPlan = (SortMergeJoinPlan) ((ClientScanPlan) queryPlan).getDelegate();
        lhsOuterPlan = (ClientScanPlan) ((TupleProjectionPlan) (sortMergeJoinPlan.getLhsPlan())).getDelegate();
        orderBy = lhsOuterPlan.getOrderBy();
        assertTrue(orderBy.getOrderByExpressions().size() == 1);
        assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("AID"));
        innerAggregatePlan = (AggregatePlan) ((HashJoinPlan) ((TupleProjectionPlan) lhsOuterPlan.getDelegate()).getDelegate()).getDelegate();
        orderBy = innerAggregatePlan.getOrderBy();
        assertTrue(orderBy.getOrderByExpressions().size() == 1);
        assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("SUM(B.CODE)"));
        assertTrue(innerAggregatePlan.getLimit().intValue() == 3);
        rhsOuterPlan = (ClientScanPlan) ((TupleProjectionPlan) (sortMergeJoinPlan.getRhsPlan())).getDelegate();
        orderBy = rhsOuterPlan.getOrderBy();
        assertTrue(orderBy.getOrderByExpressions().size() == 1);
        assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("AID"));
        innerAggregatePlan = (AggregatePlan) ((HashJoinPlan) ((TupleProjectionPlan) rhsOuterPlan.getDelegate()).getDelegate()).getDelegate();
        orderBy = innerAggregatePlan.getOrderBy();
        assertTrue(orderBy.getOrderByExpressions().size() == 1);
        assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("SUM(C.REGION) DESC"));
        assertTrue(innerAggregatePlan.getLimit().intValue() == 2);
        //test for if SubselectRewriter.isOrderByPrefix had take effect
        sql = "select t1.aid,t1.codesum,t2.regionsum from " + "(select a.aid,sum(b.code) codesum from " + tableName1 + " a inner join " + tableName2 + " b on a.aid=b.bid where b.code >=44 and b.code<=66 group by a.aid order by a.aid,codesum limit 3) t1 inner join " + "(select a.aid,sum(c.region) regionsum from " + tableName1 + " a inner join " + tableName3 + " c on a.aid=c.cid where c.region>=77 and c.region<=99 group by a.aid order by a.aid desc,regionsum desc limit 2) t2 on t1.aid=t2.aid " + "order by t1.aid desc";
        phoenixPreparedStatement = conn.prepareStatement(sql).unwrap(PhoenixPreparedStatement.class);
        queryPlan = phoenixPreparedStatement.optimizeQuery(sql);
        orderBy = queryPlan.getOrderBy();
        assertTrue(orderBy.getOrderByExpressions().size() == 1);
        assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("T1.AID DESC"));
        sortMergeJoinPlan = (SortMergeJoinPlan) ((ClientScanPlan) queryPlan).getDelegate();
        innerAggregatePlan = (AggregatePlan) ((HashJoinPlan) (((TupleProjectionPlan) sortMergeJoinPlan.getLhsPlan()).getDelegate())).getDelegate();
        orderBy = innerAggregatePlan.getOrderBy();
        assertTrue(orderBy.getOrderByExpressions().size() == 2);
        assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("A.AID"));
        assertTrue(orderBy.getOrderByExpressions().get(1).toString().equals("SUM(B.CODE)"));
        assertTrue(innerAggregatePlan.getLimit().intValue() == 3);
        rhsOuterPlan = (ClientScanPlan) ((TupleProjectionPlan) (sortMergeJoinPlan.getRhsPlan())).getDelegate();
        orderBy = rhsOuterPlan.getOrderBy();
        assertTrue(orderBy.getOrderByExpressions().size() == 1);
        assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("AID"));
        innerAggregatePlan = (AggregatePlan) ((HashJoinPlan) ((TupleProjectionPlan) rhsOuterPlan.getDelegate()).getDelegate()).getDelegate();
        orderBy = innerAggregatePlan.getOrderBy();
        assertTrue(orderBy.getOrderByExpressions().size() == 2);
        assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("A.AID DESC"));
        assertTrue(orderBy.getOrderByExpressions().get(1).toString().equals("SUM(C.REGION) DESC"));
        assertTrue(innerAggregatePlan.getLimit().intValue() == 2);
    } finally {
        if (conn != null) {
            conn.close();
        }
    }
}