Repository: phoenix Updated Branches: refs/heads/4.x-HBase-1.1 ee5683603 -> 4d344b347
PHOENIX-3005 Fixes for COUNT(DISTINCT...) with DistinctPrefixFilter and indexes. Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/4d344b34 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/4d344b34 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/4d344b34 Branch: refs/heads/4.x-HBase-1.1 Commit: 4d344b3475b4deb058406edd5a738d5e1e73cc37 Parents: ee56836 Author: Lars Hofhansl <la...@apache.org> Authored: Sun Jun 19 21:21:42 2016 -0700 Committer: Lars Hofhansl <la...@apache.org> Committed: Sun Jun 19 21:22:28 2016 -0700 ---------------------------------------------------------------------- .../phoenix/end2end/DistinctPrefixFilterIT.java | 27 ++++++++++++-------- .../apache/phoenix/compile/GroupByCompiler.java | 7 ++--- .../DistinctCountAggregateFunction.java | 6 ++--- .../phoenix/compile/QueryOptimizerTest.java | 24 +++++++++++++++++ 4 files changed, 46 insertions(+), 18 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/4d344b34/phoenix-core/src/it/java/org/apache/phoenix/end2end/DistinctPrefixFilterIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DistinctPrefixFilterIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DistinctPrefixFilterIT.java index 0310183..6ef8e73 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DistinctPrefixFilterIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DistinctPrefixFilterIT.java @@ -34,14 +34,17 @@ public class DistinctPrefixFilterIT extends BaseHBaseManagedTimeTableReuseIT { conn.setAutoCommit(false); String ddl = "CREATE TABLE " + testTableF + " (prefix1 INTEGER NOT NULL, prefix2 INTEGER NOT NULL, prefix3 INTEGER NOT NULL, " + - "col1 FLOAT, CONSTRAINT pk PRIMARY KEY(prefix1, prefix2, prefix3))"; + "col1 FLOAT, col2 INTEGER, CONSTRAINT pk PRIMARY KEY(prefix1, prefix2, prefix3)) DISABLE_WAL=true, IMMUTABLE_ROWS=true"; createTestTable(getUrl(), ddl); ddl = "CREATE TABLE " + testTableV + " (prefix1 varchar NOT NULL, prefix2 varchar NOT NULL, prefix3 INTEGER NOT NULL, " + - "col1 FLOAT, CONSTRAINT pk PRIMARY KEY(prefix1, prefix2, prefix3))"; + "col1 FLOAT, col2 INTEGER, CONSTRAINT pk PRIMARY KEY(prefix1, prefix2, prefix3)) DISABLE_WAL=true, IMMUTABLE_ROWS=true"; createTestTable(getUrl(), ddl); + conn.prepareStatement("CREATE INDEX " + testTableF + "_idx ON "+testTableF+"(col2) DISABLE_WAL=true").execute(); + conn.prepareStatement("CREATE INDEX " + testTableV + "_idx ON "+testTableV+"(col2) DISABLE_WAL=true").execute(); + conn.prepareStatement("CREATE SEQUENCE " + testSeq + " CACHE 1000").execute(); insertPrefixF(1, 1); @@ -76,8 +79,7 @@ public class DistinctPrefixFilterIT extends BaseHBaseManagedTimeTableReuseIT { multiply(); multiply(); multiply(); - multiply(); - multiply(); // 512 per unique prefix + multiply(); // 256 per unique prefix } @Test @@ -150,7 +152,11 @@ public class DistinctPrefixFilterIT extends BaseHBaseManagedTimeTableReuseIT { testPlan("SELECT DISTINCT (prefix1, prefix2, prefix3) FROM "+testTable, false); testPlan("SELECT DISTINCT prefix1, prefix2, col1, prefix3 FROM "+testTable, false); testPlan("SELECT DISTINCT prefix1, prefix2, col1 FROM "+testTable, false); - testPlan("SELECT DISTINCT col1, prefix1, prefix2 FROM "+testTable, false);; + testPlan("SELECT DISTINCT col1, prefix1, prefix2 FROM "+testTable, false); + testPlan("SELECT DISTINCT col1 FROM "+testTable, false); + testPlan("SELECT COUNT(DISTINCT col1) FROM "+testTable, false); + testPlan("SELECT DISTINCT col2 FROM "+testTable, true); + testPlan("SELECT COUNT(DISTINCT col2) FROM "+testTable, true); testPlan("SELECT prefix1 FROM "+testTable+" GROUP BY prefix1", true); testPlan("SELECT COUNT(prefix1) FROM (SELECT prefix1 FROM "+testTable+" GROUP BY prefix1)", true); // aggregate over the group by, cannot optimize @@ -176,7 +182,7 @@ public class DistinctPrefixFilterIT extends BaseHBaseManagedTimeTableReuseIT { private void testPlan(String query, boolean optimizable) throws Exception { ResultSet rs = conn.createStatement().executeQuery("EXPLAIN "+query); - assertEquals(QueryUtil.getExplainPlan(rs).contains(PREFIX), optimizable); + assertEquals(optimizable, QueryUtil.getExplainPlan(rs).contains(PREFIX)); } @Test @@ -256,6 +262,7 @@ public class DistinctPrefixFilterIT extends BaseHBaseManagedTimeTableReuseIT { testCount("SELECT %s COUNT(DISTINCT prefix1), COUNT(DISTINCT (prefix1, prefix2)) FROM " + testTable + " WHERE col1 > 0.99", -1, -1); testCount("SELECT %s COUNT(DISTINCT col1) FROM " + testTable, -1); + testCount("SELECT %s COUNT(DISTINCT col2) FROM " + testTable, -1); } @Test @@ -333,7 +340,7 @@ public class DistinctPrefixFilterIT extends BaseHBaseManagedTimeTableReuseIT { private static void insertPrefixF(int prefix1, int prefix2) throws SQLException { String query = "UPSERT INTO " + testTableF - + "(prefix1, prefix2, prefix3, col1) VALUES(?,?,NEXT VALUE FOR "+testSeq+",rand())"; + + "(prefix1, prefix2, prefix3, col1, col2) VALUES(?,?,NEXT VALUE FOR "+testSeq+",rand(), trunc(rand()*1000))"; PreparedStatement stmt = conn.prepareStatement(query); stmt.setInt(1, prefix1); stmt.setInt(2, prefix2); @@ -342,7 +349,7 @@ public class DistinctPrefixFilterIT extends BaseHBaseManagedTimeTableReuseIT { private static void insertPrefixV(String prefix1, String prefix2) throws SQLException { String query = "UPSERT INTO " + testTableV - + "(prefix1, prefix2, prefix3, col1) VALUES(?,?,NEXT VALUE FOR "+testSeq+",rand())"; + + "(prefix1, prefix2, prefix3, col1, col2) VALUES(?,?,NEXT VALUE FOR "+testSeq+",rand(), trunc(rand()*1000))"; PreparedStatement stmt = conn.prepareStatement(query); stmt.setString(1, prefix1); stmt.setString(2, prefix2); @@ -351,9 +358,9 @@ public class DistinctPrefixFilterIT extends BaseHBaseManagedTimeTableReuseIT { private static void multiply() throws SQLException { conn.prepareStatement("UPSERT INTO " + testTableF - + " SELECT prefix1,prefix2,NEXT VALUE FOR "+testSeq+",rand() FROM "+testTableF).execute(); + + " SELECT prefix1,prefix2,NEXT VALUE FOR "+testSeq+",rand(), trunc(rand()*1000) FROM "+testTableF).execute(); conn.prepareStatement("UPSERT INTO " + testTableV - + " SELECT prefix1,prefix2,NEXT VALUE FOR "+testSeq+",rand() FROM "+testTableV).execute(); + + " SELECT prefix1,prefix2,NEXT VALUE FOR "+testSeq+",rand(), trunc(rand()*1000) FROM "+testTableV).execute(); conn.commit(); } } http://git-wip-us.apache.org/repos/asf/phoenix/blob/4d344b34/phoenix-core/src/main/java/org/apache/phoenix/compile/GroupByCompiler.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/GroupByCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/GroupByCompiler.java index 267d132..a405317 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/compile/GroupByCompiler.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/GroupByCompiler.java @@ -154,11 +154,8 @@ public class GroupByCompiler { isOrderPreserving = tracker.isOrderPreserving(); orderPreservingColumnCount = tracker.getOrderPreservingColumnCount(); } - if (isOrderPreserving) { - return new GroupBy.GroupByBuilder(this).setOrderPreservingColumnCount(orderPreservingColumnCount).build(); - } - if (isUngroupedAggregate) { - return UNGROUPED_GROUP_BY; + if (isOrderPreserving || isUngroupedAggregate) { + return new GroupBy.GroupByBuilder(this).setIsOrderPreserving(isOrderPreserving).setOrderPreservingColumnCount(orderPreservingColumnCount).build(); } List<Expression> expressions = Lists.newArrayListWithExpectedSize(this.expressions.size()); List<Expression> keyExpressions = expressions; http://git-wip-us.apache.org/repos/asf/phoenix/blob/4d344b34/phoenix-core/src/main/java/org/apache/phoenix/expression/function/DistinctCountAggregateFunction.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/DistinctCountAggregateFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/DistinctCountAggregateFunction.java index 6ce3c27..958f8fd 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/DistinctCountAggregateFunction.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/DistinctCountAggregateFunction.java @@ -21,16 +21,16 @@ import java.util.List; import org.apache.hadoop.conf.Configuration; import org.apache.hadoop.hbase.io.ImmutableBytesWritable; - import org.apache.phoenix.expression.Expression; import org.apache.phoenix.expression.aggregator.Aggregator; import org.apache.phoenix.expression.aggregator.DistinctCountClientAggregator; import org.apache.phoenix.expression.aggregator.DistinctValueWithCountServerAggregator; +import org.apache.phoenix.parse.DistinctCountParseNode; import org.apache.phoenix.parse.FunctionParseNode.Argument; import org.apache.phoenix.parse.FunctionParseNode.BuiltInFunction; +import org.apache.phoenix.schema.tuple.Tuple; import org.apache.phoenix.schema.types.PDataType; import org.apache.phoenix.schema.types.PLong; -import org.apache.phoenix.schema.tuple.Tuple; import org.apache.phoenix.util.SchemaUtil; @@ -41,7 +41,7 @@ import org.apache.phoenix.util.SchemaUtil; * * @since 1.2.1 */ -@BuiltInFunction(name=DistinctCountAggregateFunction.NAME, args= {@Argument()} ) +@BuiltInFunction(name=DistinctCountAggregateFunction.NAME, nodeClass=DistinctCountParseNode.class, args= {@Argument()} ) public class DistinctCountAggregateFunction extends DelegateConstantToCountAggregateFunction { public static final String NAME = "DISTINCT_COUNT"; public static final String NORMALIZED_NAME = SchemaUtil.normalizeIdentifier(NAME); http://git-wip-us.apache.org/repos/asf/phoenix/blob/4d344b34/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryOptimizerTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryOptimizerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryOptimizerTest.java index c3345ba..5f452f1 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryOptimizerTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryOptimizerTest.java @@ -613,6 +613,30 @@ public class QueryOptimizerTest extends BaseConnectionlessQueryTest { assertEquals("Query should not use index", PTableType.VIEW, plan.getTableRef().getTable().getType()); } + @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 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()); + } + private void assertPlanDetails(PreparedStatement stmt, String expectedPkCols, String expectedPkColsDataTypes, boolean expectedHasOrderBy, int expectedLimit) throws SQLException { Connection conn = stmt.getConnection(); QueryPlan plan = PhoenixRuntime.getOptimizedQueryPlan(stmt);
