@Override public OrderBy getOrderBy() { return delegate.getOrderBy(); }
/** * Whether or not the query plan has any order by expressions. * @param plan * @return */ public static boolean hasOrderBy(QueryPlan plan) { checkNotNull(plan); List<OrderByExpression> orderBys = plan.getOrderBy().getOrderByExpressions(); return orderBys != null && !orderBys.isEmpty(); }
private synchronized void openCursor(Connection conn) throws SQLException { if(isOpen){ return; } this.scan = this.queryPlan.getContext().getScan(); isReversed=OrderBy.REV_ROW_KEY_ORDER_BY.equals(this.queryPlan.getOrderBy()); isOpen = true; }
private void assertOrderByHasBeenOptimizedOut(Connection conn, String sql) throws SQLException { PreparedStatement stmt = conn.prepareStatement(sql); QueryPlan plan = PhoenixRuntime.getOptimizedQueryPlan(stmt); assertEquals(0, plan.getOrderBy().getOrderByExpressions().size()); }
@Test public void testOrderByDropped() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); try{ conn.createStatement().execute("CREATE TABLE foo (k VARCHAR NOT NULL PRIMARY KEY, v VARCHAR) IMMUTABLE_ROWS=true"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT * FROM foo ORDER BY 'a','b','c'"); assertTrue(plan.getOrderBy().getOrderByExpressions().isEmpty()); } finally { conn.close(); } }
@Test public void testOrderByDroppedCompositeKey() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE foo (j INTEGER NOT NULL, k BIGINT NOT NULL, v VARCHAR CONSTRAINT pk PRIMARY KEY (j,k)) IMMUTABLE_ROWS=true"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT * FROM foo ORDER BY j,k"); assertEquals(OrderBy.FWD_ROW_KEY_ORDER_BY,plan.getOrderBy()); }
@Test public void testOrderByNotDroppedCompositeKey() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE foo (j INTEGER NOT NULL, k BIGINT NOT NULL, v VARCHAR CONSTRAINT pk PRIMARY KEY (j,k)) IMMUTABLE_ROWS=true"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT * FROM foo ORDER BY k,j"); assertFalse(plan.getOrderBy().getOrderByExpressions().isEmpty()); }
@Test public void testOrderByOptimizedOut() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE foo (k VARCHAR NOT NULL PRIMARY KEY, v VARCHAR) IMMUTABLE_ROWS=true"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT * FROM foo ORDER BY k"); assertEquals(OrderBy.FWD_ROW_KEY_ORDER_BY,plan.getOrderBy()); }
@Test public void testOrderByNotDropped() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE foo (k VARCHAR NOT NULL PRIMARY KEY, v VARCHAR) IMMUTABLE_ROWS=true"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT * FROM foo ORDER BY v"); assertFalse(plan.getOrderBy().getOrderByExpressions().isEmpty()); }
@Test public void testNotOrderByOrderPreservingForAggregation() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE IF NOT EXISTS VA_TEST(ID VARCHAR NOT NULL PRIMARY KEY, VAL1 VARCHAR, VAL2 INTEGER)"); String[] queries = { "select distinct ID, VAL1, VAL2 from VA_TEST where \"ID\" in ('ABC','ABD','ABE','ABF','ABG','ABH','AAA', 'AAB', 'AAC','AAD','AAE','AAF') order by VAL1 ASC" }; String query; for (int i = 0; i < queries.length; i++) { query = queries[i]; QueryPlan plan = conn.createStatement().unwrap(PhoenixStatement.class).compileQuery(query); assertFalse("Expected order by not to be compiled out: " + query, plan.getOrderBy().getOrderByExpressions().isEmpty()); } }
@Test public void testOrderByOrderPreservingRev() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k1 date not null, k2 date not null, k3 varchar, v varchar, constraint pk primary key(k1,k2 DESC,k3 DESC))"); String[] queries = { "SELECT * FROM T ORDER BY INVERT(k1),k2,k3 nulls last", "SELECT * FROM T ORDER BY INVERT(k1),k2", "SELECT * FROM T ORDER BY INVERT(k1)", "SELECT * FROM T ORDER BY TRUNC(k1, 'DAY') DESC, CEIL(k2, 'HOUR') DESC", "SELECT * FROM T ORDER BY k1 DESC", }; String query; for (int i = 0; i < queries.length; i++) { query = queries[i]; QueryPlan plan = conn.createStatement().unwrap(PhoenixStatement.class).compileQuery(query); assertTrue("Expected order by to be compiled out: " + query, plan.getOrderBy() == OrderBy.REV_ROW_KEY_ORDER_BY); } }
/** * 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 testOrderByOrderPreservingFwd() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k1 date not null, k2 date not null, k3 varchar, v varchar, constraint pk primary key(k1,k2,k3))"); String[] queries = { "SELECT * FROM T WHERE k2=CURRENT_DATE() ORDER BY k1, k3", "SELECT * FROM T ORDER BY (k1,k2), k3", "SELECT * FROM T ORDER BY k1,k2,k3 NULLS FIRST", "SELECT * FROM T ORDER BY k1,k2,k3", "SELECT * FROM T ORDER BY k1,k2", "SELECT * FROM T ORDER BY k1", "SELECT * FROM T ORDER BY CAST(k1 AS TIMESTAMP)", "SELECT * FROM T ORDER BY (k1,k2,k3)", "SELECT * FROM T ORDER BY TRUNC(k1, 'DAY'), CEIL(k2, 'HOUR')", "SELECT * FROM T ORDER BY INVERT(k1) DESC", "SELECT * FROM T WHERE k1=CURRENT_DATE() ORDER BY k2", }; String query; for (int i = 0; i < queries.length; i++) { query = queries[i]; QueryPlan plan = conn.createStatement().unwrap(PhoenixStatement.class).compileQuery(query); assertTrue("Expected order by to be compiled out: " + query, plan.getOrderBy() == OrderBy.FWD_ROW_KEY_ORDER_BY); } }
@Test public void testNotOrderByOrderPreserving() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k1 date not null, k2 varchar, k3 varchar, v varchar, constraint pk primary key(k1,k2,k3 desc))"); String[] queries = { "SELECT * FROM T ORDER BY k1,k2 NULLS LAST", "SELECT * FROM T ORDER BY k1,k2, k3 NULLS LAST", "SELECT * FROM T ORDER BY k1,k3", "SELECT * FROM T ORDER BY SUBSTR(TO_CHAR(k1),1,4)", "SELECT * FROM T ORDER BY k2", "SELECT * FROM T ORDER BY INVERT(k1),k3", "SELECT * FROM T ORDER BY CASE WHEN k1 = CURRENT_DATE() THEN 0 ELSE 1 END", "SELECT * FROM T ORDER 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 order by not to be compiled out: " + query, plan.getOrderBy().getOrderByExpressions().isEmpty()); } }
" ORDER BY score DESC\n" + " LIMIT 2"); assertFalse(plan.getOrderBy().getOrderByExpressions().isEmpty()); plan = getQueryPlan(conn, "SELECT DISTINCT entity_id, score\n" + " FROM GROUPBYTEST\n" + " ORDER BY score DESC\n" + " LIMIT 2"); assertTrue(plan.getOrderBy().getOrderByExpressions().isEmpty());
@Test public void testQueryViewStatementOptimization() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); String fullTableName = SchemaUtil.getTableName(SCHEMA1, generateUniqueName()); String fullViewName1 = SchemaUtil.getTableName(SCHEMA2, generateUniqueName()); String fullViewName2 = SchemaUtil.getTableName(SCHEMA3, generateUniqueName()); String sql = "CREATE TABLE " + fullTableName + " (k1 INTEGER NOT NULL, k2 INTEGER NOT NULL, v1 DECIMAL, CONSTRAINT pk PRIMARY KEY (k1, k2))" + tableDDLOptions; conn.createStatement().execute(sql); sql = "CREATE VIEW " + fullViewName1 + " AS SELECT * FROM " + fullTableName; conn.createStatement().execute(sql); sql = "CREATE VIEW " + fullViewName2 + " AS SELECT * FROM " + fullTableName + " WHERE k1 = 1.0"; conn.createStatement().execute(sql); sql = "SELECT * FROM " + fullViewName1 + " order by k1, k2"; PreparedStatement stmt = conn.prepareStatement(sql); QueryPlan plan = PhoenixRuntime.getOptimizedQueryPlan(stmt); assertEquals(0, plan.getOrderBy().getOrderByExpressions().size()); sql = "SELECT * FROM " + fullViewName2 + " order by k1, k2"; stmt = conn.prepareStatement(sql); plan = PhoenixRuntime.getOptimizedQueryPlan(stmt); assertEquals(0, plan.getOrderBy().getOrderByExpressions().size()); }
QueryPlan plan = conn.createStatement().unwrap(PhoenixStatement.class).compileQuery(query); assertTrue(query, plan.getOrderBy() == OrderBy.REV_ROW_KEY_ORDER_BY);
String query = queries[i]; QueryPlan plan = conn.createStatement().unwrap(PhoenixStatement.class).compileQuery(query); assertTrue(plan.getOrderBy() == OrderBy.FWD_ROW_KEY_ORDER_BY);
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); }
public QueryPlan compileUnionAll(SelectStatement select) throws SQLException { List<SelectStatement> unionAllSelects = select.getSelects(); List<QueryPlan> plans = new ArrayList<QueryPlan>(); for (int i=0; i < unionAllSelects.size(); i++ ) { SelectStatement subSelect = unionAllSelects.get(i); // Push down order-by and limit into sub-selects. if (!select.getOrderBy().isEmpty() || select.getLimit() != null) { if (select.getOffset() == null) { subSelect = NODE_FACTORY.select(subSelect, select.getOrderBy(), select.getLimit(), null); } else { subSelect = NODE_FACTORY.select(subSelect, select.getOrderBy(), null, null); } } QueryPlan subPlan = compileSubquery(subSelect, true); plans.add(subPlan); } TableRef tableRef = UnionCompiler.contructSchemaTable(statement, plans, select.hasWildcard() ? null : select.getSelect()); ColumnResolver resolver = FromCompiler.getResolver(tableRef); StatementContext context = new StatementContext(statement, resolver, scan, sequenceManager); QueryPlan plan = compileSingleFlatQuery(context, select, statement.getParameters(), false, false, null, null, false); plan = new UnionPlan(context, select, tableRef, plan.getProjector(), plan.getLimit(), plan.getOffset(), plan.getOrderBy(), GroupBy.EMPTY_GROUP_BY, plans, context.getBindManager().getParameterMetaData()); return plan; }