[spark] branch master updated: [SPARK-27949][SQL] Support SUBSTRING(str FROM n1 [FOR n2]) syntax

[do...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

dongjoon 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 3b37bfd  [SPARK-27949][SQL] Support SUBSTRING(str FROM n1 [FOR n2]) syntax
3b37bfd is described below

commit 3b37bfde2a9bef6f6f95138b1c399b36dbed99eb
Author: Zhu, Lipeng <li...@ebay.com>
AuthorDate: Mon Jun 10 09:05:10 2019 -0700

    [SPARK-27949][SQL] Support SUBSTRING(str FROM n1 [FOR n2]) syntax
    
    ## What changes were proposed in this pull request?
    
    Currently, function `substr/substring`'s usage is like `substring(string_expression, n1 [,n2])`.
    
    But, the ANSI SQL defined the pattern for substr/substring is like `SUBSTRING(str FROM n1 [FOR n2])`. This gap makes some inconvenient when we switch to the SparkSQL.
    
    - ANSI SQL-92: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
    
    Below are the mainly DB engines to support the ANSI standard for substring.
    - PostgreSQL https://www.postgresql.org/docs/9.1/functions-string.html
    - MySQL https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substring
    - Redshift https://docs.aws.amazon.com/redshift/latest/dg/r_SUBSTRING.html
    - Teradata https://docs.teradata.com/reader/756LNiPSFdY~4JcCCcR5Cw/XnePye0Cwexw6Pny_qnxVA
    
    **Oracle, SQL Server, Hive, Presto don't have this additional syntax.**
    
    ## How was this patch tested?
    
    Pass the Jenkins with the updated test cases.
    
    Closes #24802 from lipzhu/SPARK-27949.
    
    Authored-by: Zhu, Lipeng <li...@ebay.com>
    Signed-off-by: Dongjoon Hyun <dh...@apple.com>
---
 docs/sql-keywords.md                               |  2 +
 .../apache/spark/sql/catalyst/parser/SqlBase.g4    |  8 ++
 .../spark/sql/catalyst/parser/AstBuilder.scala     | 11 +++
 .../parser/TableIdentifierParserSuite.scala        |  2 +
 .../sql-tests/inputs/string-functions.sql          | 14 ++++
 .../sql-tests/results/string-functions.sql.out     | 98 +++++++++++++++++++++-
 6 files changed, 134 insertions(+), 1 deletion(-)

diff --git a/docs/sql-keywords.md b/docs/sql-keywords.md
index 62378d9..8442291 100644
--- a/docs/sql-keywords.md
+++ b/docs/sql-keywords.md
@@ -248,6 +248,8 @@ Below is a list of all the keywords in Spark SQL.
   <tr><td>STORED</td><td>non-reserved</td><td>non-reserved</td><td>non-reserved</td></tr>
   <tr><td>STRATIFY</td><td>non-reserved</td><td>non-reserved</td><td>non-reserved</td></tr>
   <tr><td>STRUCT</td><td>non-reserved</td><td>non-reserved</td><td>non-reserved</td></tr>
+  <tr><td>SUBSTR</td><td>non-reserved</td><td>non-reserved</td><td>non-reserved</td></tr>
+  <tr><td>SUBSTRING</td><td>non-reserved</td><td>non-reserved</td><td>non-reserved</td></tr>
   <tr><td>TABLE</td><td>reserved</td><td>non-reserved</td><td>reserved</td></tr>
   <tr><td>TABLES</td><td>non-reserved</td><td>non-reserved</td><td>non-reserved</td></tr>
   <tr><td>TABLESAMPLE</td><td>non-reserved</td><td>non-reserved</td><td>reserved</td></tr>
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 d32f922..e919c90 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
@@ -671,6 +671,8 @@ primaryExpression
     | base=primaryExpression '.' fieldName=identifier                                          #dereference
     | '(' expression ')'                                                                       #parenthesizedExpression
     | EXTRACT '(' field=identifier FROM source=valueExpression ')'                             #extract
+    | (SUBSTR | SUBSTRING) '(' str=valueExpression (FROM | ',') pos=valueExpression
+      ((FOR | ',') len=valueExpression)? ')'                                                   #substring
     ;
 
 constant
@@ -1001,6 +1003,8 @@ ansiNonReserved
     | STORED
     | STRATIFY
     | STRUCT
+    | SUBSTR
+    | SUBSTRING
     | TABLES
     | TABLESAMPLE
     | TBLPROPERTIES
@@ -1255,6 +1259,8 @@ nonReserved
     | STORED
     | STRATIFY
     | STRUCT
+    | SUBSTR
+    | SUBSTRING
     | TABLE
     | TABLES
     | TABLESAMPLE
@@ -1511,6 +1517,8 @@ STATISTICS: 'STATISTICS';
 STORED: 'STORED';
 STRATIFY: 'STRATIFY';
 STRUCT: 'STRUCT';
+SUBSTR: 'SUBSTR';
+SUBSTRING: 'SUBSTRING';
 TABLE: 'TABLE';
 TABLES: 'TABLES';
 TABLESAMPLE: 'TABLESAMPLE';
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 ca5a567..fdef5f9 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
@@ -1334,6 +1334,17 @@ class AstBuilder(conf: SQLConf) extends SqlBaseBaseVisitor[AnyRef] with Logging
   }
 
   /**
+   * Create a Substring/Substr expression.
+   */
+  override def visitSubstring(ctx: SubstringContext): Expression = withOrigin(ctx) {
+    if (ctx.len != null) {
+      Substring(expression(ctx.str), expression(ctx.pos), expression(ctx.len))
+    } else {
+      new Substring(expression(ctx.str), expression(ctx.pos))
+    }
+  }
+
+  /**
    * Create a (windowed) Function expression.
    */
   override def visitFunctionCall(ctx: FunctionCallContext): Expression = withOrigin(ctx) {
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 f86ce16..551597e 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
@@ -511,6 +511,8 @@ class TableIdentifierParserSuite extends SparkFunSuite with SQLHelper {
     "stored",
     "stratify",
     "struct",
+    "substr",
+    "substring",
     "table",
     "tables",
     "tablesample",
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 fbc2316..3fe571d 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
@@ -24,3 +24,17 @@ select right(null, -2), right("abcd", -2), right("abcd", 0), right("abcd", 'a');
 -- split function
 SELECT split('aa1cc2ee3', '[1-9]+');
 SELECT split('aa1cc2ee3', '[1-9]+', 2);
+
+-- substring function
+SELECT substr('Spark SQL', 5);
+SELECT substr('Spark SQL', -3);
+SELECT substr('Spark SQL', 5, 1);
+SELECT substr('Spark SQL' from 5);
+SELECT substr('Spark SQL' from -3);
+SELECT substr('Spark SQL' from 5 for 1);
+SELECT substring('Spark SQL', 5);
+SELECT substring('Spark SQL', -3);
+SELECT substring('Spark SQL', 5, 1);
+SELECT substring('Spark SQL' from 5);
+SELECT substring('Spark SQL' from -3);
+SELECT substring('Spark SQL' from 5 for 1);
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 25d93b2..e6e0795 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: 13
+-- Number of queries: 25
 
 
 -- !query 0
@@ -106,3 +106,99 @@ SELECT split('aa1cc2ee3', '[1-9]+', 2)
 struct<split(aa1cc2ee3, [1-9]+, 2):array<string>>
 -- !query 12 output
 ["aa","cc2ee3"]
+
+
+-- !query 13
+SELECT substr('Spark SQL', 5)
+-- !query 13 schema
+struct<substring(Spark SQL, 5, 2147483647):string>
+-- !query 13 output
+k SQL
+
+
+-- !query 14
+SELECT substr('Spark SQL', -3)
+-- !query 14 schema
+struct<substring(Spark SQL, -3, 2147483647):string>
+-- !query 14 output
+SQL
+
+
+-- !query 15
+SELECT substr('Spark SQL', 5, 1)
+-- !query 15 schema
+struct<substring(Spark SQL, 5, 1):string>
+-- !query 15 output
+k
+
+
+-- !query 16
+SELECT substr('Spark SQL' from 5)
+-- !query 16 schema
+struct<substring(Spark SQL, 5, 2147483647):string>
+-- !query 16 output
+k SQL
+
+
+-- !query 17
+SELECT substr('Spark SQL' from -3)
+-- !query 17 schema
+struct<substring(Spark SQL, -3, 2147483647):string>
+-- !query 17 output
+SQL
+
+
+-- !query 18
+SELECT substr('Spark SQL' from 5 for 1)
+-- !query 18 schema
+struct<substring(Spark SQL, 5, 1):string>
+-- !query 18 output
+k
+
+
+-- !query 19
+SELECT substring('Spark SQL', 5)
+-- !query 19 schema
+struct<substring(Spark SQL, 5, 2147483647):string>
+-- !query 19 output
+k SQL
+
+
+-- !query 20
+SELECT substring('Spark SQL', -3)
+-- !query 20 schema
+struct<substring(Spark SQL, -3, 2147483647):string>
+-- !query 20 output
+SQL
+
+
+-- !query 21
+SELECT substring('Spark SQL', 5, 1)
+-- !query 21 schema
+struct<substring(Spark SQL, 5, 1):string>
+-- !query 21 output
+k
+
+
+-- !query 22
+SELECT substring('Spark SQL' from 5)
+-- !query 22 schema
+struct<substring(Spark SQL, 5, 2147483647):string>
+-- !query 22 output
+k SQL
+
+
+-- !query 23
+SELECT substring('Spark SQL' from -3)
+-- !query 23 schema
+struct<substring(Spark SQL, -3, 2147483647):string>
+-- !query 23 output
+SQL
+
+
+-- !query 24
+SELECT substring('Spark SQL' from 5 for 1)
+-- !query 24 schema
+struct<substring(Spark SQL, 5, 1):string>
+-- !query 24 output
+k


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org