spark-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From lix...@apache.org
Subject spark git commit: [SPARK-20754][SQL][FOLLOWUP] Add Function Alias For MOD/POSITION.
Date Wed, 14 Jun 2017 06:39:12 GMT
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 <wgyumg@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 <wgyumg@gmail.com>
Authored: Tue Jun 13 23:39:06 2017 -0700
Committer: Xiao Li <gatorsmile@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-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org


Mime
View raw message