You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by li...@apache.org on 2016/10/06 23:09:57 UTC
spark git commit: [SPARK-17750][SQL][BACKPORT-2.0] Fix CREATE VIEW
with INTERVAL arithmetic
Repository: spark
Updated Branches:
refs/heads/branch-2.0 e355ca8e8 -> b1a9c41e8
[SPARK-17750][SQL][BACKPORT-2.0] Fix CREATE VIEW with INTERVAL arithmetic
## What changes were proposed in this pull request?
Currently, Spark raises `RuntimeException` when creating a view with timestamp with INTERVAL arithmetic like the following. The root cause is the arithmetic expression, `TimeAdd`, was transformed into `timeadd` function as a VIEW definition. This PR fixes the SQL definition of `TimeAdd` and `TimeSub` expressions.
```scala
scala> sql("CREATE TABLE dates (ts TIMESTAMP)")
scala> sql("CREATE VIEW view1 AS SELECT ts + INTERVAL 1 DAY FROM dates")
java.lang.RuntimeException: Failed to analyze the canonicalized SQL: ...
```
## How was this patch tested?
Pass Jenkins with a new testcase.
Author: Dongjoon Hyun <do...@apache.org>
Closes #15383 from dongjoon-hyun/SPARK-17750-BACK.
Project: http://git-wip-us.apache.org/repos/asf/spark/repo
Commit: http://git-wip-us.apache.org/repos/asf/spark/commit/b1a9c41e
Tree: http://git-wip-us.apache.org/repos/asf/spark/tree/b1a9c41e
Diff: http://git-wip-us.apache.org/repos/asf/spark/diff/b1a9c41e
Branch: refs/heads/branch-2.0
Commit: b1a9c41e8c41c90dd15ee6f635356dd1a5bbf395
Parents: e355ca8
Author: Dongjoon Hyun <do...@apache.org>
Authored: Thu Oct 6 16:09:45 2016 -0700
Committer: gatorsmile <ga...@gmail.com>
Committed: Thu Oct 6 16:09:45 2016 -0700
----------------------------------------------------------------------
.../expressions/datetimeExpressions.scala | 2 ++
.../test/resources/sqlgen/interval_arithmetic.sql | 8 ++++++++
.../sql/catalyst/ExpressionSQLBuilderSuite.scala | 18 +++++++++++++++++-
.../sql/catalyst/LogicalPlanToSQLSuite.scala | 16 ++++++++++++++++
4 files changed, 43 insertions(+), 1 deletion(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/spark/blob/b1a9c41e/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala
----------------------------------------------------------------------
diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala
index 04c17bd..7ab68a1 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala
@@ -682,6 +682,7 @@ case class TimeAdd(start: Expression, interval: Expression)
override def right: Expression = interval
override def toString: String = s"$left + $right"
+ override def sql: String = s"${left.sql} + ${right.sql}"
override def inputTypes: Seq[AbstractDataType] = Seq(TimestampType, CalendarIntervalType)
override def dataType: DataType = TimestampType
@@ -762,6 +763,7 @@ case class TimeSub(start: Expression, interval: Expression)
override def right: Expression = interval
override def toString: String = s"$left - $right"
+ override def sql: String = s"${left.sql} - ${right.sql}"
override def inputTypes: Seq[AbstractDataType] = Seq(TimestampType, CalendarIntervalType)
override def dataType: DataType = TimestampType
http://git-wip-us.apache.org/repos/asf/spark/blob/b1a9c41e/sql/hive/src/test/resources/sqlgen/interval_arithmetic.sql
----------------------------------------------------------------------
diff --git a/sql/hive/src/test/resources/sqlgen/interval_arithmetic.sql b/sql/hive/src/test/resources/sqlgen/interval_arithmetic.sql
new file mode 100644
index 0000000..31d0034
--- /dev/null
+++ b/sql/hive/src/test/resources/sqlgen/interval_arithmetic.sql
@@ -0,0 +1,8 @@
+-- This file is automatically generated by LogicalPlanToSQLSuite.
+select ts + interval 1 day, ts + interval 2 days,
+ ts - interval 1 day, ts - interval 2 days,
+ ts + interval '1' day, ts + interval '2' days,
+ ts - interval '1' day, ts - interval '2' days
+from dates
+--------------------------------------------------------------------------------
+SELECT `gen_attr_0` AS `CAST(ts + interval 1 days AS TIMESTAMP)`, `gen_attr_2` AS `CAST(ts + interval 2 days AS TIMESTAMP)`, `gen_attr_3` AS `CAST(ts - interval 1 days AS TIMESTAMP)`, `gen_attr_4` AS `CAST(ts - interval 2 days AS TIMESTAMP)`, `gen_attr_5` AS `CAST(ts + interval 1 days AS TIMESTAMP)`, `gen_attr_6` AS `CAST(ts + interval 2 days AS TIMESTAMP)`, `gen_attr_7` AS `CAST(ts - interval 1 days AS TIMESTAMP)`, `gen_attr_8` AS `CAST(ts - interval 2 days AS TIMESTAMP)` FROM (SELECT CAST(`gen_attr_1` + interval 1 days AS TIMESTAMP) AS `gen_attr_0`, CAST(`gen_attr_1` + interval 2 days AS TIMESTAMP) AS `gen_attr_2`, CAST(`gen_attr_1` - interval 1 days AS TIMESTAMP) AS `gen_attr_3`, CAST(`gen_attr_1` - interval 2 days AS TIMESTAMP) AS `gen_attr_4`, CAST(`gen_attr_1` + interval 1 days AS TIMESTAMP) AS `gen_attr_5`, CAST(`gen_attr_1` + interval 2 days AS TIMESTAMP) AS `gen_attr_6`, CAST(`gen_attr_1` - interval 1 days AS TIMESTAMP) AS `gen_attr_7`, CAST(`gen_attr_1` - interval 2 days A
S TIMESTAMP) AS `gen_attr_8` FROM (SELECT `ts` AS `gen_attr_1` FROM `default`.`dates`) AS gen_subquery_0) AS gen_subquery_1
http://git-wip-us.apache.org/repos/asf/spark/blob/b1a9c41e/sql/hive/src/test/scala/org/apache/spark/sql/catalyst/ExpressionSQLBuilderSuite.scala
----------------------------------------------------------------------
diff --git a/sql/hive/src/test/scala/org/apache/spark/sql/catalyst/ExpressionSQLBuilderSuite.scala b/sql/hive/src/test/scala/org/apache/spark/sql/catalyst/ExpressionSQLBuilderSuite.scala
index 43a218b..56c0736 100644
--- a/sql/hive/src/test/scala/org/apache/spark/sql/catalyst/ExpressionSQLBuilderSuite.scala
+++ b/sql/hive/src/test/scala/org/apache/spark/sql/catalyst/ExpressionSQLBuilderSuite.scala
@@ -20,7 +20,9 @@ package org.apache.spark.sql.catalyst
import java.sql.Timestamp
import org.apache.spark.sql.catalyst.dsl.expressions._
-import org.apache.spark.sql.catalyst.expressions.{If, Literal, SpecifiedWindowFrame, WindowSpecDefinition}
+import org.apache.spark.sql.catalyst.expressions.{If, Literal, SpecifiedWindowFrame, TimeAdd,
+ TimeSub, WindowSpecDefinition}
+import org.apache.spark.unsafe.types.CalendarInterval
class ExpressionSQLBuilderSuite extends SQLBuilderTest {
test("literal") {
@@ -118,4 +120,18 @@ class ExpressionSQLBuilderSuite extends SQLBuilderTest {
s"(PARTITION BY `a`, `b` ORDER BY `c` ASC, `d` DESC $frame)"
)
}
+
+ test("interval arithmetic") {
+ val interval = Literal(new CalendarInterval(0, CalendarInterval.MICROS_PER_DAY))
+
+ checkSQL(
+ TimeAdd('a, interval),
+ "`a` + interval 1 days"
+ )
+
+ checkSQL(
+ TimeSub('a, interval),
+ "`a` - interval 1 days"
+ )
+ }
}
http://git-wip-us.apache.org/repos/asf/spark/blob/b1a9c41e/sql/hive/src/test/scala/org/apache/spark/sql/catalyst/LogicalPlanToSQLSuite.scala
----------------------------------------------------------------------
diff --git a/sql/hive/src/test/scala/org/apache/spark/sql/catalyst/LogicalPlanToSQLSuite.scala b/sql/hive/src/test/scala/org/apache/spark/sql/catalyst/LogicalPlanToSQLSuite.scala
index ef2f756..3794f63 100644
--- a/sql/hive/src/test/scala/org/apache/spark/sql/catalyst/LogicalPlanToSQLSuite.scala
+++ b/sql/hive/src/test/scala/org/apache/spark/sql/catalyst/LogicalPlanToSQLSuite.scala
@@ -1121,4 +1121,20 @@ class LogicalPlanToSQLSuite extends SQLBuilderTest with SQLTestUtils {
""".stripMargin,
"inline_tables")
}
+
+ test("SPARK-17750 - interval arithmetic") {
+ withTable("dates") {
+ sql("create table dates (ts timestamp)")
+ checkSQL(
+ """
+ |select ts + interval 1 day, ts + interval 2 days,
+ | ts - interval 1 day, ts - interval 2 days,
+ | ts + interval '1' day, ts + interval '2' days,
+ | ts - interval '1' day, ts - interval '2' days
+ |from dates
+ """.stripMargin,
+ "interval_arithmetic"
+ )
+ }
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org