sohami closed pull request #1461: DRILL-1248: Allow positional / named aliases
in group by / having clauses
URL: https://github.com/apache/drill/pull/1461
This is a PR merged from a forked repository.
As GitHub hides the original diff on merge, it is displayed below for
the sake of provenance:
As this is a foreign pull request (from a fork), the diff is supplied
below (as it won't show otherwise due to GitHub magic):
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 e6efeb92d1b..4a6aefc86b4 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.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 DrillConformance(SqlConformanceEnum flavor) {
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 d9564b7d606..f1f74a683b8 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 void testAggGroupByWithNullDecimal() throws
Exception {
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 void testAggGroupByWithNullDecimal() throws
Exception {
"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 4af1a84b84d..f058bd7aaa5 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 5bb908fa692..a6e1f41963f 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>
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
With regards,
Apache Git Services
