DRILL-595, DRILL-811, DRILL-1029: Support select * in view, subquery, table expression in with clause. Most fixes in Optiq.
Project: http://git-wip-us.apache.org/repos/asf/incubator-drill/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-drill/commit/4f59d4f0 Tree: http://git-wip-us.apache.org/repos/asf/incubator-drill/tree/4f59d4f0 Diff: http://git-wip-us.apache.org/repos/asf/incubator-drill/diff/4f59d4f0 Branch: refs/heads/master Commit: 4f59d4f0f835fec935cbdf05a277015cb763b9e4 Parents: c4cc9e4 Author: Jinfeng Ni <j...@maprtech.com> Authored: Fri Aug 15 17:21:38 2014 -0700 Committer: Jacques Nadeau <jacq...@apache.org> Committed: Wed Aug 27 13:33:47 2014 -0700 ---------------------------------------------------------------------- .../org/apache/drill/exec/dotdrill/View.java | 10 ++ .../drill/exec/planner/StarColumnHelper.java | 10 +- .../drill/exec/planner/logical/DrillOptiq.java | 10 ++ .../exec/planner/logical/DrillViewTable.java | 3 +- .../org/apache/drill/TestExampleQueries.java | 150 +++++++++++++++++++ pom.xml | 2 +- 6 files changed, 182 insertions(+), 3 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/4f59d4f0/exec/java-exec/src/main/java/org/apache/drill/exec/dotdrill/View.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/dotdrill/View.java b/exec/java-exec/src/main/java/org/apache/drill/exec/dotdrill/View.java index 7965e02..8119c29 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/dotdrill/View.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/dotdrill/View.java @@ -20,6 +20,7 @@ package org.apache.drill.exec.dotdrill; import java.util.List; import com.google.common.collect.ImmutableList; +import org.apache.drill.exec.planner.StarColumnHelper; import org.apache.drill.exec.planner.types.RelDataTypeDrillImpl; import org.apache.drill.exec.planner.types.RelDataTypeHolder; import org.eigenbase.reltype.RelDataType; @@ -135,6 +136,15 @@ public class View { return fields.isEmpty(); } + @JsonIgnore + public boolean hasStar() { + for (FieldType field : fields) { + if (StarColumnHelper.isNonPrefixedStarColumn(field.name)) + return true; + } + return false; + } + public String getSql() { return sql; } http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/4f59d4f0/exec/java-exec/src/main/java/org/apache/drill/exec/planner/StarColumnHelper.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/StarColumnHelper.java b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/StarColumnHelper.java index 4c04b22..591faf9 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/StarColumnHelper.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/StarColumnHelper.java @@ -45,7 +45,15 @@ public class StarColumnHelper { public static boolean isPrefixedStarColumn(String fieldName) { return fieldName.indexOf(PREFIXED_STAR_COLUMN) > 0 ; // the delimiter * starts at none-zero position. } - + + public static boolean isNonPrefixedStarColumn(String fieldName) { + return fieldName.startsWith("*"); + } + + public static boolean isStarColumn(String fieldName) { + return isPrefixedStarColumn(fieldName) || isNonPrefixedStarColumn(fieldName); + } + public static String extractStarColumnPrefix(String fieldName) { assert (isPrefixedStarColumn(fieldName)); http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/4f59d4f0/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java index aa88310..891b889 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java @@ -36,6 +36,7 @@ import org.apache.drill.common.types.TypeProtos.MajorType; import org.apache.drill.common.types.TypeProtos; import org.apache.drill.common.types.TypeProtos.MinorType; import org.apache.drill.common.types.Types; +import org.apache.drill.exec.planner.StarColumnHelper; import org.eigenbase.rel.RelNode; import org.eigenbase.reltype.RelDataTypeField; import org.eigenbase.rex.RexCall; @@ -167,6 +168,15 @@ public class DrillOptiq { if (call.getOperator() == SqlStdOperatorTable.ITEM) { SchemaPath left = (SchemaPath) call.getOperands().get(0).accept(this); + + // Convert expr of item[*, 'abc'] into column expression 'abc' + String rootSegName = left.getRootSegment().getPath(); + if (StarColumnHelper.isStarColumn(rootSegName)) { + rootSegName = rootSegName.substring(0, rootSegName.indexOf("*")); + final RexLiteral literal = (RexLiteral) call.getOperands().get(1); + return SchemaPath.getSimplePath(rootSegName + literal.getValue2().toString()); + } + final RexLiteral literal = (RexLiteral) call.getOperands().get(1); switch(literal.getTypeName()){ case DECIMAL: http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/4f59d4f0/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillViewTable.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillViewTable.java b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillViewTable.java index e798d4e..68e666a 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillViewTable.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillViewTable.java @@ -56,7 +56,8 @@ public class DrillViewTable implements TranslatableTable, DrillViewInfoProvider RelDataType rowType = relOptTable.getRowType(); RelNode rel = context.expandView(rowType, view.getSql(), view.getWorkspaceSchemaPath()); - if (view.isDynamic()){ + if (view.isDynamic() || view.hasStar()){ + // if View's field has "*", return rel directly. return rel; }else{ // if the View's field list is not "*", try to create a cast. http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/4f59d4f0/exec/java-exec/src/test/java/org/apache/drill/TestExampleQueries.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/test/java/org/apache/drill/TestExampleQueries.java b/exec/java-exec/src/test/java/org/apache/drill/TestExampleQueries.java index d9e8b20..0053388 100644 --- a/exec/java-exec/src/test/java/org/apache/drill/TestExampleQueries.java +++ b/exec/java-exec/src/test/java/org/apache/drill/TestExampleQueries.java @@ -18,6 +18,7 @@ package org.apache.drill; import org.apache.drill.common.util.FileUtils; +import org.apache.drill.exec.rpc.RpcException; import org.junit.Ignore; import org.junit.Test; @@ -321,4 +322,153 @@ public class TestExampleQueries extends BaseTestQuery{ test(query); } + @Test + public void testSelStarSubQJson2() throws Exception { + test("select v.first_name from (select * from cp.`employee.json`) v limit 2" ); + } + + // Select * in SubQuery, View or CTE (With clause) + @Test // Select * in SubQuery : regular columns appear in select clause, where, group by, order by. + public void testSelStarSubQPrefix() throws Exception { + test("select t.n_nationkey, t.n_name, t.n_regionkey from (select * from cp.`tpch/nation.parquet`) t where t.n_regionkey > 1 order by t.n_name" ); + + test("select n.n_regionkey, count(*) as cnt from ( select * from ( select * from cp.`tpch/nation.parquet`) t where t.n_nationkey < 10 ) n where n.n_nationkey >1 group by n.n_regionkey order by n.n_regionkey ; "); + + test("select t.n_regionkey, count(*) as cnt from (select * from cp.`tpch/nation.parquet`) t where t.n_nationkey > 1 group by t.n_regionkey order by t.n_regionkey;" ); + } + + @Test // Select * in SubQuery : regular columns appear in select clause, where, group by, order by. + public void testSelStarSubQNoPrefix() throws Exception { + test("select n_nationkey, n_name, n_regionkey from (select * from cp.`tpch/nation.parquet`) where n_regionkey > 1 order by n_name" ); + + test("select n_regionkey, count(*) as cnt from ( select * from ( select * from cp.`tpch/nation.parquet`) where n_nationkey < 10 ) where n_nationkey >1 group by n_regionkey order by n_regionkey ; "); + + test("select n_regionkey, count(*) as cnt from (select * from cp.`tpch/nation.parquet`) t where n_nationkey > 1 group by n_regionkey order by n_regionkey;" ); + } + + @Test // join two SubQuery, each having select * : regular columns appear in the select , where and on clause, group by, order by. + public void testSelStarSubQJoin() throws Exception { + // select clause, where. + test(" select n.n_nationkey, n.n_name, n.n_regionkey, r.r_name \n" + + " from (select * from cp.`tpch/nation.parquet`) n, \n" + + " (select * from cp.`tpch/region.parquet`) r \n" + + " where n.n_regionkey = r.r_regionkey " ); + + // select clause, where, group by, order by + test(" select n.n_regionkey, count(*) as cnt \n" + + " from (select * from cp.`tpch/nation.parquet`) n \n" + + " , (select * from cp.`tpch/region.parquet`) r \n" + + " where n.n_regionkey = r.r_regionkey and n.n_nationkey > 10 \n" + + " group by n.n_regionkey \n" + + " order by n.n_regionkey; " ); + + // select clause, where, on, group by, order by. + test(" select n.n_regionkey, count(*) as cnt \n" + + " from (select * from cp.`tpch/nation.parquet`) n \n" + + " join (select * from cp.`tpch/region.parquet`) r \n" + + " on n.n_regionkey = r.r_regionkey \n" + + " where n.n_nationkey > 10 \n" + + " group by n.n_regionkey \n" + + " order by n.n_regionkey; " ); + + // Outer query use select *. Join condition in where clause. + test(" select * \n" + + " from (select * from cp.`tpch/nation.parquet`) n \n" + + " , (select * from cp.`tpch/region.parquet`) r \n" + + " where n.n_regionkey = r.r_regionkey " ); + + // Outer query use select *. Join condition in on clause. + test(" select * \n" + + " from (select * from cp.`tpch/nation.parquet`) n \n" + + " join (select * from cp.`tpch/region.parquet`) r \n" + + " on n.n_regionkey = r.r_regionkey " ); + } + + @Test // DRILL-595 : Select * in CTE WithClause : regular columns appear in select clause, where, group by, order by. + public void testDRILL_595WithClause() throws Exception { + test(" with x as (select * from cp.`region.json`) \n" + + " select x.region_id, x.sales_city \n" + + " from x where x.region_id > 10 limit 5;"); + + test(" with x as (select * from cp.`region.json`) \n" + + " select region_id, sales_city \n" + + " from x where region_id > 10 limit 5;"); + + test(" with x as (select * from cp.`tpch/nation.parquet`) \n" + + " select x.n_regionkey, count(*) as cnt \n" + + " from x \n" + + " where x.n_nationkey > 5 \n" + + " group by x.n_regionkey \n" + + " order by cnt limit 5; "); + + } + + @Test // DRILL-595 : Join two CTE, each having select * : regular columns appear in the select , where and on clause, group by, order by. + public void testDRILL_595WithClauseJoin() throws Exception { + test("with n as (select * from cp.`tpch/nation.parquet`), \n " + + " r as (select * from cp.`tpch/region.parquet`) \n" + + "select n.n_nationkey, n.n_name, n.n_regionkey, r.r_name \n" + + "from n, r \n" + + "where n.n_regionkey = r.r_regionkey ;" ); + + test("with n as (select * from cp.`tpch/nation.parquet`), \n " + + " r as (select * from cp.`tpch/region.parquet`) \n" + + "select n.n_regionkey, count(*) as cnt \n" + + "from n, r \n" + + "where n.n_regionkey = r.r_regionkey and n.n_nationkey > 5 \n" + + "group by n.n_regionkey \n" + + "order by cnt;" ); + } + + @Test // DRILL-811 + public void testDRILL_811View() throws Exception { + test("use dfs.tmp"); + test("create view nation_view as select * from cp.`tpch/nation.parquet`;"); + + test("select n.n_nationkey, n.n_name, n.n_regionkey from nation_view n where n.n_nationkey > 8 order by n.n_regionkey"); + + test("select n.n_regionkey, count(*) as cnt from nation_view n where n.n_nationkey > 8 group by n.n_regionkey order by n.n_regionkey"); + + test("drop view nation_view "); + } + + @Test // DRILL-811 + public void testDRILL_811ViewJoin() throws Exception { + test("use dfs.tmp"); + test("create view nation_view as select * from cp.`tpch/nation.parquet`;"); + test("create view region_view as select * from cp.`tpch/region.parquet`;"); + + test("select n.n_nationkey, n.n_regionkey, r.r_name from region_view r , nation_view n where r.r_regionkey = n.n_regionkey "); + + test("select n.n_regionkey, count(*) as cnt from region_view r , nation_view n where r.r_regionkey = n.n_regionkey and n.n_nationkey > 8 group by n.n_regionkey order by n.n_regionkey"); + + test("select n.n_regionkey, count(*) as cnt from region_view r join nation_view n on r.r_regionkey = n.n_regionkey and n.n_nationkey > 8 group by n.n_regionkey order by n.n_regionkey"); + + test("drop view region_view "); + test("drop view nation_view "); + } + + @Test // DRILL-811 + public void testDRILL_811Json() throws Exception { + test("use dfs.tmp"); + test("create view region_view as select * from cp.`region.json`;"); + test("select sales_city, sales_region from region_view where region_id > 50 order by sales_country; "); + test("drop view region_view "); + } + + @Test(expected = RpcException.class) // Should get "At line 1, column 8: Column 'n_nationkey' is ambiguous" + public void testSelStarAmbiguousJoin() throws Exception { + test("select x.n_nationkey, x.n_name, x.n_regionkey, x.r_name from (select * from cp.`tpch/nation.parquet` n, cp.`tpch/region.parquet` r where n.n_regionkey = r.r_regionkey) x " ) ; + } + + @Test // select star for a SchemaTable. + public void testSelStarSubQSchemaTable() throws Exception { + test("select name, kind, type from (select * from sys.options);"); + } + + @Test // Join a select star of SchemaTable, with a select star of Schema-less table. + public void testSelStarJoinSchemaWithSchemaLess() throws Exception { + test("select t1.name, t1.kind, t2.n_nationkey from (select * from sys.options) t1 join (select * from cp.`tpch/nation.parquet`) t2 on t1.name = t2.n_name;"); + } + } http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/4f59d4f0/pom.xml ---------------------------------------------------------------------- diff --git a/pom.xml b/pom.xml index f04d5b2..de02c92 100644 --- a/pom.xml +++ b/pom.xml @@ -664,7 +664,7 @@ <dependency> <groupId>net.hydromatic</groupId> <artifactId>optiq-core</artifactId> - <version>0.9-SNAPSHOT</version> + <version>0.9-drill-r2</version> <exclusions> <exclusion> <groupId>org.jgrapht</groupId>
