@Override public GroupBy getGroupBy() { return delegate.getGroupBy(); }
/** * The method validates the statement passed to the query plan. List of conditions are * <ol> * <li>Is a SELECT statement</li> * <li>doesn't contain ORDER BY expression</li> * <li>doesn't contain LIMIT</li> * <li>doesn't contain GROUP BY expression</li> * <li>doesn't contain DISTINCT</li> * <li>doesn't contain AGGREGATE functions</li> * </ol> * @param queryPlan * @return */ private boolean isValidStatement(final QueryPlan queryPlan) { if(queryPlan.getStatement().getOperation() != PhoenixStatement.Operation.QUERY) { throw new IllegalArgumentException("Query passed isn't a SELECT statement"); } if(!queryPlan.getOrderBy().getOrderByExpressions().isEmpty() || queryPlan.getLimit() != null || (queryPlan.getGroupBy() != null && !queryPlan.getGroupBy().isEmpty()) || queryPlan.getStatement().isDistinct() || queryPlan.getStatement().isAggregate()) { throw new IllegalArgumentException("SELECT statement shouldn't contain DISTINCT or ORDER BY or LIMIT or GROUP BY expressions"); } return true; }
@Test public void testDistinctPrefixOnIntIndex() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 INTEGER, v2 VARCHAR)"); conn.createStatement().execute("CREATE INDEX idx ON t(v1)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT COUNT(DISTINCT v1) FROM t"); assertTrue(plan.getGroupBy().isOrderPreserving()); assertFalse(plan.getGroupBy().getKeyExpressions().isEmpty()); assertEquals("IDX", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testDistinctPrefixOnVarcharIndex() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"); conn.createStatement().execute("CREATE INDEX idx ON t(v1)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT COUNT(DISTINCT v1) FROM t"); assertTrue(plan.getGroupBy().isOrderPreserving()); assertFalse(plan.getGroupBy().getKeyExpressions().isEmpty()); assertEquals("IDX", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testNotGroupByOrderPreserving() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k1 date not null, k2 date not null, k3 date not null, v varchar, constraint pk primary key(k1,k2,k3))"); String[] queries = { "SELECT 1 FROM T GROUP BY k1,k3", "SELECT 1 FROM T GROUP BY k2", "SELECT 1 FROM T GROUP BY INVERT(k1),k3", "SELECT 1 FROM T GROUP BY CASE WHEN k1 = CURRENT_DATE() THEN 0 ELSE 1 END", "SELECT 1 FROM T GROUP BY TO_CHAR(k1)", }; String query; for (int i = 0; i < queries.length; i++) { query = queries[i]; QueryPlan plan = conn.createStatement().unwrap(PhoenixStatement.class).compileQuery(query); assertFalse("Expected group by not to be order preserving: " + query, plan.getGroupBy().isOrderPreserving()); } }
@Test public void testGroupByOrderPreserving() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k1 date not null, k2 date not null, k3 date not null, v varchar, constraint pk primary key(k1,k2,k3))"); String[] queries = { "SELECT 1 FROM T GROUP BY k3, (k1,k2)", "SELECT 1 FROM T GROUP BY k2,k1,k3", "SELECT 1 FROM T GROUP BY k1,k2", "SELECT 1 FROM T GROUP BY k1", "SELECT 1 FROM T GROUP BY CAST(k1 AS TIMESTAMP)", "SELECT 1 FROM T GROUP BY (k1,k2,k3)", "SELECT 1 FROM T GROUP BY TRUNC(k2, 'DAY'), CEIL(k1, 'HOUR')", }; String query; for (int i = 0; i < queries.length; i++) { query = queries[i]; QueryPlan plan = conn.createStatement().unwrap(PhoenixStatement.class).compileQuery(query); assertTrue("Expected group by to be order preserving: " + query, plan.getGroupBy().isOrderPreserving()); } }
public BaseResultIterators(QueryPlan plan, Integer perScanLimit, Integer offset, ParallelScanGrouper scanGrouper, Scan scan, Map<ImmutableBytesPtr,ServerCache> caches, QueryPlan dataPlan) throws SQLException { super(plan.getContext(), plan.getTableRef(), plan.getGroupBy(), plan.getOrderBy(), plan.getStatement().getHint(), QueryUtil.getOffsetLimit(plan.getLimit(), plan.getOffset()), offset); this.plan = plan;
@Test public void testKeyOrderedGroupByOptimization() throws Exception { // Select columns in PK String[] queries = new String[] { "SELECT count(1) FROM atable GROUP BY organization_id,entity_id", "SELECT count(1) FROM atable GROUP BY organization_id,substr(entity_id,1,3),entity_id", "SELECT count(1) FROM atable GROUP BY entity_id,organization_id", "SELECT count(1) FROM atable GROUP BY substr(entity_id,1,3),organization_id", "SELECT count(1) FROM ptsdb GROUP BY host,inst,round(\"DATE\",'HOUR')", "SELECT count(1) FROM atable GROUP BY organization_id", }; List<Object> binds = Collections.emptyList(); for (String query : queries) { QueryPlan plan = getQueryPlan(query, binds); assertEquals(query, BaseScannerRegionObserver.KEY_ORDERED_GROUP_BY_EXPRESSIONS, plan.getGroupBy().getScanAttribName()); } }
@Test public void testNotKeyOrderedGroupByOptimization() throws Exception { // Select columns in PK String[] queries = new String[] { "SELECT count(1) FROM atable GROUP BY entity_id", "SELECT count(1) FROM atable GROUP BY substr(organization_id,2,3)", "SELECT count(1) FROM atable GROUP BY substr(entity_id,1,3)", "SELECT count(1) FROM atable GROUP BY to_date(organization_id)", "SELECT count(1) FROM atable GROUP BY regexp_substr(organization_id, '.*foo.*'),entity_id", "SELECT count(1) FROM atable GROUP BY substr(organization_id,1),entity_id", }; List<Object> binds = Collections.emptyList(); for (String query : queries) { QueryPlan plan = getQueryPlan(query, binds); assertEquals(plan.getGroupBy().getScanAttribName(), BaseScannerRegionObserver.UNORDERED_GROUP_BY_EXPRESSIONS); } }
int c = boundCount2 - boundCount1; if (c != 0) return c; if (plan1.getGroupBy() != null && plan2.getGroupBy() != null) { if (plan1.getGroupBy().isOrderPreserving() != plan2.getGroupBy().isOrderPreserving()) { return plan1.getGroupBy().isOrderPreserving() ? -1 : 1;
query = queries[i]; QueryPlan plan = conn.createStatement().unwrap(PhoenixStatement.class).compileQuery(query); assertTrue("Expected group by to be order preserving: " + query, plan.getGroupBy().isOrderPreserving());
protected QueryPlan compileSingleQuery(StatementContext context, SelectStatement select, List<Object> binds, boolean asSubquery, boolean allowPageFilter) throws SQLException{ SelectStatement innerSelect = select.getInnerSelectStatement(); if (innerSelect == null) { return compileSingleFlatQuery(context, select, binds, asSubquery, allowPageFilter, null, null, true); } QueryPlan innerPlan = compileSubquery(innerSelect, false); TupleProjector tupleProjector = new TupleProjector(innerPlan.getProjector()); innerPlan = new TupleProjectionPlan(innerPlan, tupleProjector, null); // Replace the original resolver and table with those having compiled type info. TableRef tableRef = context.getResolver().getTables().get(0); ColumnResolver resolver = FromCompiler.getResolverForCompiledDerivedTable(statement.getConnection(), tableRef, innerPlan.getProjector()); context.setResolver(resolver); tableRef = resolver.getTables().get(0); context.setCurrentTable(tableRef); boolean isInRowKeyOrder = innerPlan.getGroupBy() == GroupBy.EMPTY_GROUP_BY && innerPlan.getOrderBy() == OrderBy.EMPTY_ORDER_BY; return compileSingleFlatQuery(context, select, binds, asSubquery, allowPageFilter, innerPlan, tupleProjector, isInRowKeyOrder); }
sql="select DISTINCT entity_id, score from ( select entity_id, score from "+tableName+" limit 1)"; QueryPlan queryPlan=getQueryPlan(conn, sql); assertTrue(queryPlan.getGroupBy().getExpressions().get(0).getSortOrder()==SortOrder.DESC); assertTrue(queryPlan.getGroupBy().getExpressions().get(1).getSortOrder()==SortOrder.DESC); assertTrue(queryPlan.getGroupBy().getKeyExpressions().get(0).getSortOrder()==SortOrder.DESC); assertTrue(queryPlan.getGroupBy().getKeyExpressions().get(1).getSortOrder()==SortOrder.DESC); assertTrue(queryPlan.getGroupBy().getExpressions().get(0).getSortOrder()==SortOrder.DESC); assertTrue(queryPlan.getGroupBy().getExpressions().get(1).getSortOrder()==SortOrder.DESC); assertTrue(queryPlan.getGroupBy().getKeyExpressions().get(0).getSortOrder()==SortOrder.DESC); assertTrue(queryPlan.getGroupBy().getKeyExpressions().get(1).getSortOrder()==SortOrder.DESC); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).getExpression().getSortOrder()==SortOrder.DESC); assertTrue(queryPlan.getGroupBy().getExpressions().get(0).getSortOrder()==SortOrder.DESC); assertTrue(queryPlan.getGroupBy().getExpressions().get(1).getSortOrder()==SortOrder.DESC); assertTrue(queryPlan.getGroupBy().getKeyExpressions().get(0).getSortOrder()==SortOrder.DESC); assertTrue(queryPlan.getGroupBy().getKeyExpressions().get(1).getSortOrder()==SortOrder.DESC); assertTrue(queryPlan.getOrderBy()==OrderBy.FWD_ROW_KEY_ORDER_BY); } finally {
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) {
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) {
QueryPlan plan = stmt.getQueryPlan(); assertEquals(indexTableName, plan.getContext().getCurrentTable().getTable().getName().getString()); assertEquals(BaseScannerRegionObserver.KEY_ORDERED_GROUP_BY_EXPRESSIONS, plan.getGroupBy().getScanAttribName()); assertTrue(rs.next()); assertEquals("a", rs.getString(1));
ScanUtil.addOffsetAttribute(scan, offset); int cols = plan.getGroupBy().getOrderPreservingColumnCount(); if (cols > 0 && keyOnlyFilter && !plan.getStatement().getHint().hasHint(HintNode.Hint.RANGE_SCAN) && cols < plan.getTableRef().getTable().getRowKeySchema().getFieldCount() && plan.getGroupBy().isOrderPreserving() && (context.getAggregationManager().isEmpty() || plan.getGroupBy().isUngroupedAggregate())) {
assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size() ==2); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("PK2")); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy() == (!desc ? OrderBy.FWD_ROW_KEY_ORDER_BY : OrderBy.REV_ROW_KEY_ORDER_BY)); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size() ==2); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("PK2 DESC")); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy() == (!desc ? OrderBy.REV_ROW_KEY_ORDER_BY : OrderBy.FWD_ROW_KEY_ORDER_BY)); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size() == 2); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("PK3")); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy() == (!desc ? OrderBy.FWD_ROW_KEY_ORDER_BY : OrderBy.REV_ROW_KEY_ORDER_BY)); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size() == 2); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("PK3 DESC")); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy() == (!desc ? OrderBy.REV_ROW_KEY_ORDER_BY : OrderBy.FWD_ROW_KEY_ORDER_BY)); assertTrue(queryPlan.getGroupBy().isOrderPreserving());
plan.getOrderBy().getOrderByExpressions().isEmpty() && !dataPlan.getOrderBy().getOrderByExpressions().isEmpty() || plan.getGroupBy().isOrderPreserving() && !dataPlan.getGroupBy().isOrderPreserving();
assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID")); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy()== OrderBy.REV_ROW_KEY_ORDER_BY); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy()== OrderBy.FWD_ROW_KEY_ORDER_BY); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID DESC NULLS LAST")); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID")); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID NULLS LAST")); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID DESC")); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID DESC NULLS LAST"));