Return-Path: X-Original-To: apmail-drill-commits-archive@www.apache.org Delivered-To: apmail-drill-commits-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 3E82518C61 for ; Tue, 29 Mar 2016 01:47:38 +0000 (UTC) Received: (qmail 1363 invoked by uid 500); 29 Mar 2016 01:47:38 -0000 Delivered-To: apmail-drill-commits-archive@drill.apache.org Received: (qmail 1328 invoked by uid 500); 29 Mar 2016 01:47:38 -0000 Mailing-List: contact commits-help@drill.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: commits@drill.apache.org Delivered-To: mailing list commits@drill.apache.org Received: (qmail 1319 invoked by uid 99); 29 Mar 2016 01:47:37 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 29 Mar 2016 01:47:37 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id C666BDFC73; Tue, 29 Mar 2016 01:47:37 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: venki@apache.org To: commits@drill.apache.org Message-Id: X-Mailer: ASF-Git Admin Mailer Subject: drill git commit: DRILL-4549: Add support for more truncation units in date_trunc function Date: Tue, 29 Mar 2016 01:47:37 +0000 (UTC) Repository: drill Updated Branches: refs/heads/master 7e6de2bbc -> 4442e162a DRILL-4549: Add support for more truncation units in date_trunc function Project: http://git-wip-us.apache.org/repos/asf/drill/repo Commit: http://git-wip-us.apache.org/repos/asf/drill/commit/4442e162 Tree: http://git-wip-us.apache.org/repos/asf/drill/tree/4442e162 Diff: http://git-wip-us.apache.org/repos/asf/drill/diff/4442e162 Branch: refs/heads/master Commit: 4442e162a0c9d92281e52f66319abf6eba1dde97 Parents: 7e6de2b Author: vkorukanti Authored: Mon Mar 28 11:09:34 2016 -0700 Committer: vkorukanti Committed: Mon Mar 28 17:54:18 2016 -0700 ---------------------------------------------------------------------- .../src/main/codegen/data/DateIntervalFunc.tdd | 4 +- .../DateTruncFunctions.java | 271 +++++++++++++-- .../drill/exec/planner/logical/DrillOptiq.java | 31 ++ .../org/apache/drill/TestFunctionsQuery.java | 34 +- .../exec/fn/impl/TestDateTruncFunctions.java | 340 +++++++++++++++++++ 5 files changed, 621 insertions(+), 59 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/drill/blob/4442e162/exec/java-exec/src/main/codegen/data/DateIntervalFunc.tdd ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/main/codegen/data/DateIntervalFunc.tdd b/exec/java-exec/src/main/codegen/data/DateIntervalFunc.tdd index 8b842c3..9f1ae37 100644 --- a/exec/java-exec/src/main/codegen/data/DateIntervalFunc.tdd +++ b/exec/java-exec/src/main/codegen/data/DateIntervalFunc.tdd @@ -15,5 +15,7 @@ # limitations under the License. { {dates: ["Date", "TimeStamp", "Time"] }, - {intervals: ["Interval", "IntervalDay", "IntervalYear", "Int", "BigInt"] } + {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" ] }, } http://git-wip-us.apache.org/repos/asf/drill/blob/4442e162/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/DateTruncFunctions.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/DateTruncFunctions.java b/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/DateTruncFunctions.java index 5ffff24..ad2f4c8 100644 --- a/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/DateTruncFunctions.java +++ b/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/DateTruncFunctions.java @@ -39,46 +39,259 @@ import io.netty.buffer.ByteBuf; public class GDateTimeTruncateFunctions { -<#list dateIntervalFunc.dates as type> - -@SuppressWarnings("unused") -@FunctionTemplate(names = "date_trunc", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) -public static class G${type}DateTrunc implements DrillSimpleFunc { +<#list dateIntervalFunc.truncInputTypes as type> <#-- Start InputType Loop --> + /** + * This class merely act as a placeholder so that Calcite allows 'trunc('truncationUnit', col)' + * function in SQL. + */ + @SuppressWarnings("unused") + @FunctionTemplate(name = "date_trunc", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) + public static class G${type}DummyDateTrunc implements DrillSimpleFunc { @Param VarCharHolder left; @Param ${type}Holder right; @Output ${type}Holder out; + + @Override + public void setup() { } + + @Override + public void eval() { } + } + <#list dateIntervalFunc.truncUnits as toUnit> <#-- Start UnitType Loop --> + + <#-- Filter out unsupported combinations --> + <#if !(type == "Time" && toUnit == "Week") && + !(type == "Interval" && toUnit == "Week") && + !(type == "IntervalDay" && toUnit == "Week") && + !(type == "IntervalYear" && toUnit == "Week") + > <#-- Truncate by Week not supported for Time input type --> + + @SuppressWarnings("unused") + @FunctionTemplate(names = "date_trunc_${toUnit}", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) + public static class G${type}${toUnit}DateTrunc implements DrillSimpleFunc { + @Param ${type}Holder right; + @Output ${type}Holder out; @Workspace org.joda.time.MutableDateTime dateTime; + <#if toUnit == "Quarter" || toUnit == "Decade" || toUnit == "Century" || toUnit == "Millennium"> + @Workspace org.joda.time.MutableDateTime dateTime2; + public void setup() { dateTime = new org.joda.time.MutableDateTime(org.joda.time.DateTimeZone.UTC); + <#if toUnit == "Quarter" || toUnit == "Decade" || toUnit == "Century" || toUnit == "Millennium"> + dateTime2 = new org.joda.time.MutableDateTime(org.joda.time.DateTimeZone.UTC); + } public void eval() { - dateTime.setMillis(right.value); - <#if type != "Time"> - if (org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(left.start, left.end, left.buffer).equalsIgnoreCase("YEAR")) dateTime.setRounding(dateTime.getChronology().year()); - else if (org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(left.start, left.end, left.buffer).equalsIgnoreCase("MONTH")) dateTime.setRounding(dateTime.getChronology().monthOfYear()); - else if (org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(left.start, left.end, left.buffer).equalsIgnoreCase("DAY")) dateTime.setRounding(dateTime.getChronology().dayOfMonth()); - else - - <#if type != "Date"> - if (org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(left.start, left.end, left.buffer).equalsIgnoreCase("HOUR")) dateTime.setRounding(dateTime.getChronology().hourOfDay()); - else if (org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(left.start, left.end, left.buffer).equalsIgnoreCase("MINUTE")) dateTime.setRounding(dateTime.getChronology().minuteOfHour()); - else if (org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(left.start, left.end, left.buffer).equalsIgnoreCase("SECOND")) dateTime.setRounding(dateTime.getChronology().secondOfMinute()); - else - - <#if type == "TimeStamp"> - throw new UnsupportedOperationException("date_trunc function supports the following time units for TimeStamp: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND"); - out.value = dateTime.getMillis(); - <#elseif type == "Date"> - throw new UnsupportedOperationException("date_trunc function supports the following time units for Date: YEAR, MONTH, DAY"); - out.value = dateTime.getMillis(); - <#elseif type == "Time"> - throw new UnsupportedOperationException("date_trunc function supports the following time units for Time: HOUR, MINUTE, SECOND"); - out.value = (int) dateTime.getMillis(); - + <#if type == "Time"> <#-- Start InputType --> + <#if toUnit == "Hour"> <#-- Start UnitType --> + dateTime.setMillis(right.value); + dateTime.setRounding(dateTime.getChronology().hourOfDay()); + out.value = (int) dateTime.getMillis(); + <#elseif toUnit == "Minute"> + dateTime.setMillis(right.value); + dateTime.setRounding(dateTime.getChronology().minuteOfHour()); + out.value = (int) dateTime.getMillis(); + <#elseif toUnit == "Second"> + dateTime.setMillis(right.value); + dateTime.setRounding(dateTime.getChronology().secondOfMinute()); + out.value = (int) dateTime.getMillis(); + <#else> + <#-- For all other units truncate the whole thing --> + out.value = 0; + <#-- End UnitType --> + <#elseif type == "Date"> + <#if toUnit == "Second" || toUnit == "Minute" || toUnit == "Hour" || toUnit == "Day"> <#-- Start UnitType --> + // No truncation as there is no time part in date + out.value = right.value; + <#elseif toUnit == "Year"> + dateTime.setMillis(right.value); + dateTime.setRounding(dateTime.getChronology().year()); + out.value = dateTime.getMillis(); + <#elseif toUnit == "Month"> + dateTime.setMillis(right.value); + dateTime.setRounding(dateTime.getChronology().monthOfYear()); + out.value = dateTime.getMillis(); + <#elseif toUnit == "Week"> + dateTime.setMillis(right.value); + dateTime.setRounding(dateTime.getChronology().weekOfWeekyear()); + out.value = dateTime.getMillis(); + <#elseif toUnit == "Quarter"> + dateTime.setMillis(right.value); + int month = dateTime.getMonthOfYear(); + dateTime.setRounding(dateTime.getChronology().year()); + dateTime2.setMillis(dateTime.getMillis()); + dateTime2.add(org.joda.time.DurationFieldType.months(), ((month-1)/3)*3); + out.value = dateTime2.getMillis(); + <#elseif toUnit == "Decade"> + dateTime.setMillis(right.value); + int year = dateTime.getYear(); + dateTime.setRounding(dateTime.getChronology().centuryOfEra()); + dateTime2.setMillis(dateTime.getMillis()); + dateTime2.add(org.joda.time.DurationFieldType.years(), ((year%100)/10)*10); + out.value = dateTime2.getMillis(); + <#elseif toUnit == "Century"> + dateTime.setMillis(right.value); + dateTime.add(org.joda.time.DurationFieldType.years(), -1); + dateTime.setRounding(dateTime.getChronology().centuryOfEra()); + dateTime2.setMillis(dateTime.getMillis()); + dateTime2.add(org.joda.time.DurationFieldType.years(), 1); + out.value = dateTime2.getMillis(); + <#elseif toUnit == "Millennium"> + dateTime.setMillis(right.value); + int year = dateTime.getYear(); + dateTime.setRounding(dateTime.getChronology().era()); + dateTime2.setMillis(dateTime.getMillis()); + dateTime2.add(org.joda.time.DurationFieldType.years(), ((year-1)/1000)*1000); + out.value = dateTime2.getMillis(); + <#-- End UnitType --> + <#elseif type == "TimeStamp"> + <#if toUnit == "Year"> <#-- Start UnitType --> + dateTime.setMillis(right.value); + dateTime.setRounding(dateTime.getChronology().year()); + out.value = dateTime.getMillis(); + <#elseif toUnit == "Month"> + dateTime.setMillis(right.value); + dateTime.setRounding(dateTime.getChronology().monthOfYear()); + out.value = dateTime.getMillis(); + <#elseif toUnit == "Day"> + dateTime.setMillis(right.value); + dateTime.setRounding(dateTime.getChronology().dayOfMonth()); + out.value = dateTime.getMillis(); + <#elseif toUnit == "Hour"> + dateTime.setMillis(right.value); + dateTime.setRounding(dateTime.getChronology().hourOfDay()); + out.value = dateTime.getMillis(); + <#elseif toUnit == "Minute"> + dateTime.setMillis(right.value); + dateTime.setRounding(dateTime.getChronology().minuteOfHour()); + out.value = dateTime.getMillis(); + <#elseif toUnit == "Second"> + dateTime.setMillis(right.value); + dateTime.setRounding(dateTime.getChronology().secondOfMinute()); + out.value = dateTime.getMillis(); + <#elseif toUnit == "Week"> + dateTime.setMillis(right.value); + dateTime.setRounding(dateTime.getChronology().weekOfWeekyear()); + out.value = dateTime.getMillis(); + <#elseif toUnit == "Quarter"> + dateTime.setMillis(right.value); + int month = dateTime.getMonthOfYear(); + dateTime.setRounding(dateTime.getChronology().year()); + dateTime2.setMillis(dateTime.getMillis()); + dateTime2.add(org.joda.time.DurationFieldType.months(), ((month-1)/3)*3); + out.value = dateTime2.getMillis(); + <#elseif toUnit == "Decade"> + dateTime.setMillis(right.value); + int year = dateTime.getYear(); + dateTime.setRounding(dateTime.getChronology().centuryOfEra()); + dateTime2.setMillis(dateTime.getMillis()); + dateTime2.add(org.joda.time.DurationFieldType.years(), ((year%100)/10)*10); + out.value = dateTime2.getMillis(); + <#elseif toUnit == "Century"> + dateTime.setMillis(right.value); + dateTime.add(org.joda.time.DurationFieldType.years(), -1); + dateTime.setRounding(dateTime.getChronology().centuryOfEra()); + dateTime2.setMillis(dateTime.getMillis()); + dateTime2.add(org.joda.time.DurationFieldType.years(), 1); + out.value = dateTime2.getMillis(); + <#elseif toUnit == "Millennium"> + dateTime.setMillis(right.value); + int year = dateTime.getYear(); + dateTime.setRounding(dateTime.getChronology().era()); + dateTime2.setMillis(dateTime.getMillis()); + dateTime2.add(org.joda.time.DurationFieldType.years(), ((year-1)/1000)*1000); + out.value = dateTime2.getMillis(); + <#-- End UnitType --> + <#elseif type == "Interval"> + <#if toUnit == "Second"> <#-- Start UnitType --> + out.months = right.months; + out.days = right.days; + out.milliseconds = (right.milliseconds/(org.apache.drill.exec.expr.fn.impl.DateUtility.secondsToMillis))* + (org.apache.drill.exec.expr.fn.impl.DateUtility.secondsToMillis); + <#elseif toUnit == "Minute"> + out.months = right.months; + out.days = right.days; + out.milliseconds = (right.milliseconds/(org.apache.drill.exec.expr.fn.impl.DateUtility.minutesToMillis))* + (org.apache.drill.exec.expr.fn.impl.DateUtility.minutesToMillis); + <#elseif toUnit == "Hour"> + out.months = right.months; + out.days = right.days; + out.milliseconds = + (right.milliseconds/(org.apache.drill.exec.expr.fn.impl.DateUtility.hoursToMillis))* + (org.apache.drill.exec.expr.fn.impl.DateUtility.hoursToMillis); + <#elseif toUnit == "Day"> + out.months = right.months; + out.days = right.days; + out.milliseconds = 0; + <#elseif toUnit == "Month"> + out.months = right.months; + out.days = 0; + out.milliseconds = 0; + <#elseif toUnit == "Quarter"> + out.months = (right.months / 3) * 3; + out.days = 0; + out.milliseconds = 0; + <#elseif toUnit == "Year"> + out.months = (right.months / 12) * 12; + out.days = 0; + out.milliseconds = 0; + <#elseif toUnit == "Decade"> + out.months = (right.months / 120) * 120; + out.days = 0; + out.milliseconds = 0; + <#elseif toUnit == "Century"> + out.months = (right.months / 1200) * 1200; + out.days = 0; + out.milliseconds = 0; + <#elseif toUnit == "Millennium"> + out.months = (right.months / 12000) * 12000; + out.days = 0; + out.milliseconds = 0; + <#-- End UnitType --> + <#elseif type == "IntervalDay"> + <#if toUnit == "Second"> <#-- Start UnitType --> + out.days = right.days; + out.milliseconds = (right.milliseconds/(org.apache.drill.exec.expr.fn.impl.DateUtility.secondsToMillis))* + (org.apache.drill.exec.expr.fn.impl.DateUtility.secondsToMillis); + <#elseif toUnit == "Minute"> + out.days = right.days; + out.milliseconds = (right.milliseconds/(org.apache.drill.exec.expr.fn.impl.DateUtility.minutesToMillis))* + (org.apache.drill.exec.expr.fn.impl.DateUtility.minutesToMillis); + <#elseif toUnit == "Hour"> + out.days = right.days; + out.milliseconds = + (right.milliseconds/(org.apache.drill.exec.expr.fn.impl.DateUtility.hoursToMillis))* + (org.apache.drill.exec.expr.fn.impl.DateUtility.hoursToMillis); + <#elseif toUnit == "Day"> + out.days = right.days; + out.milliseconds = 0; + <#elseif toUnit == "Month" || toUnit == "Quarter" || toUnit == "Year" || toUnit == "Decade" || toUnit == "Century" || toUnit == "Millennium"> + out.days = 0; + out.milliseconds = 0; + <#-- End UnitType --> + <#elseif type == "IntervalYear"> + <#if toUnit == "Second" || toUnit == "Minute" || toUnit == "Hour" || toUnit == "Day"> <#-- Start UnitType --> + out.value = right.value; + <#elseif toUnit == "Month"> + out.value = right.value; + <#elseif toUnit == "Quarter"> + out.value = (right.value / 3) * 3; + <#elseif toUnit == "Year"> + out.value = (right.value / 12) * 12; + <#elseif toUnit == "Decade"> + out.value = (right.value / 120) * 120; + <#elseif toUnit == "Century"> + out.value = (right.value / 1200) * 1200; + <#elseif toUnit == "Millennium"> + out.value = (right.value / 12000) * 12000; + <#-- End UnitType --> + <#-- End InputType --> } } - + <#-- Filter out unsupported combinations --> + <#-- End UnitType Loop --> + <#-- End InputType Loop --> } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/drill/blob/4442e162/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 cae6796..87b76ae 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 @@ -22,6 +22,7 @@ import java.util.GregorianCalendar; import java.util.LinkedList; import java.util.List; +import org.apache.calcite.rel.logical.LogicalAggregate; import org.apache.drill.common.exceptions.UserException; import org.apache.drill.common.expression.ExpressionPosition; import org.apache.drill.common.expression.FieldReference; @@ -422,11 +423,41 @@ public class DrillOptiq { } 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); } return FunctionCallFactory.createExpression(functionName, args); } + private LogicalExpression handleDateTruncFunction(final List args) { + // 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.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(); + return FunctionCallFactory.createExpression("date_trunc_" + functionPostfix, args.subList(1, 2)); + } + + throw new UnsupportedOperationException("date_trunc function supports the following time units: " + + "YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, WEEK, QUARTER, DECADE, CENTURY, MILLENNIUM"); + } + + @Override public LogicalExpression visitLiteral(RexLiteral literal) { switch(literal.getType().getSqlTypeName()){ http://git-wip-us.apache.org/repos/asf/drill/blob/4442e162/exec/java-exec/src/test/java/org/apache/drill/TestFunctionsQuery.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/test/java/org/apache/drill/TestFunctionsQuery.java b/exec/java-exec/src/test/java/org/apache/drill/TestFunctionsQuery.java index 24021f8..53a86bb 100644 --- a/exec/java-exec/src/test/java/org/apache/drill/TestFunctionsQuery.java +++ b/exec/java-exec/src/test/java/org/apache/drill/TestFunctionsQuery.java @@ -17,7 +17,6 @@ */ package org.apache.drill; -import org.apache.drill.exec.expr.fn.impl.DateUtility; import org.apache.drill.exec.planner.physical.PlannerSettings; import org.joda.time.DateTime; import org.junit.AfterClass; @@ -27,6 +26,9 @@ import org.junit.Test; import java.math.BigDecimal; +import static org.apache.drill.exec.expr.fn.impl.DateUtility.formatDate; +import static org.apache.drill.exec.expr.fn.impl.DateUtility.formatTimeStamp; + public class TestFunctionsQuery extends BaseTestQuery { // enable decimal data type @@ -543,7 +545,7 @@ public class TestFunctionsQuery extends BaseTestQuery { "timestamp '2008-2-23 12:23:23' as TS " + "FROM cp.`tpch/region.parquet` limit 1"; - DateTime date = DateUtility.formatTimeStamp.parseDateTime("2008-02-23 12:23:23.0"); + DateTime date = formatTimeStamp.parseDateTime("2008-02-23 12:23:23.0"); testBuilder() .sqlQuery(query) .unOrdered() @@ -701,7 +703,7 @@ public class TestFunctionsQuery extends BaseTestQuery { "To_DaTe('2003/07/09', 'yyyy/MM/dd') as col3 " + "from cp.`employee.json` LIMIT 1"; - DateTime date = DateUtility.formatDate.parseDateTime("2003-07-09"); + DateTime date = formatDate.parseDateTime("2003-07-09"); testBuilder() .sqlQuery(query) @@ -780,32 +782,6 @@ public class TestFunctionsQuery extends BaseTestQuery { } @Test - public void testDateTrunc() throws Exception { - String query = "select " - + "date_trunc('MINUTE', time '2:30:21.5') as TIME1, " - + "date_trunc('SECOND', time '2:30:21.5') as TIME2, " - + "date_trunc('HOUR', timestamp '1991-05-05 10:11:12.100') as TS1, " - + "date_trunc('SECOND', timestamp '1991-05-05 10:11:12.100') as TS2, " - + "date_trunc('MONTH', date '2011-2-2') as DATE1, " - + "date_trunc('YEAR', date '2011-2-2') as DATE2 " - + "from cp.`employee.json` where employee_id < 2"; - - DateTime time1 = DateUtility.formatTime.parseDateTime("2:30:00.0"); - DateTime time2 = DateUtility.formatTime.parseDateTime("2:30:21.0"); - DateTime ts1 = DateUtility.formatTimeStamp.parseDateTime("1991-05-05 10:00:00.0"); - DateTime ts2 = DateUtility.formatTimeStamp.parseDateTime("1991-05-05 10:11:12.0"); - DateTime date1 = DateUtility.formatDate.parseDateTime("2011-02-01"); - DateTime date2 = DateUtility.formatDate.parseDateTime("2011-01-01"); - - testBuilder() - .sqlQuery(query) - .unOrdered() - .baselineColumns("TIME1", "TIME2", "TS1", "TS2", "DATE1", "DATE2") - .baselineValues(time1, time2, ts1, ts2, date1, date2) - .go(); - } - - @Test public void testCaseWithDecimalExpressions() throws Exception { String query = "select " + "case when true then cast(employee_id as decimal(15, 5)) else cast('0.0' as decimal(2, 1)) end as col1 " + http://git-wip-us.apache.org/repos/asf/drill/blob/4442e162/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestDateTruncFunctions.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestDateTruncFunctions.java b/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestDateTruncFunctions.java new file mode 100644 index 0000000..db456b2 --- /dev/null +++ b/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestDateTruncFunctions.java @@ -0,0 +1,340 @@ +/** + * 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.BaseTestQuery; +import org.joda.time.DateTime; +import org.joda.time.Period; +import org.junit.Test; + +import static org.apache.drill.exec.expr.fn.impl.DateUtility.formatDate; +import static org.apache.drill.exec.expr.fn.impl.DateUtility.formatTime; +import static org.apache.drill.exec.expr.fn.impl.DateUtility.formatTimeStamp; + +public class TestDateTruncFunctions extends BaseTestQuery { + + @Test + public void dateTruncOnTime() throws Exception { + final String query = "SELECT " + + "date_trunc('SECOND', time '2:30:21.5') as `second`, " + + "date_trunc('MINUTE', time '2:30:21.5') as `minute`, " + + "date_trunc('HOUR', time '2:30:21.5') as `hour`, " + + "date_trunc('DAY', time '2:30:21.5') as `day`, " + + "date_trunc('MONTH', time '2:30:21.5') as `month`, " + + "date_trunc('YEAR', time '2:30:21.5') as `year`, " + + "date_trunc('QUARTER', time '2:30:21.5') as `quarter`, " + + "date_trunc('DECADE', time '2:30:21.5') as `decade`, " + + "date_trunc('CENTURY', time '2:30:21.5') as `century`, " + + "date_trunc('MILLENNIUM', time '2:30:21.5') as `millennium` " + + "FROM sys.version"; + + testBuilder() + .sqlQuery(query) + .unOrdered() + .baselineColumns("second", "minute", "hour", "day", "month", "year", "quarter", "decade", "century", "millennium") + .baselineValues( + formatTime.parseDateTime("2:30:21.0"), // seconds + formatTime.parseDateTime("2:30:00.0"), // minute + formatTime.parseDateTime("2:00:00.0"), // hour + formatTime.parseDateTime("0:00:00.0"), // day + formatTime.parseDateTime("0:00:00.0"), // month + formatTime.parseDateTime("0:00:00.0"), // year + formatTime.parseDateTime("0:00:00.0"), // quarter + formatTime.parseDateTime("0:00:00.0"), // decade + formatTime.parseDateTime("0:00:00.0"), // century + formatTime.parseDateTime("0:00:00.0")) // millennium + .go(); + } + + @Test + public void dateTruncOnDateSimpleUnits() throws Exception { + final String query = "SELECT " + + "date_trunc('SECOND', date '2011-2-3') as `second`, " + + "date_trunc('MINUTE', date '2011-2-3') as `minute`, " + + "date_trunc('HOUR', date '2011-2-3') as `hour`, " + + "date_trunc('DAY', date '2011-2-3') as `day`, " + + "date_trunc('WEEK', date '2011-2-3') as `week`, " + + "date_trunc('MONTH', date '2011-2-3') as `month`, " + + "date_trunc('YEAR', date '2011-2-3') as `year`, " + + "date_trunc('QUARTER', date '2011-5-3') as `q1`, " + + "date_trunc('QUARTER', date '2011-7-13') as `q2`, " + + "date_trunc('QUARTER', date '2011-9-13') as `q3`, " + + "date_trunc('DECADE', date '2011-2-3') as `decade1`, " + + "date_trunc('DECADE', date '2072-2-3') as `decade2`, " + + "date_trunc('DECADE', date '1978-2-3') as `decade3` " + + "FROM sys.version"; + + testBuilder() + .sqlQuery(query) + .unOrdered() + .baselineColumns("second", "minute", "hour", "day", "month", "week" , "year", "q1", "q2", "q3", "decade1", "decade2", "decade3") + .baselineValues( + formatDate.parseDateTime("2011-02-03"), // seconds + formatDate.parseDateTime("2011-02-03"), // minute + formatDate.parseDateTime("2011-02-03"), // hour + formatDate.parseDateTime("2011-02-03"), // day + formatDate.parseDateTime("2011-02-01"), // month + formatDate.parseDateTime("2011-01-31"), // week + formatDate.parseDateTime("2011-01-01"), // year + formatDate.parseDateTime("2011-04-01"), // quarter-1 + formatDate.parseDateTime("2011-07-01"), // quarter-2 + formatDate.parseDateTime("2011-07-01"), // quarter-3 + formatDate.parseDateTime("2010-01-01"), // decade-1 + formatDate.parseDateTime("2070-01-01"), // decade-2 + formatDate.parseDateTime("1970-01-01")) // decade-3 + .go(); + } + + @Test + public void dateTruncOnDateCentury() throws Exception { + // TODO: It would be good to have some tests on dates in BC period, but looks like currently Calcite parser is + // not accepting date literals in BC. + final String query = "SELECT " + + "date_trunc('CENTURY', date '2011-2-3') as c1, " + + "date_trunc('CENTURY', date '2000-2-3') as c2, " + + "date_trunc('CENTURY', date '1901-11-3') as c3, " + + "date_trunc('CENTURY', date '900-2-3') as c4, " + + "date_trunc('CENTURY', date '0001-1-3') as c5 " + + "FROM sys.version"; + + testBuilder() + .sqlQuery(query) + .unOrdered() + .baselineColumns("c1", "c2", "c3", "c4", "c5") + .baselineValues( + formatDate.parseDateTime("2001-01-01"), // c1 + formatDate.parseDateTime("1901-01-01"), // c2 + formatDate.parseDateTime("1901-01-01"), // c3 + formatDate.parseDateTime("0801-01-01"), // c4 + formatDate.parseDateTime("0001-01-01")) // c5 + .go(); + } + + @Test + public void test() throws Exception { + org.joda.time.MutableDateTime dateTime = new org.joda.time.MutableDateTime(org.joda.time.DateTimeZone.UTC); + dateTime.setMillis(formatDate.parseDateTime("2001-01-01")); + dateTime.setRounding(dateTime.getChronology().centuryOfEra()); + } + + @Test + public void dateTruncOnDateMillennium() throws Exception { + // TODO: It would be good to have some tests on dates in BC period, but looks like currently Calcite parser is + // not accepting date literals in BC. + final String query = "SELECT " + + "date_trunc('MILLENNIUM', date '2011-2-3') as `m1`, " + + "date_trunc('MILLENNIUM', date '2000-11-3') as `m2`, " + + "date_trunc('MILLENNIUM', date '1983-05-18') as `m3`, " + + "date_trunc('MILLENNIUM', date '990-11-3') as `m4`, " + + "date_trunc('MILLENNIUM', date '0001-11-3') as `m5` " + + "FROM sys.version"; + + testBuilder() + .sqlQuery(query) + .unOrdered() + .baselineColumns("m1", "m2", "m3", "m4", "m5") + .baselineValues( + formatDate.parseDateTime("2001-01-01"), // m1 + formatDate.parseDateTime("1001-01-01"), // m2 + formatDate.parseDateTime("1001-01-01"), // m3 + formatDate.parseDateTime("0001-01-01"), // m4 + formatDate.parseDateTime("0001-01-01")) // m5 + .go(); + } + + @Test + public void dateTruncOnTimeStampSimpleUnits() throws Exception { + final String query = "SELECT " + + "date_trunc('SECOND', timestamp '2011-2-3 10:11:12.100') as `second`, " + + "date_trunc('MINUTE', timestamp '2011-2-3 10:11:12.100') as `minute`, " + + "date_trunc('HOUR', timestamp '2011-2-3 10:11:12.100') as `hour`, " + + "date_trunc('DAY', timestamp '2011-2-3 10:11:12.100') as `day`, " + + "date_trunc('WEEK', timestamp '2011-2-3 10:11:12.100') as `week`, " + + "date_trunc('MONTH', timestamp '2011-2-3 10:11:12.100') as `month`, " + + "date_trunc('YEAR', timestamp '2011-2-3 10:11:12.100') as `year`, " + + "date_trunc('QUARTER', timestamp '2011-5-3 10:11:12.100') as `q1`, " + + "date_trunc('QUARTER', timestamp '2011-7-13 10:11:12.100') as `q2`, " + + "date_trunc('QUARTER', timestamp '2011-9-13 10:11:12.100') as `q3`, " + + "date_trunc('DECADE', timestamp '2011-2-3 10:11:12.100') as `decade1`, " + + "date_trunc('DECADE', timestamp '2072-2-3 10:11:12.100') as `decade2`, " + + "date_trunc('DECADE', timestamp '1978-2-3 10:11:12.100') as `decade3` " + + "FROM sys.version"; + + testBuilder() + .sqlQuery(query) + .unOrdered() + .baselineColumns("second", "minute", "hour", "day", "month", "week" , "year", "q1", "q2", "q3", "decade1", "decade2", "decade3") + .baselineValues( + formatTimeStamp.parseDateTime("2011-02-03 10:11:12.0"), // seconds + formatTimeStamp.parseDateTime("2011-02-03 10:11:00.0"), // minute + formatTimeStamp.parseDateTime("2011-02-03 10:00:00.0"), // hour + formatTimeStamp.parseDateTime("2011-02-03 00:00:00.0"), // day + formatTimeStamp.parseDateTime("2011-02-01 00:00:00.0"), // month + formatTimeStamp.parseDateTime("2011-01-31 00:00:00.0"), // week + formatTimeStamp.parseDateTime("2011-01-01 00:00:00.0"), // year + formatTimeStamp.parseDateTime("2011-04-01 00:00:00.0"), // quarter-1 + formatTimeStamp.parseDateTime("2011-07-01 00:00:00.0"), // quarter-2 + formatTimeStamp.parseDateTime("2011-07-01 00:00:00.0"), // quarter-3 + formatTimeStamp.parseDateTime("2010-01-01 00:00:00.0"), // decade-1 + formatTimeStamp.parseDateTime("2070-01-01 00:00:00.0"), // decade-2 + formatTimeStamp.parseDateTime("1970-01-01 00:00:00.0")) // decade-3 + .go(); + } + + @Test + public void dateTruncOnTimeStampCentury() throws Exception { + // TODO: It would be good to have some tests on dates in BC period, but looks like currently Calcite parser is + // not accepting date literals in BC. + final String query = "SELECT " + + "date_trunc('CENTURY', timestamp '2011-2-3 10:11:12.100') as c1, " + + "date_trunc('CENTURY', timestamp '2000-2-3 10:11:12.100') as c2, " + + "date_trunc('CENTURY', timestamp '1901-11-3 10:11:12.100') as c3, " + + "date_trunc('CENTURY', timestamp '900-2-3 10:11:12.100') as c4, " + + "date_trunc('CENTURY', timestamp '0001-1-3 10:11:12.100') as c5 " + + "FROM sys.version"; + + testBuilder() + .sqlQuery(query) + .unOrdered() + .baselineColumns("c1", "c2", "c3", "c4", "c5") + .baselineValues( + formatTimeStamp.parseDateTime("2001-01-01 00:00:00.0"), // c1 + formatTimeStamp.parseDateTime("1901-01-01 00:00:00.0"), // c2 + formatTimeStamp.parseDateTime("1901-01-01 00:00:00.0"), // c3 + formatTimeStamp.parseDateTime("0801-01-01 00:00:00.0"), // c4 + formatTimeStamp.parseDateTime("0001-01-01 00:00:00.0")) // c5 + .go(); + } + + @Test + public void dateTruncOnTimeStampMillennium() throws Exception { + // TODO: It would be good to have some tests on dates in BC period, but looks like currently Calcite parser is + // not accepting date literals in BC. + final String query = "SELECT " + + "date_trunc('MILLENNIUM', timestamp '2011-2-3 10:11:12.100') as `m1`, " + + "date_trunc('MILLENNIUM', timestamp '2000-11-3 10:11:12.100') as `m2`, " + + "date_trunc('MILLENNIUM', timestamp '1983-05-18 10:11:12.100') as `m3`, " + + "date_trunc('MILLENNIUM', timestamp '990-11-3 10:11:12.100') as `m4`, " + + "date_trunc('MILLENNIUM', timestamp '0001-11-3 10:11:12.100') as `m5` " + + "FROM sys.version"; + + testBuilder() + .sqlQuery(query) + .unOrdered() + .baselineColumns("m1", "m2", "m3", "m4", "m5") + .baselineValues( + formatTimeStamp.parseDateTime("2001-01-01 00:00:00.0"), // m1 + formatTimeStamp.parseDateTime("1001-01-01 00:00:00.0"), // m2 + formatTimeStamp.parseDateTime("1001-01-01 00:00:00.0"), // m3 + formatTimeStamp.parseDateTime("0001-01-01 00:00:00.0"), // m4 + formatTimeStamp.parseDateTime("0001-01-01 00:00:00.0")) // m5 + .go(); + } + + @Test + public void dateTruncOnIntervalYear() throws Exception { + final String query = "SELECT " + + "date_trunc('SECOND', interval '217-7' year(3) to month) as `second`, " + + "date_trunc('MINUTE', interval '217-7' year(3) to month) as `minute`, " + + "date_trunc('HOUR', interval '217-7' year(3) to month) as `hour`, " + + "date_trunc('DAY', interval '217-7' year(3) to month) as `day`, " + + "date_trunc('MONTH', interval '217-7' year(3) to month) as `month`, " + + "date_trunc('YEAR', interval '217-7' year(3) to month) as `year`, " + + "date_trunc('QUARTER', interval '217-7' year(3) to month) as `quarter`, " + + "date_trunc('DECADE', interval '217-7' year(3) to month) as `decade`, " + + "date_trunc('CENTURY', interval '217-7' year(3) to month) as `century`, " + + "date_trunc('MILLENNIUM', interval '217-7' year(3) to month) as `millennium` " + + "FROM sys.version"; + + testBuilder() + .sqlQuery(query) + .unOrdered() + .baselineColumns("second", "minute", "hour", "day", "month", "year", "quarter", "decade", "century", "millennium") + .baselineValues( + new Period("P217Y7M").normalizedStandard(), // seconds + new Period("P217Y7M").normalizedStandard(), // minute + new Period("P217Y7M").normalizedStandard(), // hour + new Period("P217Y7M").normalizedStandard(), // day + new Period("P217Y7M").normalizedStandard(), // month + new Period("P217Y").normalizedStandard(), // year + new Period("P217Y6M").normalizedStandard(), // quarter + new Period("P210Y").normalizedStandard(), // decade + new Period("P200Y").normalizedStandard(), // century + new Period("PT0S").normalizedStandard()) // millennium + .go(); + } + + @Test + public void dateTruncOnIntervalDay() throws Exception { + final String query = "SELECT " + + "date_trunc('SECOND', interval '200 10:20:30.123' day(3) to second) as `second`, " + + "date_trunc('MINUTE', interval '200 10:20:30.123' day(3) to second) as `minute`, " + + "date_trunc('HOUR', interval '200 10:20:30.123' day(3) to second) as `hour`, " + + "date_trunc('DAY', interval '200 10:20:30.123' day(3) to second) as `day`, " + + "date_trunc('MONTH', interval '200 10:20:30.123' day(3) to second) as `month`, " + + "date_trunc('YEAR', interval '200 10:20:30.123' day(3) to second) as `year`, " + + "date_trunc('QUARTER', interval '200 10:20:30.123' day(3) to second) as `quarter`, " + + "date_trunc('DECADE', interval '200 10:20:30.123' day(3) to second) as `decade`, " + + "date_trunc('CENTURY', interval '200 10:20:30.123' day(3) to second) as `century`, " + + "date_trunc('MILLENNIUM', interval '200 10:20:30.123' day(3) to second) as `millennium` " + + "FROM sys.version"; + + testBuilder() + .sqlQuery(query) + .unOrdered() + .baselineColumns("second", "minute", "hour", "day", "month", "year", "quarter", "decade", "century", "millennium") + .baselineValues( + new Period().plusDays(200).plusMillis(37230000), // seconds + new Period().plusDays(200).plusMillis(37200000), // minute + new Period().plusDays(200).plusMillis(36000000), // hour + new Period().plusDays(200), // day + new Period("PT0S"), // month + new Period("PT0S"), // year + new Period("PT0S"), // quarter + new Period("PT0S"), // decade + new Period("PT0S"), // century + new Period("PT0S")) // millennium + .go(); + } + + @Test + public void testDateTrunc() throws Exception { + String query = "select " + + "date_trunc('MINUTE', time '2:30:21.5') as TIME1, " + + "date_trunc('SECOND', time '2:30:21.5') as TIME2, " + + "date_trunc('HOUR', timestamp '1991-05-05 10:11:12.100') as TS1, " + + "date_trunc('SECOND', timestamp '1991-05-05 10:11:12.100') as TS2, " + + "date_trunc('MONTH', date '2011-2-2') as DATE1, " + + "date_trunc('YEAR', date '2011-2-2') as DATE2 " + + "from cp.`employee.json` where employee_id < 2"; + + DateTime time1 = formatTime.parseDateTime("2:30:00.0"); + DateTime time2 = formatTime.parseDateTime("2:30:21.0"); + DateTime ts1 = formatTimeStamp.parseDateTime("1991-05-05 10:00:00.0"); + DateTime ts2 = formatTimeStamp.parseDateTime("1991-05-05 10:11:12.0"); + DateTime date1 = formatDate.parseDateTime("2011-02-01"); + DateTime date2 = formatDate.parseDateTime("2011-01-01"); + + testBuilder() + .sqlQuery(query) + .unOrdered() + .baselineColumns("TIME1", "TIME2", "TS1", "TS2", "DATE1", "DATE2") + .baselineValues(time1, time2, ts1, ts2, date1, date2) + .go(); + } +}