You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@drill.apache.org by so...@apache.org on 2018/09/14 22:45:12 UTC
[drill] 04/04: DRILL-1248: Allow positional / named aliases in
group by / having clauses
This is an automated email from the ASF dual-hosted git repository.
sorabh pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/drill.git
commit b28355c0581529168a5962d3f41242166033a01c
Author: Arina Ielchiieva <ar...@gmail.com>
AuthorDate: Thu Aug 30 13:46:59 2018 +0300
DRILL-1248: Allow positional / named aliases in group by / having clauses
---
.../drill/exec/planner/sql/DrillConformance.java | 25 ++++++--
.../drill/exec/fn/impl/TestAggregateFunctions.java | 4 +-
.../org/apache/drill/exec/sql/TestConformance.java | 68 +++++++++++++++++++---
pom.xml | 2 +-
4 files changed, 84 insertions(+), 15 deletions(-)
diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/DrillConformance.java b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/DrillConformance.java
index e6efeb9..4a6aefc 100644
--- a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/DrillConformance.java
+++ b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/DrillConformance.java
@@ -21,10 +21,7 @@ import org.apache.calcite.sql.validate.SqlConformanceEnum;
import org.apache.calcite.sql.validate.SqlDelegatingConformance;
/**
- * Drill's SQL conformance is SqlConformanceEnum.DEFAULT except for method isApplyAllowed().
- * Since Drill is going to allow OUTER APPLY and CROSS APPLY to allow each row from left child of Join
- * to join with output of right side (sub-query or table function that will be invoked for each row).
- * Refer to DRILL-5999 for more information.
+ * Drill's SQL conformance is SqlConformanceEnum.DEFAULT with a couple of deviations.
*/
public class DrillConformance extends SqlDelegatingConformance {
@@ -36,8 +33,28 @@ public class DrillConformance extends SqlDelegatingConformance {
super(flavor);
}
+ /**
+ * Drill allows OUTER APPLY and CROSS APPLY to allow each row from left child of Join
+ * to join with output of right side (sub-query or table function that will be invoked for each row).
+ * Refer to DRILL-5999 for more information.
+ */
@Override
public boolean isApplyAllowed() {
return true;
}
+
+ @Override
+ public boolean isGroupByOrdinal() {
+ return true;
+ }
+
+ @Override
+ public boolean isGroupByAlias() {
+ return true;
+ }
+
+ @Override
+ public boolean isHavingAlias() {
+ return true;
+ }
}
diff --git a/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestAggregateFunctions.java b/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestAggregateFunctions.java
index d9564b7..f1f74a6 100644
--- a/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestAggregateFunctions.java
+++ b/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestAggregateFunctions.java
@@ -447,7 +447,7 @@ public class TestAggregateFunctions extends BaseTestQuery {
alterSession(PlannerSettings.ENABLE_DECIMAL_DATA_TYPE_KEY, true);
testBuilder()
.sqlQuery("select sum(cast(a as decimal(9,0))) as s,\n" +
- "avg(cast(a as decimal(9,0))) as a,\n" +
+ "avg(cast(a as decimal(9,0))) as av,\n" +
"var_samp(cast(a as decimal(9,0))) as varSamp,\n" +
"var_pop(cast(a as decimal(9,0))) as varPop,\n" +
"stddev_pop(cast(a as decimal(9,0))) as stddevPop,\n" +
@@ -455,7 +455,7 @@ public class TestAggregateFunctions extends BaseTestQuery {
"max(cast(a as decimal(9,0))) as mx," +
"min(cast(a as decimal(9,0))) as mn from dfs.`%s` t group by a", fileName)
.unOrdered()
- .baselineColumns("s", "a", "varSamp", "varPop", "stddevPop", "stddevSamp", "mx", "mn")
+ .baselineColumns("s", "av", "varSamp", "varPop", "stddevPop", "stddevSamp", "mx", "mn")
.baselineValues(BigDecimal.valueOf(1), new BigDecimal("1.000000"), new BigDecimal("0.000000"),
new BigDecimal("0.000000"), new BigDecimal("0.000000"), new BigDecimal("0.000000"),
BigDecimal.valueOf(1), BigDecimal.valueOf(1))
diff --git a/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestConformance.java b/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestConformance.java
index 4af1a84..f058bd7 100644
--- a/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestConformance.java
+++ b/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestConformance.java
@@ -17,30 +17,82 @@
*/
package org.apache.drill.exec.sql;
-import org.apache.drill.PlanTestBase;
import org.apache.drill.categories.SqlTest;
-import org.apache.drill.test.BaseTestQuery;
+import org.apache.drill.test.ClusterFixture;
+import org.apache.drill.test.ClusterFixtureBuilder;
+import org.apache.drill.test.ClusterTest;
+import org.junit.BeforeClass;
import org.junit.Test;
import org.junit.experimental.categories.Category;
+import static org.junit.Assert.assertTrue;
+
@Category(SqlTest.class)
-public class TestConformance extends BaseTestQuery {
+public class TestConformance extends ClusterTest {
+
+ @BeforeClass
+ public static void setup() throws Exception {
+ ClusterFixtureBuilder builder = ClusterFixture.builder(dirTestWatcher);
+ startCluster(builder);
+ }
@Test
public void testApply() throws Exception{
-
//cross join is not support yet in Drill: DRILL-1921, so we are testing OUTER APPLY only
String query = "SELECT c.c_nationkey, o.orderdate from " +
"cp.`tpch/customer.parquet` c outer apply " +
"cp.`tpch/orders.parquet` o " +
"where c.c_custkey = o.o_custkey";
- PlanTestBase.testPlanMatchingPatterns(query,
- new String[] {"Join"}, new String[] {}
- );
+ String plan = queryBuilder().sql(query).explainText();
+ assertTrue(plan.contains("Join(condition="));
+ }
- return;
+ @Test
+ public void testGroupByWithPositionalAlias() throws Exception {
+ testBuilder()
+ .sqlQuery("select length(n_name), n_regionkey from cp.`tpch/nation.parquet` group by 1, 2")
+ .unOrdered()
+ .sqlBaselineQuery("select length(n_name), n_regionkey from cp.`tpch/nation.parquet` group by length(n_name), n_regionkey")
+ .go();
+ }
+
+ @Test
+ public void testGroupByWithNamedAlias() throws Exception {
+ testBuilder()
+ .sqlQuery("select length(n_name) as len, n_regionkey as key from cp.`tpch/nation.parquet` group by len, key")
+ .unOrdered()
+ .sqlBaselineQuery("select length(n_name) as len, n_regionkey as key from cp.`tpch/nation.parquet` group by length(n_name), n_regionkey")
+ .go();
}
+ @Test
+ public void testHavingWithNamedAlias() throws Exception {
+ testBuilder()
+ .sqlQuery("select length(n_name) as len, count(*) as cnt from cp.`tpch/nation.parquet` " +
+ "group by length(n_name) having cnt > 1")
+ .unOrdered()
+ .sqlBaselineQuery("select length(n_name) as len, count(*) as cnt from cp.`tpch/nation.parquet` " +
+ "group by length(n_name) having count(*) > 1")
+ .go();
+ }
+
+ @Test
+ public void testOrderWithPositionalAlias() throws Exception {
+ testBuilder()
+ .sqlQuery("select n_regionkey, n_name from cp.`tpch/nation.parquet` order by 1, 2")
+ .unOrdered()
+ .sqlBaselineQuery("select n_regionkey, n_name from cp.`tpch/nation.parquet` order by n_regionkey, n_name")
+ .go();
+ }
+
+ @Test
+ public void testOrderWithNamedAlias() throws Exception {
+ testBuilder()
+ .sqlQuery("select n_regionkey as r, n_name as n from cp.`tpch/nation.parquet` order by r, n")
+ .unOrdered()
+ .sqlBaselineQuery("select n_regionkey as r, n_name as n from cp.`tpch/nation.parquet` order by n_regionkey, n_name")
+ .go();
+ }
}
diff --git a/pom.xml b/pom.xml
index 5bb908f..a6e1f41 100644
--- a/pom.xml
+++ b/pom.xml
@@ -46,7 +46,7 @@
<guava.version>19.0</guava.version>
<forkCount>2</forkCount>
<parquet.version>1.10.0</parquet.version>
- <calcite.version>1.17.0-drill-r0</calcite.version>
+ <calcite.version>1.17.0-drill-r1</calcite.version>
<avatica.version>1.12.0</avatica.version>
<janino.version>2.7.6</janino.version>
<sqlline.version>1.1.9-drill-r7</sqlline.version>