use of org.apache.phoenix.compile.OrderByCompiler.OrderBy in project phoenix by apache.
the class ParallelIteratorsSplitTest method getSplits.
private static List<KeyRange> getSplits(final TableRef tableRef, final Scan scan, final List<HRegionLocation> regions, final ScanRanges scanRanges) throws SQLException {
final List<TableRef> tableRefs = Collections.singletonList(tableRef);
ColumnResolver resolver = new ColumnResolver() {
@Override
public List<PFunction> getFunctions() {
return Collections.emptyList();
}
@Override
public List<TableRef> getTables() {
return tableRefs;
}
@Override
public TableRef resolveTable(String schemaName, String tableName) throws SQLException {
throw new UnsupportedOperationException();
}
@Override
public ColumnRef resolveColumn(String schemaName, String tableName, String colName) throws SQLException {
throw new UnsupportedOperationException();
}
@Override
public PFunction resolveFunction(String functionName) throws SQLException {
throw new UnsupportedOperationException();
}
@Override
public boolean hasUDFs() {
return false;
}
@Override
public PSchema resolveSchema(String schemaName) throws SQLException {
return null;
}
@Override
public List<PSchema> getSchemas() {
return null;
}
};
PhoenixConnection connection = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
final PhoenixStatement statement = new PhoenixStatement(connection);
final StatementContext context = new StatementContext(statement, resolver, scan, new SequenceManager(statement));
context.setScanRanges(scanRanges);
ParallelIterators parallelIterators = new ParallelIterators(new QueryPlan() {
private final Set<TableRef> tableRefs = ImmutableSet.of(tableRef);
@Override
public StatementContext getContext() {
return context;
}
@Override
public ParameterMetaData getParameterMetaData() {
return PhoenixParameterMetaData.EMPTY_PARAMETER_META_DATA;
}
@Override
public ExplainPlan getExplainPlan() throws SQLException {
return ExplainPlan.EMPTY_PLAN;
}
@Override
public ResultIterator iterator(ParallelScanGrouper scanGrouper) throws SQLException {
return ResultIterator.EMPTY_ITERATOR;
}
@Override
public ResultIterator iterator(ParallelScanGrouper scanGrouper, Scan scan) throws SQLException {
return ResultIterator.EMPTY_ITERATOR;
}
@Override
public ResultIterator iterator() throws SQLException {
return ResultIterator.EMPTY_ITERATOR;
}
@Override
public long getEstimatedSize() {
return 0;
}
@Override
public Set<TableRef> getSourceRefs() {
return tableRefs;
}
@Override
public TableRef getTableRef() {
return tableRef;
}
@Override
public RowProjector getProjector() {
return RowProjector.EMPTY_PROJECTOR;
}
@Override
public Integer getLimit() {
return null;
}
@Override
public Integer getOffset() {
return null;
}
@Override
public OrderBy getOrderBy() {
return OrderBy.EMPTY_ORDER_BY;
}
@Override
public GroupBy getGroupBy() {
return GroupBy.EMPTY_GROUP_BY;
}
@Override
public List<KeyRange> getSplits() {
return null;
}
@Override
public FilterableStatement getStatement() {
return SelectStatement.SELECT_ONE;
}
@Override
public boolean isDegenerate() {
return false;
}
@Override
public boolean isRowKeyOrdered() {
return true;
}
@Override
public List<List<Scan>> getScans() {
return null;
}
@Override
public Operation getOperation() {
return Operation.QUERY;
}
@Override
public boolean useRoundRobinIterator() {
return false;
}
@Override
public <T> T accept(QueryPlanVisitor<T> visitor) {
return visitor.defaultReturn(this);
}
@Override
public Long getEstimatedRowsToScan() {
return null;
}
@Override
public Long getEstimatedBytesToScan() {
return null;
}
@Override
public Long getEstimateInfoTimestamp() throws SQLException {
return null;
}
@Override
public Cost getCost() {
return Cost.ZERO;
}
}, null, new SpoolingResultIterator.SpoolingResultIteratorFactory(context.getConnection().getQueryServices()), context.getScan(), false, null, null);
List<KeyRange> keyRanges = parallelIterators.getSplits();
return keyRanges;
}
use of org.apache.phoenix.compile.OrderByCompiler.OrderBy 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;
QueryPlan dataPlan = dataPlans.get(tableRef);
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, dataPlan) : new ScanPlan(context, select, tableRef, projector, limit, offset, orderBy, parallelIteratorFactory, allowPageFilter, dataPlan));
}
SelectStatement planSelect = asSubquery ? select : this.select;
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(planSelect, 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, planSelect, tableRef, projector, limit, offset, where, orderBy, groupBy, having, plan) : new ClientScanPlan(context, planSelect, tableRef, projector, limit, offset, where, orderBy, plan);
}
return plan;
}
use of org.apache.phoenix.compile.OrderByCompiler.OrderBy in project phoenix by apache.
the class QueryCompilerTest method doTestOrderByReverseOptimizationWithNUllsLastBug3491.
private void doTestOrderByReverseOptimizationWithNUllsLastBug3491(boolean salted, boolean desc1, boolean desc2, boolean desc3, boolean[] groupBys, OrderBy[] orderBys) throws Exception {
Connection conn = null;
try {
conn = DriverManager.getConnection(getUrl());
String tableName = "ORDERBY3491_TEST";
conn.createStatement().execute("DROP TABLE if exists " + tableName);
String sql = "CREATE TABLE " + tableName + " ( " + "ORGANIZATION_ID VARCHAR," + "CONTAINER_ID VARCHAR," + "SCORE VARCHAR," + "ENTITY_ID VARCHAR NOT NULL," + "CONSTRAINT TEST_PK PRIMARY KEY ( " + "ORGANIZATION_ID" + (desc1 ? " DESC" : "") + "," + "CONTAINER_ID" + (desc2 ? " DESC" : "") + "," + "SCORE" + (desc3 ? " DESC" : "") + "," + "ENTITY_ID" + ")) " + (salted ? "SALT_BUCKETS =4" : "");
conn.createStatement().execute(sql);
String[] sqls = { // groupBy orderPreserving orderBy asc asc
"SELECT ORGANIZATION_ID,CONTAINER_ID FROM " + tableName + " group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID ASC NULLS FIRST", "SELECT ORGANIZATION_ID,CONTAINER_ID FROM " + tableName + " group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID ASC NULLS LAST", "SELECT ORGANIZATION_ID,CONTAINER_ID FROM " + tableName + " group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID ASC NULLS FIRST", "SELECT ORGANIZATION_ID,CONTAINER_ID FROM " + tableName + " group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID ASC NULLS LAST", // groupBy orderPreserving orderBy asc desc
"SELECT ORGANIZATION_ID,CONTAINER_ID FROM " + tableName + " group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID DESC NULLS FIRST", "SELECT ORGANIZATION_ID,CONTAINER_ID FROM " + tableName + " group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID DESC NULLS LAST", "SELECT ORGANIZATION_ID,CONTAINER_ID FROM " + tableName + " group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID DESC NULLS FIRST", "SELECT ORGANIZATION_ID,CONTAINER_ID FROM " + tableName + " group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID DESC NULLS LAST", // groupBy orderPreserving orderBy desc asc
"SELECT ORGANIZATION_ID,CONTAINER_ID FROM " + tableName + " group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID ASC NULLS FIRST", "SELECT ORGANIZATION_ID,CONTAINER_ID FROM " + tableName + " group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID ASC NULLS LAST", "SELECT ORGANIZATION_ID,CONTAINER_ID FROM " + tableName + " group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID ASC NULLS FIRST", "SELECT ORGANIZATION_ID,CONTAINER_ID FROM " + tableName + " group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID ASC NULLS LAST", // groupBy orderPreserving orderBy desc desc
"SELECT ORGANIZATION_ID,CONTAINER_ID FROM " + tableName + " group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID DESC NULLS FIRST", "SELECT ORGANIZATION_ID,CONTAINER_ID FROM " + tableName + " group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID DESC NULLS LAST", "SELECT ORGANIZATION_ID,CONTAINER_ID FROM " + tableName + " group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID DESC NULLS FIRST", "SELECT ORGANIZATION_ID,CONTAINER_ID FROM " + tableName + " group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID DESC NULLS LAST", // groupBy not orderPreserving orderBy asc asc
"SELECT ORGANIZATION_ID,SCORE FROM " + tableName + " group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE ASC NULLS FIRST", "SELECT ORGANIZATION_ID,SCORE FROM " + tableName + " group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE ASC NULLS LAST", "SELECT ORGANIZATION_ID,SCORE FROM " + tableName + " group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE ASC NULLS FIRST", "SELECT ORGANIZATION_ID,SCORE FROM " + tableName + " group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE ASC NULLS LAST", // groupBy not orderPreserving orderBy asc desc
"SELECT ORGANIZATION_ID,SCORE FROM " + tableName + " group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE DESC NULLS FIRST", "SELECT ORGANIZATION_ID,SCORE FROM " + tableName + " group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE DESC NULLS LAST", "SELECT ORGANIZATION_ID,SCORE FROM " + tableName + " group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE DESC NULLS FIRST", "SELECT ORGANIZATION_ID,SCORE FROM " + tableName + " group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE DESC NULLS LAST", // groupBy not orderPreserving orderBy desc asc
"SELECT ORGANIZATION_ID,SCORE FROM " + tableName + " group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE ASC NULLS FIRST", "SELECT ORGANIZATION_ID,SCORE FROM " + tableName + " group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE ASC NULLS LAST", "SELECT ORGANIZATION_ID,SCORE FROM " + tableName + " group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE ASC NULLS FIRST", "SELECT ORGANIZATION_ID,SCORE FROM " + tableName + " group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE ASC NULLS LAST", // groupBy not orderPreserving orderBy desc desc
"SELECT ORGANIZATION_ID,SCORE FROM " + tableName + " group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE DESC NULLS FIRST", "SELECT ORGANIZATION_ID,SCORE FROM " + tableName + " group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE DESC NULLS LAST", "SELECT ORGANIZATION_ID,SCORE FROM " + tableName + " group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE DESC NULLS FIRST", "SELECT ORGANIZATION_ID,SCORE FROM " + tableName + " group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE DESC NULLS LAST", // -------only one return column----------------------------------
"SELECT SCORE FROM " + tableName + " group by SCORE ORDER BY SCORE ASC NULLS FIRST", "SELECT SCORE FROM " + tableName + " group by SCORE ORDER BY SCORE ASC NULLS LAST", "SELECT SCORE FROM " + tableName + " group by SCORE ORDER BY SCORE DESC NULLS FIRST", "SELECT SCORE FROM " + tableName + " group by SCORE ORDER BY SCORE DESC NULLS LAST" };
for (int i = 0; i < sqls.length; i++) {
sql = sqls[i];
QueryPlan queryPlan = getQueryPlan(conn, sql);
assertTrue((i + 1) + ") " + sql, queryPlan.getGroupBy().isOrderPreserving() == groupBys[i]);
OrderBy orderBy = queryPlan.getOrderBy();
if (orderBys[i] != null) {
assertTrue((i + 1) + ") " + sql, orderBy == orderBys[i]);
} else {
assertTrue((i + 1) + ") " + sql, orderBy.getOrderByExpressions().size() > 0);
}
}
} finally {
if (conn != null) {
conn.close();
}
}
}
use of org.apache.phoenix.compile.OrderByCompiler.OrderBy 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();
}
}
}
use of org.apache.phoenix.compile.OrderByCompiler.OrderBy in project phoenix by apache.
the class QueryCompilerTest method doTestOrderByReverseOptimizationBug3491.
private void doTestOrderByReverseOptimizationBug3491(boolean salted, boolean desc1, boolean desc2, boolean desc3, boolean[] groupBys, OrderBy[] orderBys) throws Exception {
Connection conn = null;
try {
conn = DriverManager.getConnection(getUrl());
String tableName = "ORDERBY3491_TEST";
conn.createStatement().execute("DROP TABLE if exists " + tableName);
String sql = "CREATE TABLE " + tableName + " ( " + "ORGANIZATION_ID INTEGER NOT NULL," + "CONTAINER_ID INTEGER NOT NULL," + "SCORE INTEGER NOT NULL," + "ENTITY_ID INTEGER NOT NULL," + "CONSTRAINT TEST_PK PRIMARY KEY ( " + "ORGANIZATION_ID" + (desc1 ? " DESC" : "") + "," + "CONTAINER_ID" + (desc2 ? " DESC" : "") + "," + "SCORE" + (desc3 ? " DESC" : "") + "," + "ENTITY_ID" + ")) " + (salted ? "SALT_BUCKETS =4" : "");
conn.createStatement().execute(sql);
String[] sqls = { // groupBy orderPreserving orderBy asc asc
"SELECT ORGANIZATION_ID,CONTAINER_ID FROM " + tableName + " group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC, CONTAINER_ID ASC", // groupBy orderPreserving orderBy asc desc
"SELECT ORGANIZATION_ID,CONTAINER_ID FROM " + tableName + " group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC, CONTAINER_ID DESC", // groupBy orderPreserving orderBy desc asc
"SELECT ORGANIZATION_ID,CONTAINER_ID FROM " + tableName + " group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC, CONTAINER_ID ASC", // groupBy orderPreserving orderBy desc desc
"SELECT ORGANIZATION_ID,CONTAINER_ID FROM " + tableName + " group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC, CONTAINER_ID DESC", // groupBy not orderPreserving orderBy asc asc
"SELECT ORGANIZATION_ID,SCORE FROM " + tableName + " group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC, SCORE ASC", // groupBy not orderPreserving orderBy asc desc
"SELECT ORGANIZATION_ID,SCORE FROM " + tableName + " group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC, SCORE DESC", // groupBy not orderPreserving orderBy desc asc
"SELECT ORGANIZATION_ID,SCORE FROM " + tableName + " group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC, SCORE ASC", // groupBy not orderPreserving orderBy desc desc
"SELECT ORGANIZATION_ID,SCORE FROM " + tableName + " group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC, SCORE DESC" };
for (int i = 0; i < sqls.length; i++) {
sql = sqls[i];
QueryPlan queryPlan = getQueryPlan(conn, sql);
assertTrue((i + 1) + ") " + sql, queryPlan.getGroupBy().isOrderPreserving() == groupBys[i]);
OrderBy orderBy = queryPlan.getOrderBy();
if (orderBys[i] != null) {
assertTrue((i + 1) + ") " + sql, orderBy == orderBys[i]);
} else {
assertTrue((i + 1) + ") " + sql, orderBy.getOrderByExpressions().size() > 0);
}
}
} finally {
if (conn != null) {
conn.close();
}
}
}
Aggregations