Repository: spark Updated Branches: refs/heads/master dccc0aa3c -> 4d01aa464
[SPARK-20754][SQL][FOLLOWUP] Add Function Alias For MOD/POSITION. ## What changes were proposed in this pull request? https://github.com/apache/spark/pull/18106 Support TRUNC (number), We should also add function alias for `MOD `and `POSITION`. `POSITION(substr IN str) `is a synonym for `LOCATE(substr,str)`. same as MySQL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_position ## How was this patch tested? unit tests Author: Yuming Wang <wgy...@gmail.com> Closes #18206 from wangyum/SPARK-20754-mod&position. Project: http://git-wip-us.apache.org/repos/asf/spark/repo Commit: http://git-wip-us.apache.org/repos/asf/spark/commit/4d01aa46 Tree: http://git-wip-us.apache.org/repos/asf/spark/tree/4d01aa46 Diff: http://git-wip-us.apache.org/repos/asf/spark/diff/4d01aa46 Branch: refs/heads/master Commit: 4d01aa46489bb2e5f7c143d5e58981b3eb2f4a7d Parents: dccc0aa Author: Yuming Wang <wgy...@gmail.com> Authored: Tue Jun 13 23:39:06 2017 -0700 Committer: Xiao Li <gatorsm...@gmail.com> Committed: Tue Jun 13 23:39:06 2017 -0700 ---------------------------------------------------------------------- .../org/apache/spark/sql/catalyst/parser/SqlBase.g4 | 3 +++ .../spark/sql/catalyst/analysis/FunctionRegistry.scala | 2 ++ .../spark/sql/catalyst/expressions/arithmetic.scala | 2 ++ .../sql/catalyst/expressions/stringExpressions.scala | 4 ++++ .../org/apache/spark/sql/catalyst/parser/AstBuilder.scala | 7 +++++++ .../sql/catalyst/parser/TableIdentifierParserSuite.scala | 2 +- .../src/test/resources/sql-tests/inputs/operators.sql | 5 ++++- .../test/resources/sql-tests/inputs/string-functions.sql | 3 +++ .../test/resources/sql-tests/results/operators.sql.out | 10 +++++++++- .../resources/sql-tests/results/string-functions.sql.out | 10 +++++++++- 10 files changed, 44 insertions(+), 4 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/spark/blob/4d01aa46/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 43f7ff5..ef5648c 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 @@ -563,6 +563,7 @@ primaryExpression | CAST '(' expression AS dataType ')' #cast | FIRST '(' expression (IGNORE NULLS)? ')' #first | LAST '(' expression (IGNORE NULLS)? ')' #last + | POSITION '(' substr=valueExpression IN str=valueExpression ')' #position | constant #constantDefault | ASTERISK #star | qualifiedName '.' ASTERISK #star @@ -720,6 +721,7 @@ nonReserved | SET | RESET | VIEW | REPLACE | IF + | POSITION | NO | DATA | START | TRANSACTION | COMMIT | ROLLBACK | IGNORE | SORT | CLUSTER | DISTRIBUTE | UNSET | TBLPROPERTIES | SKEWED | STORED | DIRECTORIES | LOCATION @@ -850,6 +852,7 @@ MACRO: 'MACRO'; IGNORE: 'IGNORE'; IF: 'IF'; +POSITION: 'POSITION'; EQ : '=' | '=='; NSEQ: '<=>'; http://git-wip-us.apache.org/repos/asf/spark/blob/4d01aa46/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala ---------------------------------------------------------------------- diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala index 4245b70..8773281 100644 --- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala +++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala @@ -240,6 +240,7 @@ object FunctionRegistry { expression[Log1p]("log1p"), expression[Log2]("log2"), expression[Log]("ln"), + expression[Remainder]("mod"), expression[UnaryMinus]("negative"), expression[Pi]("pi"), expression[Pmod]("pmod"), @@ -325,6 +326,7 @@ object FunctionRegistry { expression[StringTrimLeft]("ltrim"), expression[JsonTuple]("json_tuple"), expression[ParseUrl]("parse_url"), + expression[StringLocate]("position"), expression[FormatString]("printf"), expression[RegExpExtract]("regexp_extract"), expression[RegExpReplace]("regexp_replace"), http://git-wip-us.apache.org/repos/asf/spark/blob/4d01aa46/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/arithmetic.scala ---------------------------------------------------------------------- diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/arithmetic.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/arithmetic.scala index f2b2522..ec6e6ba 100644 --- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/arithmetic.scala +++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/arithmetic.scala @@ -320,6 +320,8 @@ case class Divide(left: Expression, right: Expression) extends BinaryArithmetic Examples: > SELECT 2 _FUNC_ 1.8; 0.2 + > SELECT MOD(2, 1.8); + 0.2 """) case class Remainder(left: Expression, right: Expression) extends BinaryArithmetic { http://git-wip-us.apache.org/repos/asf/spark/blob/4d01aa46/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/stringExpressions.scala ---------------------------------------------------------------------- diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/stringExpressions.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/stringExpressions.scala index 035a1af..717ada2 100644 --- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/stringExpressions.scala +++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/stringExpressions.scala @@ -654,8 +654,12 @@ case class SubstringIndex(strExpr: Expression, delimExpr: Expression, countExpr: """, extended = """ Examples: + > SELECT _FUNC_('bar', 'foobarbar'); + 4 > SELECT _FUNC_('bar', 'foobarbar', 5); 7 + > SELECT POSITION('bar' IN 'foobarbar'); + 4 """) // scalastyle:on line.size.limit case class StringLocate(substr: Expression, str: Expression, start: Expression) http://git-wip-us.apache.org/repos/asf/spark/blob/4d01aa46/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 a16611a..500d999 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 @@ -1077,6 +1077,13 @@ class AstBuilder(conf: SQLConf) extends SqlBaseBaseVisitor[AnyRef] with Logging } /** + * Create a Position expression. + */ + override def visitPosition(ctx: PositionContext): Expression = withOrigin(ctx) { + new StringLocate(expression(ctx.substr), expression(ctx.str)) + } + + /** * Create a (windowed) Function expression. */ override def visitFunctionCall(ctx: FunctionCallContext): Expression = withOrigin(ctx) { http://git-wip-us.apache.org/repos/asf/spark/blob/4d01aa46/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/TableIdentifierParserSuite.scala ---------------------------------------------------------------------- diff --git a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/TableIdentifierParserSuite.scala b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/TableIdentifierParserSuite.scala index f33abc5..76be6ee 100644 --- a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/TableIdentifierParserSuite.scala +++ b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/TableIdentifierParserSuite.scala @@ -51,7 +51,7 @@ class TableIdentifierParserSuite extends SparkFunSuite { "rollup", "row", "rows", "set", "smallint", "table", "timestamp", "to", "trigger", "true", "truncate", "update", "user", "values", "with", "regexp", "rlike", "bigint", "binary", "boolean", "current_date", "current_timestamp", "date", "double", "float", - "int", "smallint", "timestamp", "at") + "int", "smallint", "timestamp", "at", "position") val hiveStrictNonReservedKeyword = Seq("anti", "full", "inner", "left", "semi", "right", "natural", "union", "intersect", "except", "database", "on", "join", "cross", "select", "from", http://git-wip-us.apache.org/repos/asf/spark/blob/4d01aa46/sql/core/src/test/resources/sql-tests/inputs/operators.sql ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/inputs/operators.sql b/sql/core/src/test/resources/sql-tests/inputs/operators.sql index 75a0256..3934620 100644 --- a/sql/core/src/test/resources/sql-tests/inputs/operators.sql +++ b/sql/core/src/test/resources/sql-tests/inputs/operators.sql @@ -76,4 +76,7 @@ select floor(0.01); select floor(-0.10); -- comparison operator -select 1 > 0.00001 +select 1 > 0.00001; + +-- mod +select mod(7, 2), mod(7, 0), mod(0, 2), mod(7, null), mod(null, 2), mod(null, null); http://git-wip-us.apache.org/repos/asf/spark/blob/4d01aa46/sql/core/src/test/resources/sql-tests/inputs/string-functions.sql ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/inputs/string-functions.sql b/sql/core/src/test/resources/sql-tests/inputs/string-functions.sql index d82df11..20c0390 100644 --- a/sql/core/src/test/resources/sql-tests/inputs/string-functions.sql +++ b/sql/core/src/test/resources/sql-tests/inputs/string-functions.sql @@ -15,3 +15,6 @@ select replace('abc', 'b'); -- uuid select length(uuid()), (uuid() <> uuid()); + +-- position +select position('bar' in 'foobarbar'), position(null, 'foobarbar'), position('aaads', null); http://git-wip-us.apache.org/repos/asf/spark/blob/4d01aa46/sql/core/src/test/resources/sql-tests/results/operators.sql.out ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/results/operators.sql.out b/sql/core/src/test/resources/sql-tests/results/operators.sql.out index 57e8a61..51ccf76 100644 --- a/sql/core/src/test/resources/sql-tests/results/operators.sql.out +++ b/sql/core/src/test/resources/sql-tests/results/operators.sql.out @@ -1,5 +1,5 @@ -- Automatically generated by SQLQueryTestSuite --- Number of queries: 50 +-- Number of queries: 51 -- !query 0 @@ -412,3 +412,11 @@ select 1 > 0.00001 struct<(CAST(1 AS BIGINT) > 0):boolean> -- !query 49 output true + + +-- !query 50 +select mod(7, 2), mod(7, 0), mod(0, 2), mod(7, null), mod(null, 2), mod(null, null) +-- !query 50 schema +struct<(7 % 2):int,(7 % 0):int,(0 % 2):int,(7 % CAST(NULL AS INT)):int,(CAST(NULL AS INT) % 2):int,(CAST(NULL AS DOUBLE) % CAST(NULL AS DOUBLE)):double> +-- !query 50 output +1 NULL 0 NULL NULL NULL http://git-wip-us.apache.org/repos/asf/spark/blob/4d01aa46/sql/core/src/test/resources/sql-tests/results/string-functions.sql.out ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/results/string-functions.sql.out b/sql/core/src/test/resources/sql-tests/results/string-functions.sql.out index 4093a7b..52eb554 100644 --- a/sql/core/src/test/resources/sql-tests/results/string-functions.sql.out +++ b/sql/core/src/test/resources/sql-tests/results/string-functions.sql.out @@ -1,5 +1,5 @@ -- Automatically generated by SQLQueryTestSuite --- Number of queries: 7 +-- Number of queries: 8 -- !query 0 @@ -78,3 +78,11 @@ select length(uuid()), (uuid() <> uuid()) struct<length(uuid()):int,(NOT (uuid() = uuid())):boolean> -- !query 6 output 36 true + + +-- !query 7 +select position('bar' in 'foobarbar'), position(null, 'foobarbar'), position('aaads', null) +-- !query 7 schema +struct<locate(bar, foobarbar, 1):int,locate(CAST(NULL AS STRING), foobarbar, 1):int,locate(aaads, CAST(NULL AS STRING), 1):int> +-- !query 7 output +4 NULL NULL --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@spark.apache.org For additional commands, e-mail: commits-h...@spark.apache.org
