You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@drill.apache.org by ti...@apache.org on 2018/11/10 03:14:39 UTC
[drill] 01/03: DRILL-3610: Add TIMESTAMPADD and TIMESTAMPDIFF
functions
This is an automated email from the ASF dual-hosted git repository.
timothyfarkas pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/drill.git
commit 683610df1f5f62ad6f6a2a1ea3222b0bf65c767e
Author: Volodymyr Vysotskyi <vv...@gmail.com>
AuthorDate: Wed Nov 7 20:03:40 2018 +0200
DRILL-3610: Add TIMESTAMPADD and TIMESTAMPDIFF functions
closes #1528
---
.../src/main/codegen/data/DateIntervalFunc.tdd | 1 +
.../IntervalNumericArithmetic.java | 20 +-
.../TimestampDiff.java | 109 ++++++++
.../drill/exec/planner/logical/DrillOptiq.java | 274 ++++++++++++---------
.../exec/planner/sql/DrillConvertletTable.java | 35 ++-
.../drill/exec/planner/sql/TypeInferenceUtils.java | 73 +++++-
.../fn/impl/TestTimestampAddDiffFunctions.java | 202 +++++++++++++++
7 files changed, 584 insertions(+), 130 deletions(-)
diff --git a/exec/java-exec/src/main/codegen/data/DateIntervalFunc.tdd b/exec/java-exec/src/main/codegen/data/DateIntervalFunc.tdd
index 96e1607..bfa44c8 100644
--- a/exec/java-exec/src/main/codegen/data/DateIntervalFunc.tdd
+++ b/exec/java-exec/src/main/codegen/data/DateIntervalFunc.tdd
@@ -18,6 +18,7 @@
{intervals: ["Interval", "IntervalDay", "IntervalYear", "Int", "BigInt"] },
{truncInputTypes: ["Date", "TimeStamp", "Time", "Interval", "IntervalDay", "IntervalYear"] },
{truncUnits : ["Second", "Minute", "Hour", "Day", "Month", "Year", "Week", "Quarter", "Decade", "Century", "Millennium" ] },
+ {timestampDiffUnits : ["Nanosecond", "Microsecond", "Second", "Minute", "Hour", "Day", "Month", "Year", "Week", "Quarter"] },
{
varCharToDate: [
diff --git a/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/IntervalNumericArithmetic.java b/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/IntervalNumericArithmetic.java
index 66e754c..de2051a 100644
--- a/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/IntervalNumericArithmetic.java
+++ b/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/IntervalNumericArithmetic.java
@@ -129,20 +129,22 @@ public class ${intervaltype}${numerictype}Functions {
}
}
- @SuppressWarnings("unused")
- @FunctionTemplate(names = {"divide", "div"}, scope = FunctionTemplate.FunctionScope.SIMPLE, nulls=NullHandling.NULL_IF_NULL)
- public static class ${intervaltype}${numerictype}DivideFunction implements DrillSimpleFunc {
+ @SuppressWarnings("unused")
+ @FunctionTemplate(names = {"divide", "div"<#if numerictype == "Int">, "/int"</#if>},
+ scope = FunctionTemplate.FunctionScope.SIMPLE,
+ nulls = NullHandling.NULL_IF_NULL)
+ public static class ${intervaltype}${numerictype}DivideFunction implements DrillSimpleFunc {
@Param ${intervaltype}Holder left;
@Param ${numerictype}Holder right;
@Output IntervalHolder out;
- public void setup() {
- }
+ public void setup() {
+ }
- public void eval() {
- <@intervalNumericArithmeticBlock left="left" right="right" temp = "temp" op = "/" out = "out" intervaltype=intervaltype />
- }
+ public void eval() {
+ <@intervalNumericArithmeticBlock left="left" right="right" temp = "temp" op = "/" out = "out" intervaltype=intervaltype />
}
+ }
}
</#list>
-</#list>
\ No newline at end of file
+</#list>
diff --git a/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/TimestampDiff.java b/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/TimestampDiff.java
new file mode 100644
index 0000000..54232e2
--- /dev/null
+++ b/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/TimestampDiff.java
@@ -0,0 +1,109 @@
+/*
+ * 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.
+ */
+<@pp.dropOutputFile />
+<#assign className="GTimestampDiff"/>
+
+<@pp.changeOutputFile name="/org/apache/drill/exec/expr/fn/impl/${className}.java"/>
+
+<#include "/@includes/license.ftl"/>
+
+package org.apache.drill.exec.expr.fn.impl;
+
+import org.apache.drill.exec.expr.DrillSimpleFunc;
+import org.apache.drill.exec.expr.annotations.FunctionTemplate;
+import org.apache.drill.exec.expr.annotations.FunctionTemplate.NullHandling;
+import org.apache.drill.exec.expr.annotations.Output;
+import org.apache.drill.exec.expr.annotations.Workspace;
+import org.apache.drill.exec.expr.annotations.Param;
+import org.apache.drill.exec.expr.holders.*;
+import org.apache.drill.exec.record.RecordBatch;
+
+/*
+ * This class is generated using freemarker and the ${.template_name} template.
+ */
+
+public class ${className} {
+
+<#list dateIntervalFunc.timestampDiffUnits as unit>
+
+<#list dateIntervalFunc.dates as fromUnit>
+<#list dateIntervalFunc.dates as toUnit>
+
+ @FunctionTemplate(name = "timestampdiff${unit}",
+ scope = FunctionTemplate.FunctionScope.SIMPLE,
+ nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)
+ public static class TimestampDiff${unit}${fromUnit}To${toUnit} implements DrillSimpleFunc {
+
+ @Param ${fromUnit}Holder left;
+ @Param ${toUnit}Holder right;
+ @Output BigIntHolder out;
+
+ public void setup() {
+ }
+
+ public void eval() {
+ <#if unit == "Nanosecond">
+ out.value = (right.value - left.value) * 1000000;
+ <#elseif unit == "Microsecond">
+ out.value = (right.value - left.value) * 1000;
+ <#elseif unit == "Second">
+ out.value = (right.value - left.value) / org.apache.drill.exec.vector.DateUtilities.secondsToMillis;
+ <#elseif unit == "Minute">
+ out.value = (right.value - left.value) / org.apache.drill.exec.vector.DateUtilities.minutesToMillis;
+ <#elseif unit == "Hour">
+ out.value = (right.value - left.value) / org.apache.drill.exec.vector.DateUtilities.hoursToMillis;
+ <#elseif unit == "Day">
+ out.value = (right.value - left.value) / org.apache.drill.exec.vector.DateUtilities.daysToStandardMillis;
+ <#elseif unit == "Week">
+ out.value = (right.value - left.value) / 604800000; // 7 * 24 * 60 * 60 * 1000
+ <#elseif unit == "Month" || unit == "Quarter" || unit == "Year">
+ long timeMilliseconds = left.value % org.apache.drill.exec.vector.DateUtilities.daysToStandardMillis
+ - right.value % org.apache.drill.exec.vector.DateUtilities.daysToStandardMillis;
+
+ java.time.Period between = java.time.Period.between(
+ java.time.Instant.ofEpochMilli(left.value).atZone(java.time.ZoneOffset.UTC).toLocalDate(),
+ java.time.Instant.ofEpochMilli(right.value).atZone(java.time.ZoneOffset.UTC).toLocalDate());
+ int days = between.getDays();
+ if (timeMilliseconds < 0 && days > 0) {
+ // in the case of negative time value increases left operand days value
+ between = java.time.Period.between(
+ java.time.Instant.ofEpochMilli(left.value + org.apache.drill.exec.vector.DateUtilities.daysToStandardMillis).atZone(java.time.ZoneOffset.UTC).toLocalDate(),
+ java.time.Instant.ofEpochMilli(right.value).atZone(java.time.ZoneOffset.UTC).toLocalDate());
+ } else if (timeMilliseconds > 0 && days < 0) {
+ // in the case of negative days value decreases it for the right operand
+ between = java.time.Period.between(
+ java.time.Instant.ofEpochMilli(left.value - org.apache.drill.exec.vector.DateUtilities.daysToStandardMillis).atZone(java.time.ZoneOffset.UTC).toLocalDate(),
+ java.time.Instant.ofEpochMilli(right.value).atZone(java.time.ZoneOffset.UTC).toLocalDate());
+ }
+ int months = between.getMonths() + between.getYears() * org.apache.drill.exec.vector.DateUtilities.yearsToMonths;
+
+ <#if unit == "Month">
+ out.value = months;
+ <#elseif unit == "Quarter">
+ out.value = months / 4;
+ <#elseif unit == "Year">
+ out.value = months / org.apache.drill.exec.vector.DateUtilities.yearsToMonths;
+ </#if>
+ </#if>
+ }
+ }
+</#list>
+</#list>
+
+</#list>
+}
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 477b03c..63ce90f 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
@@ -18,11 +18,14 @@
package org.apache.drill.exec.planner.logical;
import java.math.BigDecimal;
+import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.LinkedList;
import java.util.List;
+import org.apache.calcite.avatica.util.TimeUnit;
import org.apache.calcite.rel.type.RelDataType;
+import org.apache.commons.lang3.StringUtils;
import org.apache.drill.common.exceptions.UserException;
import org.apache.drill.common.expression.ExpressionPosition;
import org.apache.drill.common.expression.FieldReference;
@@ -58,6 +61,7 @@ import org.apache.calcite.sql.SqlSyntax;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.util.NlsString;
+import org.apache.drill.shaded.guava.com.google.common.base.Preconditions;
import org.apache.drill.shaded.guava.com.google.common.collect.Lists;
import org.apache.drill.exec.planner.physical.PlannerSettings;
import org.apache.drill.exec.work.ExecErrorConstants;
@@ -395,7 +399,7 @@ public class DrillOptiq {
}
private LogicalExpression getDrillFunctionFromOptiqCall(RexCall call) {
- List<LogicalExpression> args = Lists.newArrayList();
+ List<LogicalExpression> args = new ArrayList<>();
for(RexNode n : call.getOperands()){
args.add(n.accept(this));
@@ -408,114 +412,158 @@ public class DrillOptiq {
/* Rewrite extract functions in the following manner
* extract(year, date '2008-2-23') ---> extractYear(date '2008-2-23')
*/
- if (functionName.equals("extract")) {
-
- // Assert that the first argument to extract is a QuotedString
- assert args.get(0) instanceof ValueExpressions.QuotedString;
-
- // Get the unit of time to be extracted
- String timeUnitStr = ((ValueExpressions.QuotedString)args.get(0)).value;
-
- switch (timeUnitStr){
- case ("YEAR"):
- case ("MONTH"):
- case ("DAY"):
- case ("HOUR"):
- case ("MINUTE"):
- case ("SECOND"):
- String functionPostfix = timeUnitStr.substring(0, 1).toUpperCase() + timeUnitStr.substring(1).toLowerCase();
- functionName += functionPostfix;
- return FunctionCallFactory.createExpression(functionName, args.subList(1, 2));
- default:
- throw new UnsupportedOperationException("extract function supports the following time units: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND");
+ switch (functionName) {
+ case "extract": {
+
+ // Assert that the first argument to extract is a QuotedString
+ assert args.get(0) instanceof ValueExpressions.QuotedString;
+
+ // Get the unit of time to be extracted
+ String timeUnitStr = ((ValueExpressions.QuotedString) args.get(0)).value;
+
+ TimeUnit timeUnit = TimeUnit.valueOf(timeUnitStr);
+
+ switch (timeUnit) {
+ case YEAR:
+ case MONTH:
+ case DAY:
+ case HOUR:
+ case MINUTE:
+ case SECOND:
+ String functionPostfix = StringUtils.capitalize(timeUnitStr.toLowerCase());
+ functionName += functionPostfix;
+ return FunctionCallFactory.createExpression(functionName, args.subList(1, 2));
+ default:
+ throw new UnsupportedOperationException("extract function supports the following time units: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND");
+ }
}
- } else if (functionName.equals("trim")) {
- String trimFunc = null;
- List<LogicalExpression> trimArgs = Lists.newArrayList();
-
- assert args.get(0) instanceof ValueExpressions.QuotedString;
- switch (((ValueExpressions.QuotedString)args.get(0)).value.toUpperCase()) {
- case "LEADING":
- trimFunc = "ltrim";
- break;
- case "TRAILING":
- trimFunc = "rtrim";
- break;
- case "BOTH":
- trimFunc = "btrim";
- break;
- default:
- assert 1 == 0;
- }
-
- trimArgs.add(args.get(2));
- trimArgs.add(args.get(1));
-
- return FunctionCallFactory.createExpression(trimFunc, trimArgs);
- } else if (functionName.equals("date_part")) {
- // Rewrite DATE_PART functions as extract functions
- // assert that the function has exactly two arguments
- assert argsSize == 2;
-
- /* Based on the first input to the date_part function we rewrite the function as the
- * appropriate extract function. For example
- * date_part('year', date '2008-2-23') ------> extractYear(date '2008-2-23')
- */
- assert args.get(0) instanceof QuotedString;
-
- QuotedString extractString = (QuotedString) args.get(0);
- String functionPostfix = extractString.value.substring(0, 1).toUpperCase() + extractString.value.substring(1).toLowerCase();
- return FunctionCallFactory.createExpression("extract" + functionPostfix, args.subList(1, 2));
- } else if (functionName.equals("concat")) {
-
- if (argsSize == 1) {
- /*
- * We treat concat with one argument as a special case. Since we don't have a function
- * implementation of concat that accepts one argument. We simply add another dummy argument
- * (empty string literal) to the list of arguments.
- */
- List<LogicalExpression> concatArgs = new LinkedList<>(args);
- concatArgs.add(QuotedString.EMPTY_STRING);
-
- return FunctionCallFactory.createExpression(functionName, concatArgs);
+ case "timestampdiff": {
+
+ // Assert that the first argument to extract is a QuotedString
+ Preconditions.checkArgument(args.get(0) instanceof ValueExpressions.QuotedString,
+ "The first argument of TIMESTAMPDIFF function should be QuotedString");
+
+ String timeUnitStr = ((ValueExpressions.QuotedString) args.get(0)).value;
+
+ TimeUnit timeUnit = TimeUnit.valueOf(timeUnitStr);
+
+ switch (timeUnit) {
+ case YEAR:
+ case MONTH:
+ case DAY:
+ case HOUR:
+ case MINUTE:
+ case SECOND:
+ case QUARTER:
+ case WEEK:
+ case MICROSECOND:
+ case NANOSECOND:
+ String functionPostfix = StringUtils.capitalize(timeUnitStr.toLowerCase());
+ functionName += functionPostfix;
+ return FunctionCallFactory.createExpression(functionName, args.subList(1, 3));
+ default:
+ throw new UnsupportedOperationException("TIMESTAMPDIFF function supports the following time units: " +
+ "YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, QUARTER, WEEK, MICROSECOND, NANOSECOND");
+ }
+ }
+ case "trim": {
+ String trimFunc;
+ List<LogicalExpression> trimArgs = new ArrayList<>();
+
+ assert args.get(0) instanceof ValueExpressions.QuotedString;
+ switch (((ValueExpressions.QuotedString) args.get(0)).value.toUpperCase()) {
+ case "LEADING":
+ trimFunc = "ltrim";
+ break;
+ case "TRAILING":
+ trimFunc = "rtrim";
+ break;
+ case "BOTH":
+ trimFunc = "btrim";
+ break;
+ default:
+ throw new UnsupportedOperationException("Invalid argument for TRIM function. " +
+ "Expected one of the following: LEADING, TRAILING, BOTH");
+ }
- } else if (argsSize > 2) {
- List<LogicalExpression> concatArgs = Lists.newArrayList();
+ trimArgs.add(args.get(2));
+ trimArgs.add(args.get(1));
- /* stack concat functions on top of each other if we have more than two arguments
- * Eg: concat(col1, col2, col3) => concat(concat(col1, col2), col3)
+ return FunctionCallFactory.createExpression(trimFunc, trimArgs);
+ }
+ case "date_part": {
+ // Rewrite DATE_PART functions as extract functions
+ // assert that the function has exactly two arguments
+ assert argsSize == 2;
+
+ /* Based on the first input to the date_part function we rewrite the function as the
+ * appropriate extract function. For example
+ * date_part('year', date '2008-2-23') ------> extractYear(date '2008-2-23')
*/
- concatArgs.add(args.get(0));
- concatArgs.add(args.get(1));
-
- LogicalExpression first = FunctionCallFactory.createExpression(functionName, concatArgs);
+ assert args.get(0) instanceof QuotedString;
- for (int i = 2; i < argsSize; i++) {
- concatArgs = Lists.newArrayList();
- concatArgs.add(first);
- concatArgs.add(args.get(i));
- first = FunctionCallFactory.createExpression(functionName, concatArgs);
+ QuotedString extractString = (QuotedString) args.get(0);
+ String functionPostfix = StringUtils.capitalize(extractString.value.toLowerCase());
+ return FunctionCallFactory.createExpression("extract" + functionPostfix, args.subList(1, 2));
+ }
+ case "concat": {
+
+ if (argsSize == 1) {
+ /*
+ * We treat concat with one argument as a special case. Since we don't have a function
+ * implementation of concat that accepts one argument. We simply add another dummy argument
+ * (empty string literal) to the list of arguments.
+ */
+ List<LogicalExpression> concatArgs = new LinkedList<>(args);
+ concatArgs.add(QuotedString.EMPTY_STRING);
+
+ return FunctionCallFactory.createExpression(functionName, concatArgs);
+
+ } else if (argsSize > 2) {
+ List<LogicalExpression> concatArgs = new ArrayList<>();
+
+ /* stack concat functions on top of each other if we have more than two arguments
+ * Eg: concat(col1, col2, col3) => concat(concat(col1, col2), col3)
+ */
+ concatArgs.add(args.get(0));
+ concatArgs.add(args.get(1));
+
+ LogicalExpression first = FunctionCallFactory.createExpression(functionName, concatArgs);
+
+ for (int i = 2; i < argsSize; i++) {
+ concatArgs = new ArrayList<>();
+ concatArgs.add(first);
+ concatArgs.add(args.get(i));
+ first = FunctionCallFactory.createExpression(functionName, concatArgs);
+ }
+
+ return first;
}
-
- return first;
+ break;
}
- } else if (functionName.equals("length")) {
-
+ case "length": {
if (argsSize == 2) {
- // Second argument should always be a literal specifying the encoding format
- assert args.get(1) instanceof ValueExpressions.QuotedString;
+ // Second argument should always be a literal specifying the encoding format
+ assert args.get(1) instanceof ValueExpressions.QuotedString;
- String encodingType = ((ValueExpressions.QuotedString) args.get(1)).value;
- functionName += encodingType.substring(0, 1).toUpperCase() + encodingType.substring(1).toLowerCase();
+ String encodingType = ((ValueExpressions.QuotedString) args.get(1)).value;
+ functionName += StringUtils.capitalize(encodingType.toLowerCase());
- return FunctionCallFactory.createExpression(functionName, args.subList(0, 1));
+ return FunctionCallFactory.createExpression(functionName, args.subList(0, 1));
}
- } else if ((functionName.equals("convert_from") || functionName.equals("convert_to"))
- && args.get(1) instanceof QuotedString) {
- return FunctionCallFactory.createConvert(functionName, ((QuotedString)args.get(1)).value, args.get(0), ExpressionPosition.UNKNOWN);
- } else if (functionName.equals("date_trunc")) {
- return handleDateTruncFunction(args);
+ break;
+ }
+ case "convert_from":
+ case "convert_to": {
+ if (args.get(1) instanceof QuotedString) {
+ return FunctionCallFactory.createConvert(functionName, ((QuotedString) args.get(1)).value, args.get(0), ExpressionPosition.UNKNOWN);
+ }
+ break;
+ }
+ case "date_trunc": {
+ return handleDateTruncFunction(args);
+ }
}
return FunctionCallFactory.createExpression(functionName, args);
@@ -526,21 +574,23 @@ public class DrillOptiq {
assert args.get(0) instanceof ValueExpressions.QuotedString;
// Get the unit of time to be extracted
- String timeUnitStr = ((ValueExpressions.QuotedString)args.get(0)).value.toUpperCase();
-
- switch (timeUnitStr){
- case ("YEAR"):
- case ("MONTH"):
- case ("DAY"):
- case ("HOUR"):
- case ("MINUTE"):
- case ("SECOND"):
- case ("WEEK"):
- case ("QUARTER"):
- case ("DECADE"):
- case ("CENTURY"):
- case ("MILLENNIUM"):
- final String functionPostfix = timeUnitStr.substring(0, 1).toUpperCase() + timeUnitStr.substring(1).toLowerCase();
+ String timeUnitStr = ((ValueExpressions.QuotedString) args.get(0)).value.toUpperCase();
+
+ TimeUnit timeUnit = TimeUnit.valueOf(timeUnitStr);
+
+ switch (timeUnit) {
+ case YEAR:
+ case MONTH:
+ case DAY:
+ case HOUR:
+ case MINUTE:
+ case SECOND:
+ case WEEK:
+ case QUARTER:
+ case DECADE:
+ case CENTURY:
+ case MILLENNIUM:
+ final String functionPostfix = StringUtils.capitalize(timeUnitStr.toLowerCase());
return FunctionCallFactory.createExpression("date_trunc_" + functionPostfix, args.subList(1, 2));
}
diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/DrillConvertletTable.java b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/DrillConvertletTable.java
index 5f3b95e..8a1b194 100644
--- a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/DrillConvertletTable.java
+++ b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/DrillConvertletTable.java
@@ -18,21 +18,29 @@
package org.apache.drill.exec.planner.sql;
import java.util.ArrayList;
+import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
+import org.apache.calcite.avatica.util.TimeUnit;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.sql.SqlBasicCall;
import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlIntervalQualifier;
import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql2rel.SqlRexConvertlet;
import org.apache.calcite.sql2rel.SqlRexConvertletTable;
import org.apache.calcite.sql2rel.StandardConvertletTable;
import org.apache.drill.exec.planner.sql.parser.DrillCalciteWrapperUtility;
-public class DrillConvertletTable implements SqlRexConvertletTable{
+public class DrillConvertletTable implements SqlRexConvertletTable {
public static HashMap<SqlOperator, SqlRexConvertlet> map = new HashMap<>();
@@ -61,6 +69,30 @@ public class DrillConvertletTable implements SqlRexConvertletTable{
}
};
+ // Custom convertlet to avoid rewriting TIMESTAMP_DIFF by Calcite,
+ // since Drill does not support Reinterpret function and does not handle
+ // all Calcite interval representations correctly.
+ private static final SqlRexConvertlet TIMESTAMP_DIFF_CONVERTLET = (cx, call) -> {
+ SqlLiteral unitLiteral = call.operand(0);
+ SqlIntervalQualifier qualifier =
+ new SqlIntervalQualifier(unitLiteral.symbolValue(TimeUnit.class), null, SqlParserPos.ZERO);
+
+ List<RexNode> operands = Arrays.asList(
+ cx.convertExpression(qualifier),
+ cx.convertExpression(call.operand(1)),
+ cx.convertExpression(call.operand(2)));
+
+ RelDataTypeFactory typeFactory = cx.getTypeFactory();
+
+ RelDataType returnType = typeFactory.createTypeWithNullability(
+ typeFactory.createSqlType(SqlTypeName.BIGINT),
+ cx.getValidator().getValidatedNodeType(call.operand(1)).isNullable()
+ || cx.getValidator().getValidatedNodeType(call.operand(2)).isNullable());
+
+ return cx.getRexBuilder().makeCall(returnType,
+ SqlStdOperatorTable.TIMESTAMP_DIFF, operands);
+ };
+
static {
// Use custom convertlet for EXTRACT function
map.put(SqlStdOperatorTable.EXTRACT, DrillExtractConvertlet.INSTANCE);
@@ -68,6 +100,7 @@ public class DrillConvertletTable implements SqlRexConvertletTable{
// which is not suitable for Infinity value case
map.put(SqlStdOperatorTable.SQRT, SQRT_CONVERTLET);
map.put(SqlStdOperatorTable.COALESCE, COALESCE_CONVERTLET);
+ map.put(SqlStdOperatorTable.TIMESTAMP_DIFF, TIMESTAMP_DIFF_CONVERTLET);
map.put(SqlStdOperatorTable.AVG, new DrillAvgVarianceConvertlet(SqlKind.AVG));
map.put(SqlStdOperatorTable.STDDEV_POP, new DrillAvgVarianceConvertlet(SqlKind.STDDEV_POP));
map.put(SqlStdOperatorTable.STDDEV_SAMP, new DrillAvgVarianceConvertlet(SqlKind.STDDEV_SAMP));
diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/TypeInferenceUtils.java b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/TypeInferenceUtils.java
index 016473c..3319104 100644
--- a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/TypeInferenceUtils.java
+++ b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/TypeInferenceUtils.java
@@ -17,6 +17,7 @@
*/
package org.apache.drill.exec.planner.sql;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.drill.shaded.guava.com.google.common.collect.ImmutableMap;
import org.apache.drill.shaded.guava.com.google.common.collect.ImmutableSet;
import org.apache.drill.shaded.guava.com.google.common.collect.Lists;
@@ -140,6 +141,7 @@ public class TypeInferenceUtils {
private static final ImmutableMap<String, SqlReturnTypeInference> funcNameToInference = ImmutableMap.<String, SqlReturnTypeInference> builder()
.put("DATE_PART", DrillDatePartSqlReturnTypeInference.INSTANCE)
+ .put(SqlStdOperatorTable.TIMESTAMP_ADD.getName(), DrillTimestampAddTypeInference.INSTANCE)
.put(SqlKind.SUM.name(), DrillSumSqlReturnTypeInference.INSTANCE)
.put(SqlKind.COUNT.name(), DrillCountSqlReturnTypeInference.INSTANCE)
.put("CONCAT", DrillConcatSqlReturnTypeInference.INSTANCE_CONCAT)
@@ -555,6 +557,60 @@ public class TypeInferenceUtils {
}
}
+ private static class DrillTimestampAddTypeInference implements SqlReturnTypeInference {
+ private static final SqlReturnTypeInference INSTANCE = new DrillTimestampAddTypeInference();
+
+ @Override
+ public RelDataType inferReturnType(SqlOperatorBinding opBinding) {
+ RelDataTypeFactory factory = opBinding.getTypeFactory();
+ // operands count ond order is checked at parsing stage
+ RelDataType inputType = opBinding.getOperandType(2);
+ boolean isNullable = inputType.isNullable() || opBinding.getOperandType(1).isNullable();
+
+ SqlTypeName inputTypeName = inputType.getSqlTypeName();
+
+ TimeUnit qualifier = ((SqlLiteral) ((SqlCallBinding) opBinding).operand(0)).getValueAs(TimeUnit.class);
+
+ SqlTypeName sqlTypeName;
+
+ // follow up with type inference of reduced expression
+ switch (qualifier) {
+ case DAY:
+ case WEEK:
+ case MONTH:
+ case QUARTER:
+ case YEAR:
+ case NANOSECOND: // NANOSECOND is not supported by Calcite SqlTimestampAddFunction.
+ // Once it is fixed, NANOSECOND should be moved to the group below.
+ sqlTypeName = inputTypeName;
+ break;
+ case MICROSECOND:
+ case MILLISECOND:
+ // precision should be specified for MICROSECOND and MILLISECOND
+ return factory.createTypeWithNullability(
+ factory.createSqlType(SqlTypeName.TIMESTAMP, 3),
+ isNullable);
+ case SECOND:
+ case MINUTE:
+ case HOUR:
+ sqlTypeName = SqlTypeName.TIMESTAMP;
+ break;
+ default:
+ sqlTypeName = SqlTypeName.ANY;
+ }
+
+ // preserves precision of input type if it was specified
+ if (inputType.getSqlTypeName().allowsPrecNoScale()) {
+ RelDataType type = factory.createSqlType(sqlTypeName, inputType.getPrecision());
+ return factory.createTypeWithNullability(type, isNullable);
+ }
+ return createCalciteTypeWithNullability(
+ opBinding.getTypeFactory(),
+ sqlTypeName,
+ isNullable);
+ }
+ }
+
private static class DrillSubstringSqlReturnTypeInference implements SqlReturnTypeInference {
private static final DrillSubstringSqlReturnTypeInference INSTANCE = new DrillSubstringSqlReturnTypeInference();
@@ -823,15 +879,16 @@ public class TypeInferenceUtils {
/**
* For Extract and date_part functions, infer the return types based on timeUnit
*/
- public static SqlTypeName getSqlTypeNameForTimeUnit(String timeUnit) {
- switch (timeUnit.toUpperCase()){
- case "YEAR":
- case "MONTH":
- case "DAY":
- case "HOUR":
- case "MINUTE":
+ public static SqlTypeName getSqlTypeNameForTimeUnit(String timeUnitStr) {
+ TimeUnit timeUnit = TimeUnit.valueOf(timeUnitStr);
+ switch (timeUnit) {
+ case YEAR:
+ case MONTH:
+ case DAY:
+ case HOUR:
+ case MINUTE:
return SqlTypeName.BIGINT;
- case "SECOND":
+ case SECOND:
return SqlTypeName.DOUBLE;
default:
throw UserException
diff --git a/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestTimestampAddDiffFunctions.java b/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestTimestampAddDiffFunctions.java
new file mode 100644
index 0000000..4b3e93b
--- /dev/null
+++ b/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestTimestampAddDiffFunctions.java
@@ -0,0 +1,202 @@
+/*
+ * 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.exec.fn.impl;
+
+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 java.time.LocalDateTime;
+import java.time.LocalTime;
+import java.util.Arrays;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+public class TestTimestampAddDiffFunctions extends ClusterTest {
+
+ private final List<String> QUALIFIERS = Arrays.asList(
+ "FRAC_SECOND",
+ "MICROSECOND",
+ "NANOSECOND",
+ "SQL_TSI_FRAC_SECOND",
+ "SQL_TSI_MICROSECOND",
+ "SECOND",
+ "SQL_TSI_SECOND",
+ "MINUTE",
+ "SQL_TSI_MINUTE",
+ "HOUR",
+ "SQL_TSI_HOUR",
+ "DAY",
+ "SQL_TSI_DAY",
+ "WEEK",
+ "SQL_TSI_WEEK",
+ "MONTH",
+ "SQL_TSI_MONTH",
+ "QUARTER",
+ "SQL_TSI_QUARTER",
+ "YEAR",
+ "SQL_TSI_YEAR");
+
+ @BeforeClass
+ public static void setup() throws Exception {
+ ClusterFixtureBuilder builder = ClusterFixture.builder(dirTestWatcher);
+ startCluster(builder);
+ }
+
+ @Test // DRILL-3610
+ public void testTimestampAddDiffLiteralTypeInference() throws Exception {
+ Map<String, String> dateTypes = new HashMap<>();
+ dateTypes.put("DATE", "2013-03-31");
+ dateTypes.put("TIME", "00:02:03.123");
+ dateTypes.put("TIMESTAMP", "2013-03-31 00:02:03");
+
+ for (String qualifier : QUALIFIERS) {
+ for (Map.Entry<String, String> typeResultPair : dateTypes.entrySet()) {
+ String dateTimeLiteral = typeResultPair.getValue();
+ String type = typeResultPair.getKey();
+
+ client.queryBuilder()
+ .sql("SELECT TIMESTAMPADD(%s, 0, CAST('%s' AS %s)) col1",
+ qualifier, dateTimeLiteral, type)
+ .run();
+
+ // TIMESTAMPDIFF with args of different types
+ for (Map.Entry<String, String> secondArg : dateTypes.entrySet()) {
+ client.queryBuilder()
+ .sql("SELECT TIMESTAMPDIFF(%s, CAST('%s' AS %s), CAST('%s' AS %s)) col1",
+ qualifier, dateTimeLiteral, type, secondArg.getValue(), secondArg.getKey())
+ .run();
+ }
+ }
+ }
+ }
+
+ @Test // DRILL-3610
+ public void testTimestampAddDiffTypeInference() throws Exception {
+ for (String qualifier : QUALIFIERS) {
+ client.queryBuilder()
+ .sql(
+ "SELECT TIMESTAMPADD(%1$s, 0, `date`) col1," +
+ "TIMESTAMPADD(%1$s, 0, `time`) timeReq," +
+ "TIMESTAMPADD(%1$s, 0, `timestamp`) timestampReq," +
+ "TIMESTAMPADD(%1$s, 0, t.time_map.`date`) dateOpt," +
+ "TIMESTAMPADD(%1$s, 0, t.time_map.`time`) timeOpt," +
+ "TIMESTAMPADD(%1$s, 0, t.time_map.`timestamp`) timestampOpt\n" +
+ "FROM cp.`datetime.parquet` t", qualifier)
+ .run();
+
+ client.queryBuilder()
+ .sql(
+ "SELECT TIMESTAMPDIFF(%1$s, `date`, `date`) col1," +
+ "TIMESTAMPDIFF(%1$s, `time`, `time`) timeReq," +
+ "TIMESTAMPDIFF(%1$s, `timestamp`, `timestamp`) timestampReq," +
+ "TIMESTAMPDIFF(%1$s, `timestamp`, t.time_map.`date`) timestampReqTimestampOpt," +
+ "TIMESTAMPDIFF(%1$s, `timestamp`, t.time_map.`timestamp`) timestampReqTimestampOpt," +
+ "TIMESTAMPDIFF(%1$s, `date`, `time`) timeDate," +
+ "TIMESTAMPDIFF(%1$s, `time`, `date`) Datetime," +
+ "TIMESTAMPDIFF(%1$s, t.time_map.`date`, t.time_map.`date`) dateOpt," +
+ "TIMESTAMPDIFF(%1$s, t.time_map.`time`, t.time_map.`time`) timeOpt," +
+ "TIMESTAMPDIFF(%1$s, t.time_map.`timestamp`, t.time_map.`timestamp`) timestampOpt\n" +
+ "FROM cp.`datetime.parquet` t", qualifier)
+ .run();
+ }
+ }
+
+ @Test // DRILL-3610
+ public void testTimestampAddParquet() throws Exception {
+ String query =
+ "SELECT TIMESTAMPADD(SECOND, 1, `date`) dateReq," +
+ "TIMESTAMPADD(QUARTER, 1, `time`) timeReq," +
+ "TIMESTAMPADD(DAY, 1, `timestamp`) timestampReq," +
+ "TIMESTAMPADD(MONTH, 1, t.time_map.`date`) dateOpt," +
+ "TIMESTAMPADD(HOUR, 1, t.time_map.`time`) timeOpt," +
+ "TIMESTAMPADD(YEAR, 1, t.time_map.`timestamp`) timestampOpt\n" +
+ "FROM cp.`datetime.parquet` t";
+
+ client.testBuilder()
+ .sqlQuery(query)
+ .unOrdered()
+ .baselineColumns("dateReq", "timeReq", "timestampReq", "dateOpt", "timeOpt", "timestampOpt")
+ .baselineValues(
+ LocalDateTime.parse("1970-01-11T00:00:01"), LocalTime.parse("00:00:03.600"), LocalDateTime.parse("2018-03-24T17:40:52.123"),
+ LocalDateTime.parse("1970-02-11T00:00"), LocalTime.parse("01:00:03.600"), LocalDateTime.parse("2019-03-23T17:40:52.123"))
+ .go();
+ }
+
+ @Test // DRILL-3610
+ public void testTimestampDiffParquet() throws Exception {
+ String query =
+ "SELECT TIMESTAMPDIFF(SECOND, DATE '1970-01-15', `date`) dateReq," +
+ "TIMESTAMPDIFF(QUARTER, TIME '12:00:03.600', `time`) timeReq," +
+ "TIMESTAMPDIFF(DAY, TIMESTAMP '2018-03-24 17:40:52.123', `timestamp`) timestampReq," +
+ "TIMESTAMPDIFF(MONTH, DATE '1971-10-30', t.time_map.`date`) dateOpt," +
+ "TIMESTAMPDIFF(HOUR, TIME '18:00:03.600', t.time_map.`time`) timeOpt," +
+ "TIMESTAMPDIFF(YEAR, TIMESTAMP '2020-03-24 17:40:52.123', t.time_map.`timestamp`) timestampOpt\n" +
+ "FROM cp.`datetime.parquet` t";
+
+ client.testBuilder()
+ .sqlQuery(query)
+ .unOrdered()
+ .baselineColumns("dateReq", "timeReq", "timestampReq", "dateOpt", "timeOpt", "timestampOpt")
+ .baselineValues(-345600L, 0L, -1L, -21L, -18L, -2L)
+ .go();
+ }
+
+ @Test // DRILL-3610
+ public void testTimestampAddDiffNull() throws Exception {
+ String query =
+ "SELECT TIMESTAMPDIFF(SECOND, DATE '1970-01-15', a) col1," +
+ "TIMESTAMPDIFF(QUARTER, a, DATE '1970-01-15') col2," +
+ "TIMESTAMPDIFF(DAY, a, a) col3," +
+ "TIMESTAMPADD(MONTH, 1, a) col4," +
+ "TIMESTAMPADD(MONTH, b, DATE '1970-01-15') col5," +
+ "TIMESTAMPADD(MONTH, b, a) col6\n" +
+ "FROM" +
+ "(SELECT CASE WHEN FALSE THEN TIME '12:00:03.600' ELSE null END AS a," +
+ "CASE WHEN FALSE THEN 2 ELSE null END AS b)";
+
+ client.testBuilder()
+ .sqlQuery(query)
+ .unOrdered()
+ .baselineColumns("col1", "col2", "col3", "col4", "col5", "col6")
+ .baselineValues(null, null, null, null, null, null)
+ .go();
+ }
+
+ @Test // DRILL-3610
+ public void testTimestampDiffTimeDateTransition() throws Exception {
+ String query =
+ "SELECT TIMESTAMPDIFF(SECOND, time '12:30:00.123', time '12:30:00') col1," +
+ "TIMESTAMPDIFF(DAY, TIMESTAMP '1970-01-15 15:30:00', TIMESTAMP '1970-01-16 12:30:00') col2," +
+ "TIMESTAMPDIFF(DAY, TIMESTAMP '1970-01-16 12:30:00', TIMESTAMP '1970-01-15 15:30:00') col3," +
+ "TIMESTAMPDIFF(MONTH, TIMESTAMP '1970-01-16 12:30:00', TIMESTAMP '1970-03-15 15:30:00') col4," +
+ "TIMESTAMPDIFF(MONTH, TIMESTAMP '1970-03-15 15:30:00', TIMESTAMP '1970-01-16 12:30:00') col5," +
+ "TIMESTAMPDIFF(DAY, DATE '2012-01-01', DATE '2013-01-01') col6," +
+ "TIMESTAMPDIFF(DAY, DATE '2013-01-01', DATE '2014-01-01') col7";
+
+ client.testBuilder()
+ .sqlQuery(query)
+ .unOrdered()
+ .baselineColumns("col1", "col2", "col3", "col4", "col5", "col6", "col7")
+ .baselineValues(0L, 0L, 0L, 1L, -1L, 366L, 365L)
+ .go();
+ }
+}