xianyinxin commented on a change in pull request #26167: [SPARK-28893][SQL]
Support MERGE INTO in the parser and add the corresponding logical plan
URL: https://github.com/apache/spark/pull/26167#discussion_r344010734
##########
File path:
sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/DDLParserSuite.scala
##########
@@ -836,6 +836,196 @@ class DDLParserSuite extends AnalysisTest {
assert(exc.getMessage.contains("Columns aliases is not allowed in
UPDATE."))
}
+ test("merge into table: basic") {
+ parseCompare(
+ """
+ |MERGE INTO testcat1.ns1.ns2.tbl AS target
+ |USING testcat2.ns1.ns2.tbl AS source
+ |ON target.col1 = source.col1
+ |WHEN MATCHED AND (target.col2='delete') THEN DELETE
+ |WHEN MATCHED AND (target.col2='update') THEN UPDATE SET target.col2 =
source.col2
+ |WHEN NOT MATCHED AND (target.col2='insert')
+ |THEN INSERT (target.col1, target.col2) values (source.col1,
source.col2)
+ """.stripMargin,
+ MergeIntoTable(
+ SubqueryAlias("target", UnresolvedRelation(Seq("testcat1", "ns1",
"ns2", "tbl"))),
+ SubqueryAlias("source", UnresolvedRelation(Seq("testcat2", "ns1",
"ns2", "tbl"))),
+ EqualTo(UnresolvedAttribute("target.col1"),
UnresolvedAttribute("source.col1")),
+ Seq(DeleteAction(Some(EqualTo(UnresolvedAttribute("target.col2"),
Literal("delete")))),
+ UpdateAction(Some(EqualTo(UnresolvedAttribute("target.col2"),
Literal("update"))),
+ Seq(Assignment(UnresolvedAttribute("target.col2"),
+ UnresolvedAttribute("source.col2"))))),
+ Seq(InsertAction(Some(EqualTo(UnresolvedAttribute("target.col2"),
Literal("insert"))),
+ Seq(Assignment(UnresolvedAttribute("target.col1"),
UnresolvedAttribute("source.col1")),
+ Assignment(UnresolvedAttribute("target.col2"),
UnresolvedAttribute("source.col2")))))))
+ }
+
+ test("merge into table: using subquery") {
+ parseCompare(
+ """
+ |MERGE INTO testcat1.ns1.ns2.tbl AS target
+ |USING (SELECT * FROM testcat2.ns1.ns2.tbl) AS source
+ |ON target.col1 = source.col1
+ |WHEN MATCHED AND (target.col2='delete') THEN DELETE
+ |WHEN MATCHED AND (target.col2='update') THEN UPDATE SET target.col2 =
source.col2
+ |WHEN NOT MATCHED AND (target.col2='insert')
+ |THEN INSERT (target.col1, target.col2) values (source.col1,
source.col2)
+ """.stripMargin,
+ MergeIntoTable(
+ SubqueryAlias("target", UnresolvedRelation(Seq("testcat1", "ns1",
"ns2", "tbl"))),
+ SubqueryAlias("source", Project(Seq(UnresolvedStar(None)),
+ UnresolvedRelation(Seq("testcat2", "ns1", "ns2", "tbl")))),
+ EqualTo(UnresolvedAttribute("target.col1"),
UnresolvedAttribute("source.col1")),
+ Seq(DeleteAction(Some(EqualTo(UnresolvedAttribute("target.col2"),
Literal("delete")))),
+ UpdateAction(Some(EqualTo(UnresolvedAttribute("target.col2"),
Literal("update"))),
+ Seq(Assignment(UnresolvedAttribute("target.col2"),
+ UnresolvedAttribute("source.col2"))))),
+ Seq(InsertAction(Some(EqualTo(UnresolvedAttribute("target.col2"),
Literal("insert"))),
+ Seq(Assignment(UnresolvedAttribute("target.col1"),
UnresolvedAttribute("source.col1")),
+ Assignment(UnresolvedAttribute("target.col2"),
UnresolvedAttribute("source.col2")))))))
+ }
+
+ test("merge into table: cte") {
+ parseCompare(
+ """
+ |MERGE INTO testcat1.ns1.ns2.tbl AS target
+ |USING (WITH s as (SELECT * FROM testcat2.ns1.ns2.tbl) SELECT * FROM
s) AS source
+ |ON target.col1 = source.col1
+ |WHEN MATCHED AND (target.col2='delete') THEN DELETE
+ |WHEN MATCHED AND (target.col2='update') THEN UPDATE SET target.col2 =
source.col2
+ |WHEN NOT MATCHED AND (target.col2='insert')
+ |THEN INSERT (target.col1, target.col2) values (source.col1,
source.col2)
+ """.stripMargin,
+ MergeIntoTable(
+ SubqueryAlias("target", UnresolvedRelation(Seq("testcat1", "ns1",
"ns2", "tbl"))),
+ SubqueryAlias("source", With(Project(Seq(UnresolvedStar(None)),
+ UnresolvedRelation(Seq("s"))),
+ Seq("s" -> SubqueryAlias("s", Project(Seq(UnresolvedStar(None)),
+ UnresolvedRelation(Seq("testcat2", "ns1", "ns2", "tbl"))))))),
+ EqualTo(UnresolvedAttribute("target.col1"),
UnresolvedAttribute("source.col1")),
+ Seq(DeleteAction(Some(EqualTo(UnresolvedAttribute("target.col2"),
Literal("delete")))),
+ UpdateAction(Some(EqualTo(UnresolvedAttribute("target.col2"),
Literal("update"))),
+ Seq(Assignment(UnresolvedAttribute("target.col2"),
+ UnresolvedAttribute("source.col2"))))),
+ Seq(InsertAction(Some(EqualTo(UnresolvedAttribute("target.col2"),
Literal("insert"))),
+ Seq(Assignment(UnresolvedAttribute("target.col1"),
UnresolvedAttribute("source.col1")),
+ Assignment(UnresolvedAttribute("target.col2"),
UnresolvedAttribute("source.col2")))))))
+ }
+
+ test("merge into table: no additional condition") {
+ parseCompare(
+ """
+ |MERGE INTO testcat1.ns1.ns2.tbl AS target
+ |USING testcat2.ns1.ns2.tbl AS source
+ |ON target.col1 = source.col1
+ |WHEN MATCHED THEN UPDATE SET target.col2 = source.col2
+ |WHEN NOT MATCHED
+ |THEN INSERT (target.col1, target.col2) values (source.col1,
source.col2)
+ """.stripMargin,
+ MergeIntoTable(
+ SubqueryAlias("target", UnresolvedRelation(Seq("testcat1", "ns1", "ns2",
"tbl"))),
+ SubqueryAlias("source", UnresolvedRelation(Seq("testcat2", "ns1", "ns2",
"tbl"))),
+ EqualTo(UnresolvedAttribute("target.col1"),
UnresolvedAttribute("source.col1")),
+ Seq(UpdateAction(None,
+ Seq(Assignment(UnresolvedAttribute("target.col2"),
UnresolvedAttribute("source.col2"))))),
+ Seq(InsertAction(None,
+ Seq(Assignment(UnresolvedAttribute("target.col1"),
UnresolvedAttribute("source.col1")),
+ Assignment(UnresolvedAttribute("target.col2"),
UnresolvedAttribute("source.col2")))))))
+ }
+
+ test("merge into table: star") {
+ parseCompare(
+ """
+ |MERGE INTO testcat1.ns1.ns2.tbl AS target
+ |USING testcat2.ns1.ns2.tbl AS source
+ |ON target.col1 = source.col1
+ |WHEN MATCHED AND (target.col2='delete') THEN DELETE
+ |WHEN MATCHED AND (target.col2='update') THEN UPDATE SET *
+ |WHEN NOT MATCHED AND (target.col2='insert')
+ |THEN INSERT *
+ """.stripMargin,
+ MergeIntoTable(
+ SubqueryAlias("target", UnresolvedRelation(Seq("testcat1", "ns1", "ns2",
"tbl"))),
+ SubqueryAlias("source", UnresolvedRelation(Seq("testcat2", "ns1", "ns2",
"tbl"))),
+ EqualTo(UnresolvedAttribute("target.col1"),
UnresolvedAttribute("source.col1")),
+ Seq(DeleteAction(Some(EqualTo(UnresolvedAttribute("target.col2"),
Literal("delete")))),
+ UpdateAction(Some(EqualTo(UnresolvedAttribute("target.col2"),
Literal("update"))),
+ Seq(Assignment(UnresolvedStar(None), UnresolvedStar(None))))),
+ Seq(InsertAction(Some(EqualTo(UnresolvedAttribute("target.col2"),
Literal("insert"))),
+ Seq(Assignment(UnresolvedStar(None), UnresolvedStar(None)))))))
+ }
+
+ test("merge into table: columns aliases is not allowed - target table") {
+ val exc = intercept[ParseException] {
+ parsePlan(
+ """
+ |MERGE INTO testcat1.ns1.ns2.tbl AS target(c1, c2)
+ |USING testcat2.ns1.ns2.tbl AS source
+ |ON target.col1 = source.col1
+ |WHEN MATCHED AND (target.col2='delete') THEN DELETE
+ |WHEN MATCHED AND (target.col2='update') THEN UPDATE SET target.col2
= source.col2
+ |WHEN NOT MATCHED AND (target.col2='insert')
+ |THEN INSERT (target.col1, target.col2) values (source.col1,
source.col2)
+ """.stripMargin)
+ }
+
+ assert(exc.getMessage.contains("Columns aliases is not allowed in MERGE."))
+ }
+
+ test("merge into table: columns aliases is not allowed - source table") {
Review comment:
done.
----------------------------------------------------------------
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:
[email protected]
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
