wuchong commented on a change in pull request #9099:
[FLINK-13237][table-planner-blink] Add expression table api test to blink
URL: https://github.com/apache/flink/pull/9099#discussion_r309595542
##########
File path:
flink-table/flink-table-planner-blink/src/test/scala/org/apache/flink/table/planner/expressions/ScalarFunctionsTest.scala
##########
@@ -2531,27 +3423,60 @@ class ScalarFunctionsTest extends ScalarTypesTestBase {
"SQL_TSI_SECOND" -> SECOND
)
- for ((interval, result) <- intervalMapResults) {
- testSqlApi(
- s"TIMESTAMPADD($interval, ${data.head._1}, ${data.head._2})",
result.head)
- testSqlApi(
- s"TIMESTAMPADD($interval, ${data(1)._1}, ${data(1)._2})", result(1))
- testSqlApi(
- s"TIMESTAMPADD($interval, ${data(2)._1}, ${data(2)._2})", result(2))
- testSqlApi(
- s"TIMESTAMPADD($interval, ${data(3)._1}, ${data(3)._2})", result(3))
- testSqlApi(
- s"TIMESTAMPADD($interval, ${data(4)._1}, ${data(4)._2})", result(4))
+ def intervalCount(interval: String, count: Int): (Expression, String) =
interval match {
+ case "YEAR" => (count.years, s"$count.years")
+ case "SQL_TSI_YEAR" => (count.years, s"$count.years")
+ case "QUARTER" => (count.quarters, s"$count.quarters")
+ case "SQL_TSI_QUARTER" => (count.quarters, s"$count.quarters")
+ case "MONTH" => (count.months, s"$count.months")
+ case "SQL_TSI_MONTH" => (count.months, s"$count.months")
+ case "WEEK" => (count.weeks, s"$count.weeks")
+ case "SQL_TSI_WEEK" => (count.weeks, s"$count.weeks")
+ case "DAY" => (count.days, s"$count.days")
+ case "SQL_TSI_DAY" => (count.days, s"$count.days")
+ case "HOUR" => (count.hours, s"$count.hours")
+ case "SQL_TSI_HOUR" => (count.hours, s"$count.hours")
+ case "MINUTE" => (count.minutes, s"$count.minutes")
+ case "SQL_TSI_MINUTE" => (count.minutes, s"$count.minutes")
+ case "SECOND" => (count.seconds, s"$count.seconds")
+ case "SQL_TSI_SECOND" => (count.seconds, s"$count.seconds")
}
- testSqlApi("TIMESTAMPADD(HOUR, CAST(NULL AS INTEGER), TIMESTAMP
'2016-02-24 12:42:25')", "null")
+ for ((interval, result) <- intervalMapResults) {
+ for (i <- 0 to 4) {
+ val (offset, ts) = data(i)
+ val timeInterval = intervalCount(interval, offset)
+ testAllApis(
+ timeInterval._1 + ts.toTimestamp,
+ s"${timeInterval._2} + '$ts'.toTimestamp",
+ s"TIMESTAMPADD($interval, $offset, TIMESTAMP '$ts')",
+ result(i))
+ }
+ }
- testSqlApi("TIMESTAMPADD(HOUR, -200, CAST(NULL AS TIMESTAMP))", "null")
+ testAllApis(
+ "2016-02-24 12:42:25".toTimestamp +
nullOf(DataTypes.INTERVAL(DataTypes.MINUTE())),
+ "'2016-02-24 12:42:25'.toTimestamp + Null(INTERVAL_MILLIS)",
+ "TIMESTAMPADD(HOUR, CAST(NULL AS INTEGER), TIMESTAMP '2016-02-24
12:42:25')",
+ "null")
- testSqlApi("TIMESTAMPADD(DAY, 1, DATE '2016-06-15')", "2016-06-16")
+ testAllApis(
+ nullOf(DataTypes.TIMESTAMP(3)) + -200.hours,
+ "Null(SQL_TIMESTAMP) + -200.hours",
+ "TIMESTAMPADD(HOUR, -200, CAST(NULL AS TIMESTAMP))",
+ "null")
- testSqlApi("TIMESTAMPADD(MONTH, 3, CAST(NULL AS TIMESTAMP))", "null")
+ testAllApis(
+ "2016-06-15".toDate + 1.day,
+ "'2016-06-15'.toDate + 1.day",
+ "TIMESTAMPADD(DAY, 1, DATE '2016-06-15')",
+ "2016-06-16")
+ testAllApis(
+ nullOf(DataTypes.TIMESTAMP(3)) + 3.months,
+ "Null(SQL_TIMESTAMP) + 3.months",
+ "TIMESTAMPADD(MONTH, 3, CAST(NULL AS TIMESTAMP))",
+ "null")
Review comment:
It seems that we missed some tests under this?
This is from flink planner expression test:
```scala
testAllApis(
"2016-02-24 12:42:25".toTimestamp + nullOf(Types.INTERVAL_MILLIS),
"'2016-02-24 12:42:25'.toTimestamp + nullOf(INTERVAL_MILLIS)",
"TIMESTAMPADD(HOUR, CAST(NULL AS INTEGER), TIMESTAMP '2016-02-24
12:42:25')",
"null")
testAllApis(
nullOf(Types.SQL_TIMESTAMP) + -200.hours,
"nullOf(SQL_TIMESTAMP) + -200.hours",
"TIMESTAMPADD(HOUR, -200, CAST(NULL AS TIMESTAMP))",
"null")
testAllApis(
nullOf(Types.SQL_TIMESTAMP) + 3.months,
"nullOf(SQL_TIMESTAMP) + 3.months",
"TIMESTAMPADD(MONTH, 3, CAST(NULL AS TIMESTAMP))",
"null")
// TIMESTAMPADD with DATE returns a TIMESTAMP value for sub-day
intervals.
testAllApis("2016-06-15".toDate + 1.month,
"'2016-06-15'.toDate + 1.month",
"timestampadd(MONTH, 1, date '2016-06-15')",
"2016-07-15")
testAllApis("2016-06-15".toDate + 1.day,
"'2016-06-15'.toDate + 1.day",
"timestampadd(DAY, 1, date '2016-06-15')",
"2016-06-16")
testAllApis("2016-06-15".toTimestamp - 1.hour,
"'2016-06-15'.toTimestamp - 1.hour",
"timestampadd(HOUR, -1, date '2016-06-15')",
"2016-06-14 23:00:00.0")
testAllApis("2016-06-15".toTimestamp + 1.minute,
"'2016-06-15'.toTimestamp + 1.minute",
"timestampadd(MINUTE, 1, date '2016-06-15')",
"2016-06-15 00:01:00.0")
testAllApis("2016-06-15".toTimestamp - 1.second,
"'2016-06-15'.toTimestamp - 1.second",
"timestampadd(SQL_TSI_SECOND, -1, date '2016-06-15')",
"2016-06-14 23:59:59.0")
testAllApis("2016-06-15".toTimestamp + 1.second,
"'2016-06-15'.toTimestamp + 1.second",
"timestampadd(SECOND, 1, date '2016-06-15')",
"2016-06-15 00:00:01.0")
testAllApis(nullOf(Types.SQL_TIMESTAMP) + 1.second,
"nullOf(SQL_TIMESTAMP) + 1.second",
"timestampadd(SECOND, 1, cast(null as date))",
"null")
testAllApis(nullOf(Types.SQL_TIMESTAMP) + 1.day,
"nullOf(SQL_TIMESTAMP) + 1.day",
"timestampadd(DAY, 1, cast(null as date))",
"null")
// Round to the last day of previous month
testAllApis("2016-05-31".toDate + 1.month,
"'2016-05-31'.toDate + 1.month",
"timestampadd(MONTH, 1, date '2016-05-31')",
"2016-06-30")
testAllApis("2016-01-31".toDate + 5.month,
"'2016-01-31'.toDate + 5.month",
"timestampadd(MONTH, 5, date '2016-01-31')",
"2016-06-30")
testAllApis("2016-03-31".toDate - 1.month,
"'2016-03-31'.toDate - 1.month",
"timestampadd(MONTH, -1, date '2016-03-31')",
"2016-02-29")
testAllApis("2016-03-31".toDate - 1.week,
"'2016-03-31'.toDate - 1.week",
"timestampadd(WEEK, -1, date '2016-03-31')",
"2016-03-24")
```
----------------------------------------------------------------
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
With regards,
Apache Git Services
