You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2023/03/23 04:39:24 UTC
[calcite] branch main updated: [CALCITE-5585] Add STRPOS function (enabled in BigQuery, Postgres libraries)
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 93b11cb1dc [CALCITE-5585] Add STRPOS function (enabled in BigQuery, Postgres libraries)
93b11cb1dc is described below
commit 93b11cb1dcd93bd09da66555b1d6ae423e7f8b21
Author: Tanner Clary <ta...@google.com>
AuthorDate: Wed Mar 22 07:28:47 2023 -0700
[CALCITE-5585] Add STRPOS function (enabled in BigQuery, Postgres libraries)
Close apache/calcite#3144
---
babel/src/test/resources/sql/big-query.iq | 35 ++++++++++++++++++++++
.../calcite/sql/fun/SqlLibraryOperators.java | 4 +++
.../calcite/sql/fun/SqlPositionFunction.java | 11 ++-----
.../calcite/sql/fun/SqlStdOperatorTable.java | 2 +-
.../calcite/sql2rel/StandardConvertletTable.java | 7 +++++
site/_docs/reference.md | 5 ++--
.../org/apache/calcite/test/SqlOperatorTest.java | 25 ++++++++++++++++
7 files changed, 78 insertions(+), 11 deletions(-)
diff --git a/babel/src/test/resources/sql/big-query.iq b/babel/src/test/resources/sql/big-query.iq
index fa7ecb90e8..82586abaf1 100755
--- a/babel/src/test/resources/sql/big-query.iq
+++ b/babel/src/test/resources/sql/big-query.iq
@@ -892,6 +892,41 @@ FROM Casted;
!ok
+#####################################################################
+# STRPOS(string, substring)
+#
+# Returns the 1-based position of the first occurrence of substring
+# within string. Returns 0 if not found.
+SELECT STRPOS("abc", "a") as result;
++--------+
+| result |
++--------+
+| 1 |
++--------+
+(1 row)
+
+!ok
+
+SELECT STRPOS("abc", "d") as result;
++--------+
+| result |
++--------+
+| 0 |
++--------+
+(1 row)
+
+!ok
+
+SELECT STRPOS("abcabc", "bc") as result;
++--------+
+| result |
++--------+
+| 2 |
++--------+
+(1 row)
+
+!ok
+
#####################################################################
# DATE
#
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
index 8e892394c6..f00c4c5f9d 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
@@ -327,6 +327,10 @@ public abstract class SqlLibraryOperators {
return type;
}
+ /** The "STRPOS(string, substring)" function. */
+ @LibraryOperator(libraries = {BIG_QUERY, POSTGRESQL})
+ public static final SqlFunction STRPOS = new SqlPositionFunction("STRPOS");
+
/** Generic "SUBSTR(string, position [, substringLength ])" function. */
private static final SqlBasicFunction SUBSTR =
SqlBasicFunction.create("SUBSTR", ReturnTypes.ARG0_NULLABLE_VARYING,
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlPositionFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlPositionFunction.java
index 80f15cc794..753ba93f8c 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlPositionFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlPositionFunction.java
@@ -40,14 +40,9 @@ public class SqlPositionFunction extends SqlFunction {
OperandTypes.STRING_SAME_SAME
.or(OperandTypes.STRING_SAME_SAME_INTEGER);
- public SqlPositionFunction() {
- super(
- "POSITION",
- SqlKind.POSITION,
- ReturnTypes.INTEGER_NULLABLE,
- null,
- OTC_CUSTOM,
- SqlFunctionCategory.NUMERIC);
+ public SqlPositionFunction(String name) {
+ super(name, SqlKind.POSITION, ReturnTypes.INTEGER_NULLABLE, null,
+ OTC_CUSTOM, SqlFunctionCategory.NUMERIC);
}
//~ Methods ----------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index 6c7f1cfc46..5351b4b914 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -1578,7 +1578,7 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
/** The "TRIM" function. */
public static final SqlFunction TRIM = SqlTrimFunction.INSTANCE;
- public static final SqlFunction POSITION = new SqlPositionFunction();
+ public static final SqlFunction POSITION = new SqlPositionFunction("POSITION");
public static final SqlBasicFunction CHAR_LENGTH =
SqlBasicFunction.create("CHAR_LENGTH",
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
index 7d466a4a2f..f230426925 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -266,6 +266,13 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
call.operand(0),
SqlLiteral.createExactNumeric("0.5", SqlParserPos.ZERO))));
+ // "STRPOS(string, substring) is equivalent to
+ // "POSITION(substring IN string)"
+ registerOp(SqlLibraryOperators.STRPOS,
+ (cx, call) -> cx.convertExpression(
+ SqlStdOperatorTable.POSITION.createCall(SqlParserPos.ZERO,
+ call.operand(1), call.operand(0))));
+
// REVIEW jvs 24-Apr-2006: This only seems to be working from within a
// windowed agg. I have added an optimizer rule
// org.apache.calcite.rel.rules.AggregateReduceFunctionsRule which handles
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 9bd3fbd8a8..efa1fb5349 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -1359,8 +1359,8 @@ comp:
| CHARACTER_LENGTH(string) | As CHAR_LENGTH(*string*)
| UPPER(string) | Returns a character string converted to upper case
| LOWER(string) | Returns a character string converted to lower case
-| POSITION(string1 IN string2) | Returns the position of the first occurrence of *string1* in *string2*
-| POSITION(string1 IN string2 FROM integer) | Returns the position of the first occurrence of *string1* in *string2* starting at a given point (not standard SQL)
+| POSITION(substring IN string) | Returns the position of the first occurrence of *substring* in *string*
+| POSITION(substring IN string FROM integer) | Returns the position of the first occurrence of *substring* in *string* starting at a given point (not standard SQL)
| TRIM( { BOTH | LEADING | TRAILING } string1 FROM string2) | Removes the longest string containing only the characters in *string1* from the start/end/both ends of *string1*
| OVERLAY(string1 PLACING string2 FROM integer [ FOR integer2 ]) | Replaces a substring of *string1* with *string2*
| SUBSTRING(string FROM integer) | Returns a substring of a character string starting at a given point
@@ -2718,6 +2718,7 @@ BigQuery's type system uses confusingly different names for types and functions:
| b | SPLIT(string [, delimiter ]) | Returns the string array of *string* split at *delimiter* (if omitted, default is comma)
| b | STARTS_WITH(string1, string2) | Returns whether *string2* is a prefix of *string1*
| m | STRCMP(string, string) | Returns 0 if both of the strings are same and returns -1 when the first argument is smaller than the second and 1 when the second one is smaller than the first one
+| b p | STRPOS(string, substring) | Equivalent to `POSITION(substring IN string)`
| b m o p | SUBSTR(string, position [, substringLength ]) | Returns a portion of *string*, beginning at character *position*, *substringLength* characters long. SUBSTR calculates lengths using characters as defined by the input character set
| b o | TANH(numeric) | Returns the hyperbolic tangent of *numeric*
| b | TIME(hour, minute, second) | Returns a TIME value *hour*, *minute*, *second* (all of type INTEGER)
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index bb9f055c50..9bf5ccf5bd 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -6079,6 +6079,31 @@ public class SqlOperatorTest {
"Third argument (pad pattern) for LPAD/RPAD must not be empty", true);
}
+ @Test void testStrposFunction() {
+ final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.STRPOS);
+ f0.checkFails("^strpos('abc', 'a')^",
+ "No match found for function signature STRPOS\\(<CHARACTER>, <CHARACTER>\\)",
+ false);
+ final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY);
+ f.checkScalar("STRPOS('abc', 'a')", "1", "INTEGER NOT NULL");
+ f.checkScalar("STRPOS('abcabc', 'bc')", "2", "INTEGER NOT NULL");
+ f.checkScalar("STRPOS('abcabc', 'd')", "0", "INTEGER NOT NULL");
+ f.checkScalar("STRPOS('abc', '')", "1", "INTEGER NOT NULL");
+ f.checkScalar("STRPOS('', 'a')", "0", "INTEGER NOT NULL");
+ f.checkNull("STRPOS(null, 'a')");
+ f.checkNull("STRPOS('a', null)");
+
+ // test for BINARY
+ f.checkScalar("STRPOS(x'2212', x'12')", "2", "INTEGER NOT NULL");
+ f.checkScalar("STRPOS(x'2122', x'12')", "0", "INTEGER NOT NULL");
+ f.checkScalar("STRPOS(x'1222', x'12')", "1", "INTEGER NOT NULL");
+ f.checkScalar("STRPOS(x'1111', x'22')", "0", "INTEGER NOT NULL");
+ f.checkScalar("STRPOS(x'2122', x'')", "1", "INTEGER NOT NULL");
+ f.checkScalar("STRPOS(x'', x'12')", "0", "INTEGER NOT NULL");
+ f.checkNull("STRPOS(null, x'')");
+ f.checkNull("STRPOS(x'', null)");
+ }
+
@Test void testStartsWithFunction() {
final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.BIG_QUERY);
f.setFor(SqlLibraryOperators.STARTS_WITH);