[ignite-3] branch main updated: IGNITE-18787: Sql. Add SUBSTR alias for SUBSTRING function (#1724)

[amashenkov]

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

amashenkov pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/ignite-3.git


The following commit(s) were added to refs/heads/main by this push:
     new c87b6f900a IGNITE-18787: Sql. Add SUBSTR alias for SUBSTRING function 
(#1724)
c87b6f900a is described below

commit c87b6f900a42e274611a7800ec51629b36e2cefd
Author: Max Zhuravkov <shh...@gmail.com>
AuthorDate: Wed Mar 1 17:04:25 2023 +0400

    IGNITE-18787: Sql. Add SUBSTR alias for SUBSTRING function (#1724)
---
 .../apache/ignite/internal/sql/engine/ItFunctionsTest.java   | 12 ++++++++++++
 .../internal/sql/engine/sql/fun/IgniteSqlOperatorTable.java  | 11 +++++++++++
 2 files changed, 23 insertions(+)

diff --git 
a/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFunctionsTest.java
 
b/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFunctionsTest.java
index 1954f269ba..5a84bfdfcc 100644
--- 
a/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFunctionsTest.java
+++ 
b/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFunctionsTest.java
@@ -280,6 +280,18 @@ public class ItFunctionsTest extends 
AbstractBasicIntegrationTest {
         }
     }
 
+    /**
+     * Tests for {@code SUBSTR(str, start[, length])} function.
+     */
+    @Test
+    public void testSubstr() {
+        assertQuery("SELECT SUBSTR('abcdefg', 1, 3)").returns("abc");
+        assertQuery("SELECT SUBSTR('abcdefg', 2)").returns("bcdefg");
+        assertQuery("SELECT SUBSTR('abcdefg', -1)").returns("abcdefg");
+        assertQuery("SELECT SUBSTR('abcdefg', 1, -3)").returns("");
+        assertQuery("SELECT SUBSTR(1000, 1, 3)").returns("100");
+    }
+
     /**
      * An interface describing a clock reporting time in a specified temporal 
value.
      *
diff --git 
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/sql/fun/IgniteSqlOperatorTable.java
 
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/sql/fun/IgniteSqlOperatorTable.java
index 59d39cd5bd..d626348b2e 100644
--- 
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/sql/fun/IgniteSqlOperatorTable.java
+++ 
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/sql/fun/IgniteSqlOperatorTable.java
@@ -22,6 +22,7 @@ import org.apache.calcite.sql.SqlFunctionCategory;
 import org.apache.calcite.sql.SqlKind;
 import org.apache.calcite.sql.fun.SqlLibraryOperators;
 import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.sql.fun.SqlSubstringFunction;
 import org.apache.calcite.sql.type.OperandTypes;
 import org.apache.calcite.sql.type.ReturnTypes;
 import org.apache.calcite.sql.type.SqlTypeName;
@@ -97,6 +98,15 @@ public class IgniteSqlOperatorTable extends 
ReflectiveSqlOperatorTable {
                     OperandTypes.SAME_SAME,
                     SqlFunctionCategory.SYSTEM);
 
+    /**
+     * Generic {@code SUBSTR(string, position [, length]} function.
+     * This function works exactly the same as {@link SqlSubstringFunction 
SUSBSTRING(string, position [, length])}.
+     */
+    public static final SqlFunction SUBSTR = new SqlFunction("SUBSTR", 
SqlKind.OTHER_FUNCTION,
+            ReturnTypes.ARG0_NULLABLE_VARYING, null,
+            OperandTypes.STRING_INTEGER_OPTIONAL_INTEGER,
+            SqlFunctionCategory.STRING);
+
     /** Singleton instance. */
     public static final IgniteSqlOperatorTable INSTANCE = new 
IgniteSqlOperatorTable();
 
@@ -205,6 +215,7 @@ public class IgniteSqlOperatorTable extends 
ReflectiveSqlOperatorTable {
         register(SqlStdOperatorTable.TRIM);
         register(SqlLibraryOperators.LTRIM);
         register(SqlLibraryOperators.RTRIM);
+        register(SUBSTR);
 
         // Math functions.
         register(SqlStdOperatorTable.MOD); // Arithmetic remainder.