You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@drill.apache.org by am...@apache.org on 2014/12/14 05:49:10 UTC
[2/2] drill git commit: DRILL-1788: Test for case-insensitive join.
Fix is in Calcite version 0.9-drill-r11.
DRILL-1788: Test for case-insensitive join. Fix is in Calcite version 0.9-drill-r11.
Bump up optiq version to 0.9-drill-r11.
Refactor TestExampleQueries by moving the Star queries to separate test file.
Project: http://git-wip-us.apache.org/repos/asf/drill/repo
Commit: http://git-wip-us.apache.org/repos/asf/drill/commit/7980a713
Tree: http://git-wip-us.apache.org/repos/asf/drill/tree/7980a713
Diff: http://git-wip-us.apache.org/repos/asf/drill/diff/7980a713
Branch: refs/heads/master
Commit: 7980a7137da7c18e49709a2da0fd9cce3e485507
Parents: bc7d1a0
Author: Aman Sinha <as...@maprtech.com>
Authored: Fri Dec 12 19:09:56 2014 -0800
Committer: Aman Sinha <as...@maprtech.com>
Committed: Sat Dec 13 20:24:05 2014 -0800
----------------------------------------------------------------------
.../org/apache/drill/TestExampleQueries.java | 191 +----------------
.../java/org/apache/drill/TestStarQueries.java | 211 +++++++++++++++++++
pom.xml | 2 +-
3 files changed, 218 insertions(+), 186 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/drill/blob/7980a713/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 2427d2c..c431799 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
@@ -28,12 +28,6 @@ import org.junit.Test;
public class TestExampleQueries extends BaseTestQuery{
static final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(TestExampleQueries.class);
- @Test // see DRILL-1811
- public void testSelStarDifferentColumnOrder() throws Exception {
- test("select first_name, * from cp.`employee.json`;");
- test("select *, first_name, *, last_name from cp.`employee.json`;");
- }
-
@Test // see DRILL-985
public void testViewFileName() throws Exception {
test("use dfs.tmp");
@@ -83,64 +77,6 @@ public class TestExampleQueries extends BaseTestQuery{
}
@Test
- public void testSelStarOrderBy() throws Exception{
- test("select * from cp.`employee.json` order by last_name");
- }
-
- @Test
- public void testSelStarOrderByLimit() throws Exception{
- test("select * from cp.`employee.json` order by last_name limit 2;");
- }
-
- @Test
- public void testSelStarPlusRegCol() throws Exception{
- test("select *, n_nationkey from cp.`tpch/nation.parquet` limit 2;");
- }
-
- @Test
- public void testSelStarWhereOrderBy() throws Exception{
- test("select * from cp.`employee.json` where first_name = 'James' order by last_name");
- }
-
- @Test
- public void testSelStarJoin() throws Exception {
- test("select * from cp.`tpch/nation.parquet` n, cp.`tpch/region.parquet` r where n.n_regionkey = r.r_regionkey order by n.n_name;");
- }
-
- @Test
- public void testSelLeftStarJoin() throws Exception {
- test("select n.* from cp.`tpch/nation.parquet` n, cp.`tpch/region.parquet` r where n.n_regionkey = r.r_regionkey order by n.n_name;");
- }
-
- @Test
- public void testSelRightStarJoin() throws Exception {
- test("select r.* from cp.`tpch/nation.parquet` n, cp.`tpch/region.parquet` r where n.n_regionkey = r.r_regionkey order by n.n_name;");
- }
-
- @Test
- public void testSelStarRegColConstJoin() throws Exception {
- test("select *, n.n_nationkey as n_nationkey0, 1 + 2 as constant from cp.`tpch/nation.parquet` n, cp.`tpch/region.parquet` r where n.n_regionkey = r.r_regionkey order by n.n_name;");
- }
-
- @Test
- public void testSelStarBothSideJoin() throws Exception {
- test("select n.*, r.* from cp.`tpch/nation.parquet` n, cp.`tpch/region.parquet` r where n.n_regionkey = r.r_regionkey;");
- }
-
- @Test
- public void testSelStarJoinSameColName() throws Exception {
- test("select * from cp.`tpch/nation.parquet` n1, cp.`tpch/nation.parquet` n2 where n1.n_nationkey = n2.n_nationkey;");
- }
-
- @Test // DRILL-1293
- public void testStarView1() throws Exception {
- test("use dfs.tmp");
- test("create view vt1 as select * from cp.`tpch/region.parquet` r, cp.`tpch/nation.parquet` n where r.r_regionkey = n.n_regionkey");
- test("select * from vt1");
- test("drop view vt1");
- }
-
- @Test
public void testJoinExpOn() throws Exception{
test("select a.n_nationkey from cp.`tpch/nation.parquet` a join cp.`tpch/region.parquet` b on a.n_regionkey + 1 = b.r_regionkey and a.n_regionkey + 1 = b.r_regionkey;");
}
@@ -344,107 +280,6 @@ 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; " );
-
- // 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
- public void testSelectStartSubQueryJoinWithWhereClause() throws Exception {
- // 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; " );
- }
-
- @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");
@@ -481,26 +316,6 @@ public class TestExampleQueries extends BaseTestQuery{
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 {
- try {
- 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 " ) ;
- } catch (RpcException e) {
- logger.info("***** Test resulted in expected failure: " + e.getMessage());
- throw e;
- }
- }
-
- @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;");
- }
-
@Test
public void testCase() throws Exception {
test("select case when n_nationkey > 0 and n_nationkey < 2 then concat(n_name, '_abc') when n_nationkey >=2 and n_nationkey < 4 then '_EFG' else concat(n_name,'_XYZ') end from cp.`tpch/nation.parquet` ;");
@@ -603,4 +418,10 @@ public class TestExampleQueries extends BaseTestQuery{
test("alter session set `planner.slice_target` = 1; " + query);
}
+ @Test // DRILL-1788
+ public void testCaseInsensitiveJoin() throws Exception {
+ test("select n3.n_name from (select n2.n_name from cp.`tpch/nation.parquet` n1, cp.`tpch/nation.parquet` n2 where n1.N_name = n2.n_name) n3 " +
+ " join cp.`tpch/nation.parquet` n4 on n3.n_name = n4.n_name");
+ }
+
}
http://git-wip-us.apache.org/repos/asf/drill/blob/7980a713/exec/java-exec/src/test/java/org/apache/drill/TestStarQueries.java
----------------------------------------------------------------------
diff --git a/exec/java-exec/src/test/java/org/apache/drill/TestStarQueries.java b/exec/java-exec/src/test/java/org/apache/drill/TestStarQueries.java
new file mode 100644
index 0000000..bbcc424
--- /dev/null
+++ b/exec/java-exec/src/test/java/org/apache/drill/TestStarQueries.java
@@ -0,0 +1,211 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.drill;
+
+import org.junit.Test;
+import org.apache.drill.exec.rpc.RpcException;
+
+public class TestStarQueries extends BaseTestQuery{
+ static final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(TestStarQueries.class);
+
+ @Test
+ public void testSelStarOrderBy() throws Exception{
+ test("select * from cp.`employee.json` order by last_name");
+ }
+
+ @Test
+ public void testSelStarOrderByLimit() throws Exception{
+ test("select * from cp.`employee.json` order by last_name limit 2;");
+ }
+
+ @Test
+ public void testSelStarPlusRegCol() throws Exception{
+ test("select *, n_nationkey from cp.`tpch/nation.parquet` limit 2;");
+ }
+
+ @Test
+ public void testSelStarWhereOrderBy() throws Exception{
+ test("select * from cp.`employee.json` where first_name = 'James' order by last_name");
+ }
+
+ @Test
+ public void testSelStarJoin() throws Exception {
+ test("select * from cp.`tpch/nation.parquet` n, cp.`tpch/region.parquet` r where n.n_regionkey = r.r_regionkey order by n.n_name;");
+ }
+
+ @Test
+ public void testSelLeftStarJoin() throws Exception {
+ test("select n.* from cp.`tpch/nation.parquet` n, cp.`tpch/region.parquet` r where n.n_regionkey = r.r_regionkey order by n.n_name;");
+ }
+
+ @Test
+ public void testSelRightStarJoin() throws Exception {
+ test("select r.* from cp.`tpch/nation.parquet` n, cp.`tpch/region.parquet` r where n.n_regionkey = r.r_regionkey order by n.n_name;");
+ }
+
+ @Test
+ public void testSelStarRegColConstJoin() throws Exception {
+ test("select *, n.n_nationkey as n_nationkey0, 1 + 2 as constant from cp.`tpch/nation.parquet` n, cp.`tpch/region.parquet` r where n.n_regionkey = r.r_regionkey order by n.n_name;");
+ }
+
+ @Test
+ public void testSelStarBothSideJoin() throws Exception {
+ test("select n.*, r.* from cp.`tpch/nation.parquet` n, cp.`tpch/region.parquet` r where n.n_regionkey = r.r_regionkey;");
+ }
+
+ @Test
+ public void testSelStarJoinSameColName() throws Exception {
+ test("select * from cp.`tpch/nation.parquet` n1, cp.`tpch/nation.parquet` n2 where n1.n_nationkey = n2.n_nationkey;");
+ }
+
+ @Test // DRILL-1293
+ public void testStarView1() throws Exception {
+ test("use dfs.tmp");
+ test("create view vt1 as select * from cp.`tpch/region.parquet` r, cp.`tpch/nation.parquet` n where r.r_regionkey = n.n_regionkey");
+ test("select * from vt1");
+ test("drop view vt1");
+ }
+
+ @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;");
+ }
+
+ @Test // see DRILL-1811
+ public void testSelStarDifferentColumnOrder() throws Exception {
+ test("select first_name, * from cp.`employee.json`;");
+ test("select *, first_name, *, last_name from cp.`employee.json`;");
+ }
+
+ @Test(expected = RpcException.class) // Should get "At line 1, column 8: Column 'n_nationkey' is ambiguous"
+ public void testSelStarAmbiguousJoin() throws Exception {
+ try {
+ 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 " ) ;
+ } catch (RpcException e) {
+ logger.info("***** Test resulted in expected failure: " + e.getMessage());
+ throw e;
+ }
+ }
+
+ @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; " );
+
+ // 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
+ public void testSelectStartSubQueryJoinWithWhereClause() throws Exception {
+ // 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; " );
+ }
+
+ @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;" );
+ }
+
+}
http://git-wip-us.apache.org/repos/asf/drill/blob/7980a713/pom.xml
----------------------------------------------------------------------
diff --git a/pom.xml b/pom.xml
index 8823afa..00aaa04 100644
--- a/pom.xml
+++ b/pom.xml
@@ -920,7 +920,7 @@
<dependency>
<groupId>net.hydromatic</groupId>
<artifactId>optiq-core</artifactId>
- <version>0.9-drill-r9</version>
+ <version>0.9-drill-r11</version>
<exclusions>
<exclusion>
<groupId>org.jgrapht</groupId>