Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 2AE01200BC8 for ; Wed, 23 Nov 2016 10:07:01 +0100 (CET) Received: by cust-asf.ponee.io (Postfix) id 296D5160AFA; Wed, 23 Nov 2016 09:07:01 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 25BEB160AFD for ; Wed, 23 Nov 2016 10:07:00 +0100 (CET) Received: (qmail 27857 invoked by uid 500); 23 Nov 2016 09:06:59 -0000 Mailing-List: contact commits-help@flink.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@flink.apache.org Delivered-To: mailing list commits@flink.apache.org Received: (qmail 27848 invoked by uid 99); 23 Nov 2016 09:06:59 -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; Wed, 23 Nov 2016 09:06:59 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id 429BFDFCC6; Wed, 23 Nov 2016 09:06:59 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: twalthr@apache.org To: commits@flink.apache.org Message-Id: <2c542c4bd7ce4acfabbb517c2e39e5e2@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: flink git commit: [FLINK-5124] [table] Support more temporal arithmetic Date: Wed, 23 Nov 2016 09:06:59 +0000 (UTC) archived-at: Wed, 23 Nov 2016 09:07:01 -0000 Repository: flink Updated Branches: refs/heads/master 4c23879a5 -> 4653ad388 [FLINK-5124] [table] Support more temporal arithmetic This closes #2851. Project: http://git-wip-us.apache.org/repos/asf/flink/repo Commit: http://git-wip-us.apache.org/repos/asf/flink/commit/4653ad38 Tree: http://git-wip-us.apache.org/repos/asf/flink/tree/4653ad38 Diff: http://git-wip-us.apache.org/repos/asf/flink/diff/4653ad38 Branch: refs/heads/master Commit: 4653ad388b05b3ba7e37982bb8af09e7fcbed6be Parents: 4c23879 Author: twalthr Authored: Tue Nov 22 18:30:38 2016 +0100 Committer: twalthr Committed: Wed Nov 23 10:05:55 2016 +0100 ---------------------------------------------------------------------- .../flink/api/table/codegen/CodeGenerator.scala | 2 +- .../table/codegen/calls/ScalarOperators.scala | 34 ++- .../api/table/expressions/arithmetic.scala | 4 + .../table/expressions/TemporalTypesTest.scala | 215 +++++++++++++++++-- 4 files changed, 220 insertions(+), 35 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/flink/blob/4653ad38/flink-libraries/flink-table/src/main/scala/org/apache/flink/api/table/codegen/CodeGenerator.scala ---------------------------------------------------------------------- diff --git a/flink-libraries/flink-table/src/main/scala/org/apache/flink/api/table/codegen/CodeGenerator.scala b/flink-libraries/flink-table/src/main/scala/org/apache/flink/api/table/codegen/CodeGenerator.scala index d40e0e3..2a8ef44 100644 --- a/flink-libraries/flink-table/src/main/scala/org/apache/flink/api/table/codegen/CodeGenerator.scala +++ b/flink-libraries/flink-table/src/main/scala/org/apache/flink/api/table/codegen/CodeGenerator.scala @@ -792,7 +792,7 @@ class CodeGenerator( requireNumeric(right) generateArithmeticOperator("-", nullCheck, resultType, left, right) - case MINUS if isTemporal(resultType) => + case MINUS | MINUS_DATE if isTemporal(resultType) => val left = operands.head val right = operands(1) requireTemporal(left) http://git-wip-us.apache.org/repos/asf/flink/blob/4653ad38/flink-libraries/flink-table/src/main/scala/org/apache/flink/api/table/codegen/calls/ScalarOperators.scala ---------------------------------------------------------------------- diff --git a/flink-libraries/flink-table/src/main/scala/org/apache/flink/api/table/codegen/calls/ScalarOperators.scala b/flink-libraries/flink-table/src/main/scala/org/apache/flink/api/table/codegen/calls/ScalarOperators.scala index bf76015..75c0149 100644 --- a/flink-libraries/flink-table/src/main/scala/org/apache/flink/api/table/codegen/calls/ScalarOperators.scala +++ b/flink-libraries/flink-table/src/main/scala/org/apache/flink/api/table/codegen/calls/ScalarOperators.scala @@ -656,38 +656,36 @@ object ScalarOperators { right: GeneratedExpression) : GeneratedExpression = { - val operator = if (plus) "+" else "-" + val op = if (plus) "+" else "-" (left.resultType, right.resultType) match { case (l: TimeIntervalTypeInfo[_], r: TimeIntervalTypeInfo[_]) if l == r => - generateArithmeticOperator(operator, nullCheck, l, left, right) + generateArithmeticOperator(op, nullCheck, l, left, right) + + case (SqlTimeTypeInfo.DATE, TimeIntervalTypeInfo.INTERVAL_MILLIS) => + generateOperatorIfNotNull(nullCheck, SqlTimeTypeInfo.DATE, left, right) { + (l, r) => s"$l $op ((int) ($r / ${MILLIS_PER_DAY}L))" + } - case (SqlTimeTypeInfo.DATE, TimeIntervalTypeInfo.INTERVAL_MILLIS) | - (TimeIntervalTypeInfo.INTERVAL_MILLIS, SqlTimeTypeInfo.DATE) => + case (SqlTimeTypeInfo.DATE, TimeIntervalTypeInfo.INTERVAL_MONTHS) => generateOperatorIfNotNull(nullCheck, SqlTimeTypeInfo.DATE, left, right) { - if (isTimePoint(left.resultType)) { - (leftTerm, rightTerm) => s"$leftTerm + ((int) ($rightTerm / ${MILLIS_PER_DAY}L))" - } else { - (leftTerm, rightTerm) => s"((int) ($leftTerm / ${MILLIS_PER_DAY}L)) + $rightTerm" - } + (l, r) => s"${qualifyMethod(BuiltInMethod.ADD_MONTHS.method)}($l, $op($r))" } - case (SqlTimeTypeInfo.TIME, TimeIntervalTypeInfo.INTERVAL_MILLIS) | - (TimeIntervalTypeInfo.INTERVAL_MILLIS, SqlTimeTypeInfo.TIME) => + case (SqlTimeTypeInfo.TIME, TimeIntervalTypeInfo.INTERVAL_MILLIS) => generateOperatorIfNotNull(nullCheck, SqlTimeTypeInfo.TIME, left, right) { - if (isTimePoint(left.resultType)) { - (leftTerm, rightTerm) => s"$leftTerm + ((int) ($rightTerm))" - } else { - (leftTerm, rightTerm) => s"((int) ($leftTerm)) + $rightTerm" - } + (l, r) => s"$l $op ((int) ($r))" } case (SqlTimeTypeInfo.TIMESTAMP, TimeIntervalTypeInfo.INTERVAL_MILLIS) => generateOperatorIfNotNull(nullCheck, SqlTimeTypeInfo.TIMESTAMP, left, right) { - (leftTerm, rightTerm) => s"$leftTerm + $rightTerm" + (l, r) => s"$l $op $r" } - // TODO more operations when CALCITE-308 is fixed + case (SqlTimeTypeInfo.TIMESTAMP, TimeIntervalTypeInfo.INTERVAL_MONTHS) => + generateOperatorIfNotNull(nullCheck, SqlTimeTypeInfo.TIMESTAMP, left, right) { + (l, r) => s"${qualifyMethod(BuiltInMethod.ADD_MONTHS.method)}($l, $op($r))" + } case _ => throw new CodeGenException("Unsupported temporal arithmetic.") http://git-wip-us.apache.org/repos/asf/flink/blob/4653ad38/flink-libraries/flink-table/src/main/scala/org/apache/flink/api/table/expressions/arithmetic.scala ---------------------------------------------------------------------- diff --git a/flink-libraries/flink-table/src/main/scala/org/apache/flink/api/table/expressions/arithmetic.scala b/flink-libraries/flink-table/src/main/scala/org/apache/flink/api/table/expressions/arithmetic.scala index cf4f82a..8702886 100644 --- a/flink-libraries/flink-table/src/main/scala/org/apache/flink/api/table/expressions/arithmetic.scala +++ b/flink-libraries/flink-table/src/main/scala/org/apache/flink/api/table/expressions/arithmetic.scala @@ -129,6 +129,10 @@ case class Minus(left: Expression, right: Expression) extends BinaryArithmetic { override private[flink] def validateInput(): ValidationResult = { if (isTimeInterval(left.resultType) && left.resultType == right.resultType) { ValidationSuccess + } else if (isTimePoint(left.resultType) && isTimeInterval(right.resultType)) { + ValidationSuccess + } else if (isTimeInterval(left.resultType) && isTimePoint(right.resultType)) { + ValidationSuccess } else { super.validateInput() } http://git-wip-us.apache.org/repos/asf/flink/blob/4653ad38/flink-libraries/flink-table/src/test/scala/org/apache/flink/api/table/expressions/TemporalTypesTest.scala ---------------------------------------------------------------------- diff --git a/flink-libraries/flink-table/src/test/scala/org/apache/flink/api/table/expressions/TemporalTypesTest.scala b/flink-libraries/flink-table/src/test/scala/org/apache/flink/api/table/expressions/TemporalTypesTest.scala index 9a34260..0547552 100644 --- a/flink-libraries/flink-table/src/test/scala/org/apache/flink/api/table/expressions/TemporalTypesTest.scala +++ b/flink-libraries/flink-table/src/test/scala/org/apache/flink/api/table/expressions/TemporalTypesTest.scala @@ -275,6 +275,9 @@ class TemporalTypesTest extends ExpressionTestBase { @Test def testTimeIntervalArithmetic(): Unit = { + + // interval months comparison + testAllApis( 12.months < 24.months, "12.months < 24.months", @@ -282,17 +285,27 @@ class TemporalTypesTest extends ExpressionTestBase { "true") testAllApis( + 8.years === 8.years, + "8.years === 8.years", + "INTERVAL '8' YEAR = INTERVAL '8' YEAR", + "true") + + // interval millis comparison + + testAllApis( 8.millis > 10.millis, "8.millis > 10.millis", "INTERVAL '0.008' SECOND > INTERVAL '0.010' SECOND", "false") testAllApis( - 8.years === 8.years, - "8.years === 8.years", - "INTERVAL '8' YEAR = INTERVAL '8' YEAR", + 8.millis === 8.millis, + "8.millis === 8.millis", + "INTERVAL '0.008' SECOND = INTERVAL '0.008' SECOND", "true") + // interval months addition/subtraction + testAllApis( 8.years + 10.months, "8.years + 10.months", @@ -300,6 +313,20 @@ class TemporalTypesTest extends ExpressionTestBase { "+8-10") testAllApis( + 2.years - 12.months, + "2.years - 12.months", + "INTERVAL '2' YEAR - INTERVAL '12' MONTH", + "+1-00") + + testAllApis( + -2.years, + "-2.years", + "-INTERVAL '2' YEAR", + "-2-00") + + // interval millis addition/subtraction + + testAllApis( 8.hours + 10.minutes + 12.seconds + 5.millis, "8.hours + 10.minutes + 12.seconds + 5.millis", "INTERVAL '8' HOUR + INTERVAL '10' MINUTE + INTERVAL '12.005' SECOND", @@ -312,46 +339,202 @@ class TemporalTypesTest extends ExpressionTestBase { "+0 00:00:50.000") testAllApis( - 2.years - 12.months, - "2.years - 12.months", - "INTERVAL '2' YEAR - INTERVAL '12' MONTH", - "+1-00") + -10.seconds, + "-10.seconds", + "-INTERVAL '10' SECOND", + "-0 00:00:10.000") - testAllApis( - -'f9.cast(Types.INTERVAL_MONTHS), - "-f9.cast(INTERVAL_MONTHS)", - "-CAST(f9 AS INTERVAL YEAR)", - "-2-00") + // addition to date + // interval millis testAllApis( 'f0 + 2.days, "f0 + 2.days", "f0 + INTERVAL '2' DAY", "1990-10-16") + // interval millis testAllApis( 30.days + 'f0, "30.days + f0", "INTERVAL '30' DAY + f0", "1990-11-13") + // interval months + testAllApis( + 'f0 + 2.months, + "f0 + 2.months", + "f0 + INTERVAL '2' MONTH", + "1990-12-14") + + // interval months + testAllApis( + 2.months + 'f0, + "2.months + f0", + "INTERVAL '2' MONTH + f0", + "1990-12-14") + + // addition to time + + // interval millis testAllApis( 'f1 + 12.hours, "f1 + 12.hours", "f1 + INTERVAL '12' HOUR", "22:20:45") + // interval millis testAllApis( - 24.hours + 'f1, - "24.hours + f1", - "INTERVAL '24' HOUR + f1", - "10:20:45") + 12.hours + 'f1, + "12.hours + f1", + "INTERVAL '12' HOUR + f1", + "22:20:45") + + // addition to timestamp + // interval millis testAllApis( 'f2 + 10.days + 4.millis, "f2 + 10.days + 4.millis", "f2 + INTERVAL '10 00:00:00.004' DAY TO SECOND", "1990-10-24 10:20:45.127") + + // interval millis + testAllApis( + 10.days + 'f2 + 4.millis, + "10.days + f2 + 4.millis", + "INTERVAL '10 00:00:00.004' DAY TO SECOND + f2", + "1990-10-24 10:20:45.127") + + // interval months + testAllApis( + 'f2 + 10.years, + "f2 + 10.years", + "f2 + INTERVAL '10' YEAR", + "2000-10-14 10:20:45.123") + + // interval months + testAllApis( + 10.years + 'f2, + "10.years + f2", + "INTERVAL '10' YEAR + f2", + "2000-10-14 10:20:45.123") + + // subtraction from date + + // interval millis + testAllApis( + 'f0 - 2.days, + "f0 - 2.days", + "f0 - INTERVAL '2' DAY", + "1990-10-12") + + // interval millis + testAllApis( + -30.days + 'f0, + "-30.days + f0", + "INTERVAL '-30' DAY + f0", + "1990-09-14") + + // interval months + testAllApis( + 'f0 - 2.months, + "f0 - 2.months", + "f0 - INTERVAL '2' MONTH", + "1990-08-14") + + // interval months + testAllApis( + -2.months + 'f0, + "-2.months + f0", + "-INTERVAL '2' MONTH + f0", + "1990-08-14") + + // subtraction from time + + // interval millis + testAllApis( + 'f1 - 12.hours, + "f1 - 12.hours", + "f1 - INTERVAL '12' HOUR", + "22:20:45") + + // interval millis + testAllApis( + -12.hours + 'f1, + "-12.hours + f1", + "INTERVAL '-12' HOUR + f1", + "22:20:45") + + // subtraction from timestamp + + // interval millis + testAllApis( + 'f2 - 10.days - 4.millis, + "f2 - 10.days - 4.millis", + "f2 - INTERVAL '10 00:00:00.004' DAY TO SECOND", + "1990-10-04 10:20:45.119") + + // interval millis + testAllApis( + -10.days + 'f2 - 4.millis, + "-10.days + f2 - 4.millis", + "INTERVAL '-10 00:00:00.004' DAY TO SECOND + f2", + "1990-10-04 10:20:45.119") + + // interval months + testAllApis( + 'f2 - 10.years, + "f2 - 10.years", + "f2 - INTERVAL '10' YEAR", + "1980-10-14 10:20:45.123") + + // interval months + testAllApis( + -10.years + 'f2, + "-10.years + f2", + "INTERVAL '-10' YEAR + f2", + "1980-10-14 10:20:45.123") + + // casting + + testAllApis( + -'f9.cast(Types.INTERVAL_MONTHS), + "-f9.cast(INTERVAL_MONTHS)", + "-CAST(f9 AS INTERVAL YEAR)", + "-2-00") + + testAllApis( + -'f10.cast(Types.INTERVAL_MILLIS), + "-f10.cast(INTERVAL_MILLIS)", + "-CAST(f10 AS INTERVAL SECOND)", + "-0 00:00:12.000") + + // addition/subtraction of interval millis and interval months + + testAllApis( + 'f0 + 2.days + 1.month, + "f0 + 2.days + 1.month", + "f0 + INTERVAL '2' DAY + INTERVAL '1' MONTH", + "1990-11-16") + + testAllApis( + 'f0 - 2.days - 1.month, + "f0 - 2.days - 1.month", + "f0 - INTERVAL '2' DAY - INTERVAL '1' MONTH", + "1990-09-12") + + testAllApis( + 'f2 + 2.days + 1.month, + "f2 + 2.days + 1.month", + "f2 + INTERVAL '2' DAY + INTERVAL '1' MONTH", + "1990-11-16 10:20:45.123") + + testAllApis( + 'f2 - 2.days - 1.month, + "f2 - 2.days - 1.month", + "f2 - INTERVAL '2' DAY - INTERVAL '1' MONTH", + "1990-09-12 10:20:45.123") } // ----------------------------------------------------------------------------------------------