Repository: spark Updated Branches: refs/heads/master bdd537164 -> 41a7dbdd3
[SPARK-10601][SQL] Support `MINUS` set operator ## What changes were proposed in this pull request? This PR adds `MINUS` set operator which is equivalent `EXCEPT DISTINCT`. This will slightly improve the compatibility with Oracle. ## How was this patch tested? Pass the Jenkins with newly added testcases. Author: Dongjoon Hyun <[email protected]> Closes #14570 from dongjoon-hyun/SPARK-10601. Project: http://git-wip-us.apache.org/repos/asf/spark/repo Commit: http://git-wip-us.apache.org/repos/asf/spark/commit/41a7dbdd Tree: http://git-wip-us.apache.org/repos/asf/spark/tree/41a7dbdd Diff: http://git-wip-us.apache.org/repos/asf/spark/diff/41a7dbdd Branch: refs/heads/master Commit: 41a7dbdd34d2641d42eb00828f16285089356aa9 Parents: bdd5371 Author: Dongjoon Hyun <[email protected]> Authored: Wed Aug 10 10:31:30 2016 +0200 Committer: Herman van Hovell <[email protected]> Committed: Wed Aug 10 10:31:30 2016 +0200 ---------------------------------------------------------------------- .../org/apache/spark/sql/catalyst/parser/SqlBase.g4 | 5 +++-- .../org/apache/spark/sql/catalyst/parser/AstBuilder.scala | 5 +++++ .../spark/sql/catalyst/parser/PlanParserSuite.scala | 3 +++ .../test/scala/org/apache/spark/sql/SQLQuerySuite.scala | 10 ++++++++++ 4 files changed, 21 insertions(+), 2 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/spark/blob/41a7dbdd/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4 ---------------------------------------------------------------------- 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 d2b5c53..ba65f2a 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 @@ -313,7 +313,7 @@ multiInsertQueryBody queryTerm : queryPrimary #queryTermDefault - | left=queryTerm operator=(INTERSECT | UNION | EXCEPT) setQuantifier? right=queryTerm #setOperation + | left=queryTerm operator=(INTERSECT | UNION | EXCEPT | SETMINUS) setQuantifier? right=queryTerm #setOperation ; queryPrimary @@ -611,7 +611,7 @@ qualifiedName identifier : strictIdentifier | ANTI | FULL | INNER | LEFT | SEMI | RIGHT | NATURAL | JOIN | CROSS | ON - | UNION | INTERSECT | EXCEPT + | UNION | INTERSECT | EXCEPT | SETMINUS ; strictIdentifier @@ -751,6 +751,7 @@ FUNCTIONS: 'FUNCTIONS'; DROP: 'DROP'; UNION: 'UNION'; EXCEPT: 'EXCEPT'; +SETMINUS: 'MINUS'; INTERSECT: 'INTERSECT'; TO: 'TO'; TABLESAMPLE: 'TABLESAMPLE'; http://git-wip-us.apache.org/repos/asf/spark/blob/41a7dbdd/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala ---------------------------------------------------------------------- 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 679adf2..c7fdc28 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 @@ -410,6 +410,7 @@ class AstBuilder extends SqlBaseBaseVisitor[AnyRef] with Logging { * - UNION [DISTINCT] * - UNION ALL * - EXCEPT [DISTINCT] + * - MINUS [DISTINCT] * - INTERSECT [DISTINCT] */ override def visitSetOperation(ctx: SetOperationContext): LogicalPlan = withOrigin(ctx) { @@ -429,6 +430,10 @@ class AstBuilder extends SqlBaseBaseVisitor[AnyRef] with Logging { throw new ParseException("EXCEPT ALL is not supported.", ctx) case SqlBaseParser.EXCEPT => Except(left, right) + case SqlBaseParser.SETMINUS if all => + throw new ParseException("MINUS ALL is not supported.", ctx) + case SqlBaseParser.SETMINUS => + Except(left, right) } } http://git-wip-us.apache.org/repos/asf/spark/blob/41a7dbdd/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/PlanParserSuite.scala ---------------------------------------------------------------------- diff --git a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/PlanParserSuite.scala b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/PlanParserSuite.scala index fbe236e..00a37cf 100644 --- a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/PlanParserSuite.scala +++ b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/PlanParserSuite.scala @@ -68,6 +68,9 @@ class PlanParserSuite extends PlanTest { assertEqual("select * from a except select * from b", a.except(b)) intercept("select * from a except all select * from b", "EXCEPT ALL is not supported.") assertEqual("select * from a except distinct select * from b", a.except(b)) + assertEqual("select * from a minus select * from b", a.except(b)) + intercept("select * from a minus all select * from b", "MINUS ALL is not supported.") + assertEqual("select * from a minus distinct select * from b", a.except(b)) assertEqual("select * from a intersect select * from b", a.intersect(b)) intercept("select * from a intersect all select * from b", "INTERSECT ALL is not supported.") assertEqual("select * from a intersect distinct select * from b", a.intersect(b)) http://git-wip-us.apache.org/repos/asf/spark/blob/41a7dbdd/sql/core/src/test/scala/org/apache/spark/sql/SQLQuerySuite.scala ---------------------------------------------------------------------- diff --git a/sql/core/src/test/scala/org/apache/spark/sql/SQLQuerySuite.scala b/sql/core/src/test/scala/org/apache/spark/sql/SQLQuerySuite.scala index 8e7c8d7..4ba324a 100644 --- a/sql/core/src/test/scala/org/apache/spark/sql/SQLQuerySuite.scala +++ b/sql/core/src/test/scala/org/apache/spark/sql/SQLQuerySuite.scala @@ -1103,6 +1103,16 @@ class SQLQuerySuite extends QueryTest with SharedSQLContext { sql("SELECT * FROM upperCaseData EXCEPT SELECT * FROM upperCaseData"), Nil) } + test("MINUS") { + checkAnswer( + sql("SELECT * FROM lowerCaseData MINUS SELECT * FROM upperCaseData"), + Row(1, "a") :: Row(2, "b") :: Row(3, "c") :: Row(4, "d") :: Nil) + checkAnswer( + sql("SELECT * FROM lowerCaseData MINUS SELECT * FROM lowerCaseData"), Nil) + checkAnswer( + sql("SELECT * FROM upperCaseData MINUS SELECT * FROM upperCaseData"), Nil) + } + test("INTERSECT") { checkAnswer( sql("SELECT * FROM lowerCaseData INTERSECT SELECT * FROM lowerCaseData"), --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
