This is an automated email from the ASF dual-hosted git repository.
huaxingao 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 f361ad8 [SPARK-37219][SQL][FOLLOWUP] Make AS OF syntax more flexible
f361ad8 is described below
commit f361ad8d2eb067ec86483a294771f1976f31a981
Author: Huaxin Gao <huaxin_...@apple.com>
AuthorDate: Thu Nov 18 15:58:25 2021 -0800
[SPARK-37219][SQL][FOLLOWUP] Make AS OF syntax more flexible
### What changes were proposed in this pull request?
Make `AS OF` syntax more flexible
### Why are the changes needed?
`((FOR SYSTEM_VERSION) | VERSION)` -> `FOR? (SYSTEM_VERSION | VERSION) AS
OF`
### Does this PR introduce _any_ user-facing change?
Yes
### How was this patch tested?
Existing tests
Closes #34621 from huaxingao/asof_followup.
Authored-by: Huaxin Gao <huaxin_...@apple.com>
Signed-off-by: Huaxin Gao <huaxin_...@apple.com>
---
.../apache/spark/sql/catalyst/parser/SqlBase.g4 | 4 +-
.../spark/sql/catalyst/parser/DDLParserSuite.scala | 50 ++++++++++++++++++++++
.../spark/sql/connector/DataSourceV2SQLSuite.scala | 18 ++++++++
3 files changed, 70 insertions(+), 2 deletions(-)
diff --git
a/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4
b/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4
index e11f737..426f529 100644
---
a/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4
+++
b/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4
@@ -600,8 +600,8 @@ fromClause
;
temporalClause
- : ((FOR SYSTEM_VERSION) | VERSION) AS OF version=(INTEGER_VALUE | STRING)
- | ((FOR SYSTEM_TIME) | TIMESTAMP) AS OF timestamp=STRING
+ : FOR? (SYSTEM_VERSION | VERSION) AS OF version=(INTEGER_VALUE | STRING)
+ | FOR? (SYSTEM_TIME | TIMESTAMP) AS OF timestamp=STRING
;
aggregationClause
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/DDLParserSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/DDLParserSuite.scala
index 6efff21..62b611a 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/DDLParserSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/DDLParserSuite.scala
@@ -2440,6 +2440,14 @@ class DDLParserSuite extends AnalysisTest {
new CaseInsensitiveStringMap(properties),
timeTravelSpec = timeTravel)))
+ comparePlans(
+ parsePlan("SELECT * FROM a.b.c FOR VERSION AS OF 'Snapshot123456789'"),
+ Project(Seq(UnresolvedStar(None)),
+ UnresolvedRelation(
+ Seq("a", "b", "c"),
+ new CaseInsensitiveStringMap(properties),
+ timeTravelSpec = timeTravel)))
+
timeTravel = TimeTravelSpec.create(None, Some("123456789"))
comparePlans(
parsePlan("SELECT * FROM a.b.c FOR SYSTEM_VERSION AS OF 123456789"),
@@ -2449,6 +2457,14 @@ class DDLParserSuite extends AnalysisTest {
new CaseInsensitiveStringMap(properties),
timeTravelSpec = timeTravel)))
+ comparePlans(
+ parsePlan("SELECT * FROM a.b.c SYSTEM_VERSION AS OF 123456789"),
+ Project(Seq(UnresolvedStar(None)),
+ UnresolvedRelation(
+ Seq("a", "b", "c"),
+ new CaseInsensitiveStringMap(properties),
+ timeTravelSpec = timeTravel)))
+
timeTravel = TimeTravelSpec.create(Some("2019-01-29 00:37:58"), None)
comparePlans(
parsePlan("SELECT * FROM a.b.c TIMESTAMP AS OF '2019-01-29 00:37:58'"),
@@ -2457,6 +2473,15 @@ class DDLParserSuite extends AnalysisTest {
Seq("a", "b", "c"),
new CaseInsensitiveStringMap(properties),
timeTravelSpec = timeTravel)))
+
+ comparePlans(
+ parsePlan("SELECT * FROM a.b.c FOR TIMESTAMP AS OF '2019-01-29
00:37:58'"),
+ Project(Seq(UnresolvedStar(None)),
+ UnresolvedRelation(
+ Seq("a", "b", "c"),
+ new CaseInsensitiveStringMap(properties),
+ timeTravelSpec = timeTravel)))
+
comparePlans(
parsePlan("SELECT * FROM a.b.c FOR SYSTEM_TIME AS OF '2019-01-29
00:37:58'"),
Project(Seq(UnresolvedStar(None)),
@@ -2465,6 +2490,14 @@ class DDLParserSuite extends AnalysisTest {
new CaseInsensitiveStringMap(properties),
timeTravelSpec = timeTravel)))
+ comparePlans(
+ parsePlan("SELECT * FROM a.b.c SYSTEM_TIME AS OF '2019-01-29 00:37:58'"),
+ Project(Seq(UnresolvedStar(None)),
+ UnresolvedRelation(
+ Seq("a", "b", "c"),
+ new CaseInsensitiveStringMap(properties),
+ timeTravelSpec = timeTravel)))
+
timeTravel = TimeTravelSpec.create(Some("2019-01-29"), None)
comparePlans(
parsePlan("SELECT * FROM a.b.c TIMESTAMP AS OF '2019-01-29'"),
@@ -2473,6 +2506,15 @@ class DDLParserSuite extends AnalysisTest {
Seq("a", "b", "c"),
new CaseInsensitiveStringMap(properties),
timeTravelSpec = timeTravel)))
+
+ comparePlans(
+ parsePlan("SELECT * FROM a.b.c FOR TIMESTAMP AS OF '2019-01-29'"),
+ Project(Seq(UnresolvedStar(None)),
+ UnresolvedRelation(
+ Seq("a", "b", "c"),
+ new CaseInsensitiveStringMap(properties),
+ timeTravelSpec = timeTravel)))
+
comparePlans(
parsePlan("SELECT * FROM a.b.c FOR SYSTEM_TIME AS OF '2019-01-29'"),
Project(Seq(UnresolvedStar(None)),
@@ -2481,6 +2523,14 @@ class DDLParserSuite extends AnalysisTest {
new CaseInsensitiveStringMap(properties),
timeTravelSpec = timeTravel)))
+ comparePlans(
+ parsePlan("SELECT * FROM a.b.c SYSTEM_TIME AS OF '2019-01-29'"),
+ Project(Seq(UnresolvedStar(None)),
+ UnresolvedRelation(
+ Seq("a", "b", "c"),
+ new CaseInsensitiveStringMap(properties),
+ timeTravelSpec = timeTravel)))
+
val e1 = intercept[DateTimeException] {
parsePlan("SELECT * FROM a.b.c TIMESTAMP AS OF '2019-01-11111'")
}.getMessage
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/connector/DataSourceV2SQLSuite.scala
b/sql/core/src/test/scala/org/apache/spark/sql/connector/DataSourceV2SQLSuite.scala
index f241b8a..6cbbf68 100644
---
a/sql/core/src/test/scala/org/apache/spark/sql/connector/DataSourceV2SQLSuite.scala
+++
b/sql/core/src/test/scala/org/apache/spark/sql/connector/DataSourceV2SQLSuite.scala
@@ -2910,10 +2910,19 @@ class DataSourceV2SQLSuite
=== Array(Row(1), Row(2)))
assert(sql("SELECT * FROM t VERSION AS OF 2345678910").collect
=== Array(Row(3), Row(4)))
+ assert(sql("SELECT * FROM t FOR VERSION AS OF
'Snapshot123456789'").collect
+ === Array(Row(1), Row(2)))
+ assert(sql("SELECT * FROM t FOR VERSION AS OF 2345678910").collect
+ === Array(Row(3), Row(4)))
+
assert(sql("SELECT * FROM t FOR SYSTEM_VERSION AS OF
'Snapshot123456789'").collect
=== Array(Row(1), Row(2)))
assert(sql("SELECT * FROM t FOR SYSTEM_VERSION AS OF 2345678910").collect
=== Array(Row(3), Row(4)))
+ assert(sql("SELECT * FROM t SYSTEM_VERSION AS OF
'Snapshot123456789'").collect
+ === Array(Row(1), Row(2)))
+ assert(sql("SELECT * FROM t SYSTEM_VERSION AS OF 2345678910").collect
+ === Array(Row(3), Row(4)))
}
val ts1 = DateTimeUtils.stringToTimestampAnsi(
@@ -2938,10 +2947,19 @@ class DataSourceV2SQLSuite
=== Array(Row(5), Row(6)))
assert(sql("SELECT * FROM t TIMESTAMP AS OF '2021-01-29
00:37:58'").collect
=== Array(Row(7), Row(8)))
+ assert(sql("SELECT * FROM t FOR TIMESTAMP AS OF '2019-01-29
00:37:58'").collect
+ === Array(Row(5), Row(6)))
+ assert(sql("SELECT * FROM t FOR TIMESTAMP AS OF '2021-01-29
00:37:58'").collect
+ === Array(Row(7), Row(8)))
+
assert(sql("SELECT * FROM t FOR SYSTEM_TIME AS OF '2019-01-29
00:37:58'").collect
=== Array(Row(5), Row(6)))
assert(sql("SELECT * FROM t FOR SYSTEM_TIME AS OF '2021-01-29
00:37:58'").collect
=== Array(Row(7), Row(8)))
+ assert(sql("SELECT * FROM t SYSTEM_TIME AS OF '2019-01-29
00:37:58'").collect
+ === Array(Row(5), Row(6)))
+ assert(sql("SELECT * FROM t SYSTEM_TIME AS OF '2021-01-29
00:37:58'").collect
+ === Array(Row(7), Row(8)))
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@spark.apache.org
For additional commands, e-mail: commits-h...@spark.apache.org
