private Scan compileQuery(String query, List<Object> binds) throws SQLException { QueryPlan plan = getQueryPlan(query, binds); return plan.getContext().getScan(); }
private Scan projectQuery(String query) throws SQLException { QueryPlan plan = getQueryPlan(query, Collections.emptyList()); plan.iterator(); // Forces projection return plan.getContext().getScan(); }
@Test public void testMultiCFProjection() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); String ddl = "CREATE TABLE multiCF (k integer primary key, a.a varchar, b.b varchar)"; conn.createStatement().execute(ddl); String query = "SELECT COUNT(*) FROM multiCF"; QueryPlan plan = getQueryPlan(query,Collections.emptyList()); plan.iterator(); Scan scan = plan.getContext().getScan(); assertTrue(scan.getFilter() instanceof FirstKeyOnlyFilter); assertEquals(1, scan.getFamilyMap().size()); }
int index = 0; for (String query : queries) { QueryPlan plan = getQueryPlan(conn, query); assertFalse((index + 1) + ") " + queries[index], plan.getOrderBy().getOrderByExpressions().isEmpty()); index++;
" )"); conn.createStatement().execute("CREATE INDEX SCORE_IDX ON GROUPBYTEST (ORGANIZATION_ID,CONTAINER_ID, SCORE DESC, ENTITY_ID DESC)"); QueryPlan plan = getQueryPlan(conn, "SELECT DISTINCT entity_id, score\n" + " FROM GROUPBYTEST\n" + " WHERE organization_id = 'org2'\n" + " LIMIT 2"); assertFalse(plan.getOrderBy().getOrderByExpressions().isEmpty()); plan = getQueryPlan(conn, "SELECT DISTINCT entity_id, score\n" + " FROM GROUPBYTEST\n" + " WHERE entity_id = 'entity1'\n" +
@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); } }
@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 testOrderPreservingGroupBy() throws Exception { try (Connection conn= DriverManager.getConnection(getUrl())) { conn.createStatement().execute("CREATE TABLE test (\n" + " pk1 INTEGER NOT NULL,\n" + " pk2 INTEGER NOT NULL,\n" + " pk3 INTEGER NOT NULL,\n" + " pk4 INTEGER NOT NULL,\n" + " v1 INTEGER,\n" + " CONSTRAINT pk PRIMARY KEY (\n" + " pk1,\n" + " pk2,\n" + " pk3,\n" + " pk4\n" + " )\n" + " )"); String[] queries = new String[] { "SELECT pk3 FROM test WHERE pk2 = 1 GROUP BY pk2+1,pk3 ORDER BY pk3", "SELECT pk3 FROM test WHERE pk2 = 1 GROUP BY pk2,pk3 ORDER BY pk3", "SELECT pk3 FROM test WHERE pk1 = 1 and pk2 = 2 GROUP BY pk1+pk2,pk3 ORDER BY pk3", "SELECT pk3 FROM test WHERE pk1 = 1 and pk2 = 2 GROUP BY pk4,CASE WHEN pk1 > pk2 THEN pk1 ELSE pk2 END,pk3 ORDER BY pk4,pk3", }; int index = 0; for (String query : queries) { QueryPlan plan = getQueryPlan(conn, query); assertTrue((index + 1) + ") " + queries[index], plan.getOrderBy().getOrderByExpressions().isEmpty()); index++; } } }
int index = 0; for (String query : queries) { QueryPlan plan = getQueryPlan(conn, query); assertTrue((index + 1) + ") " + queries[index], plan.getOrderBy().getOrderByExpressions().isEmpty()); index++;
int index = 0; for (String query : queries) { QueryPlan plan = getQueryPlan(conn, query); assertFalse((index + 1) + ") " + queries[index], plan.getOrderBy().getOrderByExpressions().isEmpty()); index++;
QueryPlan queryPlan=getQueryPlan(conn, sql); assertTrue((i+1) + ") " + sql,queryPlan.getGroupBy().isOrderPreserving()== groupBys[i]); OrderBy orderBy=queryPlan.getOrderBy();
QueryPlan queryPlan=getQueryPlan(conn, sql); assertTrue((i+1) + ") " + sql,queryPlan.getGroupBy().isOrderPreserving()== groupBys[i]); OrderBy orderBy=queryPlan.getOrderBy();
HashJoinPlan plan = (HashJoinPlan)getQueryPlan(sql, Collections.emptyList()); ScanRanges ranges=plan.getContext().getScanRanges();
" FROM " + "\""+ SYSTEM_CATALOG_SCHEMA + "\".\"" + SYSTEM_STATS_TABLE + "\"" + " LIMIT 1"; List<Object> binds = Collections.emptyList(); QueryPlan plan = getQueryPlan(query, binds); RowProjector p = plan.getProjector();
conn.createStatement().execute(sql); 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); queryPlan=getQueryPlan(conn, sql); assertTrue(queryPlan.getGroupBy().getExpressions().get(0).getSortOrder()==SortOrder.DESC); assertTrue(queryPlan.getGroupBy().getExpressions().get(1).getSortOrder()==SortOrder.DESC); queryPlan=getQueryPlan(conn, sql); assertTrue(queryPlan.getGroupBy().getExpressions().get(0).getSortOrder()==SortOrder.DESC); assertTrue(queryPlan.getGroupBy().getExpressions().get(1).getSortOrder()==SortOrder.DESC);
QueryPlan queryPlan =getQueryPlan(conn, sql); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID")); queryPlan =getQueryPlan(conn, sql); assertTrue(queryPlan.getOrderBy()== OrderBy.REV_ROW_KEY_ORDER_BY); queryPlan =getQueryPlan(conn, sql); assertTrue(queryPlan.getOrderBy()== OrderBy.FWD_ROW_KEY_ORDER_BY); queryPlan =getQueryPlan(conn, sql); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("ORGANIZATION_ID DESC NULLS LAST")); queryPlan =getQueryPlan(conn, sql); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID")); queryPlan =getQueryPlan(conn, sql); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID NULLS LAST")); queryPlan =getQueryPlan(conn, sql); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID DESC")); queryPlan =getQueryPlan(conn, sql); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().get(0).toString().equals("CONTAINER_ID DESC NULLS LAST"));
@Test public void testColumnProjectionUnionAll() throws SQLException { Connection conn = DriverManager.getConnection(getUrl()); try { conn.createStatement().execute("CREATE TABLE t1(k INTEGER PRIMARY KEY,"+ " col1 CHAR(8), col2 VARCHAR(10), col3 decimal(10,2))"); conn.createStatement().execute("CREATE TABLE t2(k TINYINT PRIMARY KEY," + " col1 CHAR(20), col2 CHAR(30), col3 double)"); QueryPlan plan = getQueryPlan("SELECT * from t1 union all select * from t2", Collections.emptyList()); RowProjector rowProj = plan.getProjector(); assertTrue(rowProj.getColumnProjector(0).getExpression().getDataType() instanceof PInteger); assertTrue(rowProj.getColumnProjector(1).getExpression().getDataType() instanceof PChar); assertTrue(rowProj.getColumnProjector(1).getExpression().getMaxLength() == 20); assertTrue(rowProj.getColumnProjector(2).getExpression().getDataType() instanceof PVarchar); assertTrue(rowProj.getColumnProjector(2).getExpression().getMaxLength() == 30); assertTrue(rowProj.getColumnProjector(3).getExpression().getDataType() instanceof PDecimal); assertTrue(rowProj.getColumnProjector(3).getExpression().getScale() == 2); } finally { conn.close(); } }
QueryPlan queryPlan =getQueryPlan(conn, sql); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); queryPlan =getQueryPlan(conn, sql); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy()== OrderBy.REV_ROW_KEY_ORDER_BY); queryPlan =getQueryPlan(conn, sql); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy()== OrderBy.FWD_ROW_KEY_ORDER_BY); queryPlan =getQueryPlan(conn, sql); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); queryPlan =getQueryPlan(conn, sql); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); queryPlan =getQueryPlan(conn, sql); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); queryPlan =getQueryPlan(conn, sql); assertTrue(queryPlan.getGroupBy().isOrderPreserving()); assertTrue(queryPlan.getOrderBy().getOrderByExpressions().size()==1); queryPlan =getQueryPlan(conn, sql);
"(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(); queryPlan=getQueryPlan(conn, sql); sortMergeJoinPlan=(SortMergeJoinPlan)((ClientScanPlan)queryPlan).getDelegate();