KurtYoung commented on a change in pull request #15133:
URL: https://github.com/apache/flink/pull/15133#discussion_r592966733
##########
File path:
flink-table/flink-table-planner-blink/src/main/scala/org/apache/flink/table/planner/codegen/calls/ScalarOperatorGens.scala
##########
@@ -272,6 +272,49 @@ object ScalarOperatorGens {
}
}
+ // minus arithmetic of time points (i.e. for TIMESTAMPDIFF for
TIMESTAMP_LTZ)
+ case (TIMESTAMP_WITH_LOCAL_TIME_ZONE, TIMESTAMP_WITH_LOCAL_TIME_ZONE |
+ TIMESTAMP_WITHOUT_TIME_ZONE | TIME_WITHOUT_TIME_ZONE | DATE) |
+ (TIMESTAMP_WITH_LOCAL_TIME_ZONE | TIMESTAMP_WITHOUT_TIME_ZONE |
+ TIME_WITHOUT_TIME_ZONE | DATE, TIMESTAMP_WITH_LOCAL_TIME_ZONE) if
!plus =>
+ resultType.getTypeRoot match {
+ case INTERVAL_YEAR_MONTH =>
+ generateOperatorIfNotNull(ctx, resultType, left, right) {
+ (ll, rr) => (left.resultType.getTypeRoot,
right.resultType.getTypeRoot) match {
+ case (TIMESTAMP_WITH_LOCAL_TIME_ZONE,
TIMESTAMP_WITH_LOCAL_TIME_ZONE) =>
+ val leftTerm = s"$ll.getMillisecond()"
+ val rightTerm = s"$rr.getMillisecond()"
+
s"${qualifyMethod(BuiltInMethods.SUBTRACT_MONTHS)}($leftTerm, $rightTerm)"
+ case _ =>
+ throw new CodeGenException(String.format(
+ "Unsupported TIMESTAMPDIFF(%s, %s, %s) which contains
TIMESTAMP_LTZ type." +
+ " TIMESTAMP_LTZ type only supports
TIMESTAMPDIFF(timepointunit," +
Review comment:
Simplify the error message a little bit: `TIMESTAMP_LTZ only supports
diff between the same type.`
##########
File path:
flink-table/flink-table-planner-blink/src/test/scala/org/apache/flink/table/planner/expressions/TemporalTypesTest.scala
##########
@@ -1136,6 +1137,126 @@ class TemporalTypesTest extends ExpressionTestBase {
testSqlApi(
"TIMESTAMPDIFF(MONTH, DATE '2019-09-01', DATE '2016-08-01')",
"-37")
+ testSqlApi(
+ "TIMESTAMPDIFF(MONTH, TIMESTAMP '2021-01-04 00:00:00', DATE
'2021-02-04')",
+ "1")
+ testSqlApi(
+ "TIMESTAMPDIFF(MONTH, DATE '2020-01-04', TIMESTAMP '2021-02-04
12:00:00')",
+ "13")
+ testSqlApi(
+ "TIMESTAMPDIFF(MONTH, TIMESTAMP '2021-01-04 00:00:00', TIME '00:00:00')",
+ "-612")
+ testSqlApi(
+ "TIMESTAMPDIFF(MONTH, TIME '00:00:00', TIMESTAMP '2021-02-04 12:00:00')",
+ "613")
+ testSqlApi(
+ "TIMESTAMPDIFF(MONTH, DATE '2021-01-04', TIME '00:00:00')",
+ "-612")
+ testSqlApi(
+ "TIMESTAMPDIFF(MONTH, TIME '00:00:00', DATE '2021-02-04')",
+ "613")
+ }
+
+ @Test
+ def testTimestampLtzArithmetic(): Unit = {
+ // TIMESTAMP_LTZ +/- INTERVAL should support nanosecond
+ testSqlApi(
+ s"${timestampTz("1970-02-01 00:00:00.123456789")} + INTERVAL '1' YEAR",
+ "1971-02-01 00:00:00.123456789")
+
+ testSqlApi(
+ s"${timestampTz("1970-02-01 00:00:00.123456789")} - INTERVAL '1' MONTH",
+ "1970-01-01 00:00:00.123456789")
+
+ testSqlApi(
+ s"${timestampTz("1970-02-01 00:00:00.123456789")} + INTERVAL '1' DAY",
+ "1970-02-02 00:00:00.123456789")
+
+ testSqlApi(
+ s"${timestampTz("1970-02-01 00:00:00.123456789")} - INTERVAL '1' HOUR",
+ "1970-01-31 23:00:00.123456789")
+
+ testSqlApi(
+ s"${timestampTz("1970-02-01 00:00:00.123456789")} + INTERVAL '1' MINUTE",
+ "1970-02-01 00:01:00.123456789")
+
+ testSqlApi(
+ s"${timestampTz("1970-02-01 00:00:00.123456789")} - INTERVAL '1' SECOND",
+ "1970-01-31 23:59:59.123456789")
+
+ // test TIMESTAMPDIFF for TIMESTAMP_LTZ type
+ testSqlApi(
Review comment:
add tests for TIMESTAMPDIFF between TIMESTAMP_LTZ type and NULL
##########
File path:
flink-table/flink-table-planner-blink/src/test/scala/org/apache/flink/table/planner/expressions/TemporalTypesTest.scala
##########
@@ -1136,6 +1137,126 @@ class TemporalTypesTest extends ExpressionTestBase {
testSqlApi(
"TIMESTAMPDIFF(MONTH, DATE '2019-09-01', DATE '2016-08-01')",
"-37")
+ testSqlApi(
+ "TIMESTAMPDIFF(MONTH, TIMESTAMP '2021-01-04 00:00:00', DATE
'2021-02-04')",
+ "1")
+ testSqlApi(
+ "TIMESTAMPDIFF(MONTH, DATE '2020-01-04', TIMESTAMP '2021-02-04
12:00:00')",
+ "13")
+ testSqlApi(
+ "TIMESTAMPDIFF(MONTH, TIMESTAMP '2021-01-04 00:00:00', TIME '00:00:00')",
+ "-612")
+ testSqlApi(
+ "TIMESTAMPDIFF(MONTH, TIME '00:00:00', TIMESTAMP '2021-02-04 12:00:00')",
+ "613")
+ testSqlApi(
+ "TIMESTAMPDIFF(MONTH, DATE '2021-01-04', TIME '00:00:00')",
+ "-612")
+ testSqlApi(
+ "TIMESTAMPDIFF(MONTH, TIME '00:00:00', DATE '2021-02-04')",
+ "613")
+ }
+
+ @Test
+ def testTimestampLtzArithmetic(): Unit = {
+ // TIMESTAMP_LTZ +/- INTERVAL should support nanosecond
+ testSqlApi(
+ s"${timestampTz("1970-02-01 00:00:00.123456789")} + INTERVAL '1' YEAR",
+ "1971-02-01 00:00:00.123456789")
+
+ testSqlApi(
+ s"${timestampTz("1970-02-01 00:00:00.123456789")} - INTERVAL '1' MONTH",
+ "1970-01-01 00:00:00.123456789")
+
+ testSqlApi(
+ s"${timestampTz("1970-02-01 00:00:00.123456789")} + INTERVAL '1' DAY",
+ "1970-02-02 00:00:00.123456789")
+
+ testSqlApi(
+ s"${timestampTz("1970-02-01 00:00:00.123456789")} - INTERVAL '1' HOUR",
+ "1970-01-31 23:00:00.123456789")
+
+ testSqlApi(
+ s"${timestampTz("1970-02-01 00:00:00.123456789")} + INTERVAL '1' MINUTE",
+ "1970-02-01 00:01:00.123456789")
+
+ testSqlApi(
+ s"${timestampTz("1970-02-01 00:00:00.123456789")} - INTERVAL '1' SECOND",
+ "1970-01-31 23:59:59.123456789")
+
+ // test TIMESTAMPDIFF for TIMESTAMP_LTZ type
+ testSqlApi(
+ s"TIMESTAMPDIFF(YEAR, ${timestampTz("1970-01-01 00:00:00.123456789")}," +
+ s" ${timestampTz("1971-01-02 01:02:03.123456789")})",
+ "1")
+
+ testSqlApi(
+ s"TIMESTAMPDIFF(MONTH, ${timestampTz("1970-01-01 00:00:00.123456789")},"
+
+ s" ${timestampTz("1971-01-02 01:02:03.123456789")})",
+ "12")
+
+ testSqlApi(
+ s"TIMESTAMPDIFF(DAY, ${timestampTz("1970-01-01 00:00:00.123")}," +
+ s" ${timestampTz("1971-01-02 01:02:03.123")})",
+ "366")
+
+ testSqlApi(
+ s"TIMESTAMPDIFF(HOUR, ${timestampTz("1970-01-01 00:00:00.123")}," +
+ s" ${timestampTz("1970-01-01 01:02:03.123")})",
+ "1")
+
+ testSqlApi(
+ s"TIMESTAMPDIFF(MINUTE, ${timestampTz("1970-01-01 01:02:03.123")}," +
+ s" ${timestampTz("1970-01-01 00:00:00")})",
+ "-62")
+
+ testSqlApi(
+ s"TIMESTAMPDIFF(SECOND, ${timestampTz("1970-01-01 00:00:00.123")}," +
+ s" ${timestampTz("1970-01-01 00:02:03.234")})",
+ "123")
+ }
+
+ @Test
+ def testInvalidTimestampLtzArithmetic(): Unit = {
+ val exceptionTemplate =
Review comment:
add invalid test between TIMESTAMP_LTZ and some non-time types such as
varchar
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to 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
