You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by do...@apache.org on 2019/07/11 01:02:02 UTC
[spark] branch master updated: [SPARK-28107][SQL] Support 'DAY TO
(HOUR|MINUTE|SECOND)', 'HOUR TO (MINUTE|SECOND)' and 'MINUTE TO SECOND'
This is an automated email from the ASF dual-hosted git repository.
dongjoon pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new d26642d [SPARK-28107][SQL] Support 'DAY TO (HOUR|MINUTE|SECOND)', 'HOUR TO (MINUTE|SECOND)' and 'MINUTE TO SECOND'
d26642d is described below
commit d26642dbbc92cfbbe1ed7407b8c2a60ecf7d2104
Author: Zhu, Lipeng <li...@ebay.com>
AuthorDate: Wed Jul 10 18:01:42 2019 -0700
[SPARK-28107][SQL] Support 'DAY TO (HOUR|MINUTE|SECOND)', 'HOUR TO (MINUTE|SECOND)' and 'MINUTE TO SECOND'
## What changes were proposed in this pull request?
The interval conversion behavior is same with the PostgreSQL.
https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/interval.sql#L180-L203
## How was this patch tested?
UT.
Closes #25000 from lipzhu/SPARK-28107.
Lead-authored-by: Zhu, Lipeng <li...@ebay.com>
Co-authored-by: Dongjoon Hyun <dh...@apple.com>
Co-authored-by: Lipeng Zhu <li...@icloud.com>
Signed-off-by: Dongjoon Hyun <dh...@apple.com>
---
.../spark/unsafe/types/CalendarInterval.java | 54 +++++++++++++++++++---
.../spark/unsafe/types/CalendarIntervalSuite.java | 8 ++++
.../spark/sql/catalyst/parser/AstBuilder.scala | 15 ++++--
.../spark/sql/hive/execution/SQLQuerySuite.scala | 19 ++++++++
4 files changed, 87 insertions(+), 9 deletions(-)
diff --git a/common/unsafe/src/main/java/org/apache/spark/unsafe/types/CalendarInterval.java b/common/unsafe/src/main/java/org/apache/spark/unsafe/types/CalendarInterval.java
index 3438f5f..908ff19 100644
--- a/common/unsafe/src/main/java/org/apache/spark/unsafe/types/CalendarInterval.java
+++ b/common/unsafe/src/main/java/org/apache/spark/unsafe/types/CalendarInterval.java
@@ -54,8 +54,8 @@ public final class CalendarInterval implements Serializable {
private static Pattern yearMonthPattern =
Pattern.compile("^(?:['|\"])?([+|-])?(\\d+)-(\\d+)(?:['|\"])?$");
- private static Pattern dayTimePattern =
- Pattern.compile("^(?:['|\"])?([+|-])?((\\d+) )?(\\d+):(\\d+):(\\d+)(\\.(\\d+))?(?:['|\"])?$");
+ private static Pattern dayTimePattern = Pattern.compile(
+ "^(?:['|\"])?([+|-])?((\\d+) )?((\\d+):)?(\\d+):(\\d+)(\\.(\\d+))?(?:['|\"])?$");
private static Pattern quoteTrimPattern = Pattern.compile("^(?:['|\"])?(.*?)(?:['|\"])?$");
@@ -160,6 +160,20 @@ public final class CalendarInterval implements Serializable {
* adapted from HiveIntervalDayTime.valueOf
*/
public static CalendarInterval fromDayTimeString(String s) throws IllegalArgumentException {
+ return fromDayTimeString(s, "day", "second");
+ }
+
+ /**
+ * Parse dayTime string in form: [-]d HH:mm:ss.nnnnnnnnn and [-]HH:mm:ss.nnnnnnnnn
+ *
+ * adapted from HiveIntervalDayTime.valueOf.
+ * Below interval conversion patterns are supported:
+ * - DAY TO (HOUR|MINUTE|SECOND)
+ * - HOUR TO (MINUTE|SECOND)
+ * - MINUTE TO SECOND
+ */
+ public static CalendarInterval fromDayTimeString(String s, String from, String to)
+ throws IllegalArgumentException {
CalendarInterval result = null;
if (s == null) {
throw new IllegalArgumentException("Interval day-time string was null");
@@ -174,12 +188,40 @@ public final class CalendarInterval implements Serializable {
int sign = m.group(1) != null && m.group(1).equals("-") ? -1 : 1;
long days = m.group(2) == null ? 0 : toLongWithRange("day", m.group(3),
0, Integer.MAX_VALUE);
- long hours = toLongWithRange("hour", m.group(4), 0, 23);
- long minutes = toLongWithRange("minute", m.group(5), 0, 59);
- long seconds = toLongWithRange("second", m.group(6), 0, 59);
+ long hours = 0;
+ long minutes;
+ long seconds = 0;
+ if (m.group(5) != null || from.equals("minute")) { // 'HH:mm:ss' or 'mm:ss minute'
+ hours = toLongWithRange("hour", m.group(5), 0, 23);
+ minutes = toLongWithRange("minute", m.group(6), 0, 59);
+ seconds = toLongWithRange("second", m.group(7), 0, 59);
+ } else if (m.group(8) != null){ // 'mm:ss.nn'
+ minutes = toLongWithRange("minute", m.group(6), 0, 59);
+ seconds = toLongWithRange("second", m.group(7), 0, 59);
+ } else { // 'HH:mm'
+ hours = toLongWithRange("hour", m.group(6), 0, 23);
+ minutes = toLongWithRange("second", m.group(7), 0, 59);
+ }
// Hive allow nanosecond precision interval
- String nanoStr = m.group(8) == null ? null : (m.group(8) + "000000000").substring(0, 9);
+ String nanoStr = m.group(9) == null ? null : (m.group(9) + "000000000").substring(0, 9);
long nanos = toLongWithRange("nanosecond", nanoStr, 0L, 999999999L);
+ switch (to) {
+ case "hour":
+ minutes = 0;
+ seconds = 0;
+ nanos = 0;
+ break;
+ case "minute":
+ seconds = 0;
+ nanos = 0;
+ break;
+ case "second":
+ // No-op
+ break;
+ default:
+ throw new IllegalArgumentException(
+ String.format("Cannot support (interval '%s' %s to %s) expression", s, from, to));
+ }
result = new CalendarInterval(0, sign * (
days * MICROS_PER_DAY + hours * MICROS_PER_HOUR + minutes * MICROS_PER_MINUTE +
seconds * MICROS_PER_SECOND + nanos / 1000L));
diff --git a/common/unsafe/src/test/java/org/apache/spark/unsafe/types/CalendarIntervalSuite.java b/common/unsafe/src/test/java/org/apache/spark/unsafe/types/CalendarIntervalSuite.java
index c125ba5..c307d74 100644
--- a/common/unsafe/src/test/java/org/apache/spark/unsafe/types/CalendarIntervalSuite.java
+++ b/common/unsafe/src/test/java/org/apache/spark/unsafe/types/CalendarIntervalSuite.java
@@ -185,6 +185,14 @@ public class CalendarIntervalSuite {
} catch (IllegalArgumentException e) {
assertTrue(e.getMessage().contains("not match day-time format"));
}
+
+ try {
+ input = "5 1:12:20";
+ fromDayTimeString(input, "hour", "microsecond");
+ fail("Expected to throw an exception for the invalid convention type");
+ } catch (IllegalArgumentException e) {
+ assertTrue(e.getMessage().contains("Cannot support (interval"));
+ }
}
@Test
diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
index 6c5ad55..d9f8b9a 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
@@ -1851,7 +1851,8 @@ class AstBuilder(conf: SQLConf) extends SqlBaseBaseVisitor[AnyRef] with Logging
* Create a [[CalendarInterval]] for a unit value pair. Two unit configuration types are
* supported:
* - Single unit.
- * - From-To unit (only 'YEAR TO MONTH' and 'DAY TO SECOND' and 'HOUR to SECOND' are supported).
+ * - From-To unit ('YEAR TO MONTH', 'DAY TO HOUR', 'DAY TO MINUTE', 'DAY TO SECOND',
+ * 'HOUR TO MINUTE', 'HOUR TO SECOND' and 'MINUTE TO SECOND' are supported).
*/
override def visitIntervalField(ctx: IntervalFieldContext): CalendarInterval = withOrigin(ctx) {
import ctx._
@@ -1866,10 +1867,18 @@ class AstBuilder(conf: SQLConf) extends SqlBaseBaseVisitor[AnyRef] with Logging
CalendarInterval.fromSingleUnitString(u, s)
case ("year", Some("month")) =>
CalendarInterval.fromYearMonthString(s)
+ case ("day", Some("hour")) =>
+ CalendarInterval.fromDayTimeString(s, "day", "hour")
+ case ("day", Some("minute")) =>
+ CalendarInterval.fromDayTimeString(s, "day", "minute")
case ("day", Some("second")) =>
- CalendarInterval.fromDayTimeString(s)
+ CalendarInterval.fromDayTimeString(s, "day", "second")
+ case ("hour", Some("minute")) =>
+ CalendarInterval.fromDayTimeString(s, "hour", "minute")
case ("hour", Some("second")) =>
- CalendarInterval.fromDayTimeString(s)
+ CalendarInterval.fromDayTimeString(s, "hour", "second")
+ case ("minute", Some("second")) =>
+ CalendarInterval.fromDayTimeString(s, "minute", "second")
case (from, Some(t)) =>
throw new ParseException(s"Intervals FROM $from TO $t are not supported.", ctx)
}
diff --git a/sql/hive/src/test/scala/org/apache/spark/sql/hive/execution/SQLQuerySuite.scala b/sql/hive/src/test/scala/org/apache/spark/sql/hive/execution/SQLQuerySuite.scala
index dc58c2d..fd505d1 100644
--- a/sql/hive/src/test/scala/org/apache/spark/sql/hive/execution/SQLQuerySuite.scala
+++ b/sql/hive/src/test/scala/org/apache/spark/sql/hive/execution/SQLQuerySuite.scala
@@ -1183,12 +1183,31 @@ class SQLQuerySuite extends QueryTest with SQLTestUtils with TestHiveSingleton {
Row(CalendarInterval.fromString("interval 100 milliseconds")))
checkAnswer(sql("select interval '10-9' year to month"),
Row(CalendarInterval.fromString("interval 10 years 9 months")))
+ checkAnswer(sql("select interval '20 15:40:32.99899999' day to hour"),
+ Row(CalendarInterval.fromString("interval 2 weeks 6 days 15 hours")))
+ checkAnswer(sql("select interval '20 15:40:32.99899999' day to minute"),
+ Row(CalendarInterval.fromString("interval 2 weeks 6 days 15 hours 40 minutes")))
checkAnswer(sql("select interval '20 15:40:32.99899999' day to second"),
Row(CalendarInterval.fromString("interval 2 weeks 6 days 15 hours 40 minutes " +
"32 seconds 998 milliseconds 999 microseconds")))
+ checkAnswer(sql("select interval '15:40:32.99899999' hour to minute"),
+ Row(CalendarInterval.fromString("interval 15 hours 40 minutes")))
+ checkAnswer(sql("select interval '15:40.99899999' hour to second"),
+ Row(CalendarInterval.fromString("interval 15 minutes 40 seconds 998 milliseconds " +
+ "999 microseconds")))
+ checkAnswer(sql("select interval '15:40' hour to second"),
+ Row(CalendarInterval.fromString("interval 15 hours 40 minutes")))
checkAnswer(sql("select interval '15:40:32.99899999' hour to second"),
Row(CalendarInterval.fromString("interval 15 hours 40 minutes 32 seconds 998 milliseconds " +
"999 microseconds")))
+ checkAnswer(sql("select interval '20 40:32.99899999' minute to second"),
+ Row(CalendarInterval.fromString("interval 2 weeks 6 days 40 minutes 32 seconds " +
+ "998 milliseconds 999 microseconds")))
+ checkAnswer(sql("select interval '40:32.99899999' minute to second"),
+ Row(CalendarInterval.fromString("interval 40 minutes 32 seconds 998 milliseconds " +
+ "999 microseconds")))
+ checkAnswer(sql("select interval '40:32' minute to second"),
+ Row(CalendarInterval.fromString("interval 40 minutes 32 seconds")))
checkAnswer(sql("select interval '30' year"),
Row(CalendarInterval.fromString("interval 30 years")))
checkAnswer(sql("select interval '25' month"),
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org