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 <lip...@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 <lip...@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-unsubscr...@spark.apache.org
For additional commands, e-mail: commits-h...@spark.apache.org
