You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@iotdb.apache.org by ja...@apache.org on 2023/03/23 11:43:50 UTC
[iotdb] branch master updated: [IoTDB-5637] Add substring as built-in scalar function
This is an automated email from the ASF dual-hosted git repository.
jackietien pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/iotdb.git
The following commit(s) were added to refs/heads/master by this push:
new 3ee95741cc [IoTDB-5637] Add substring as built-in scalar function
3ee95741cc is described below
commit 3ee95741cc4e6d87e2fe81e56ad2c915e67bb075
Author: Zhijia Cao <ca...@126.com>
AuthorDate: Thu Mar 23 19:43:42 2023 +0800
[IoTDB-5637] Add substring as built-in scalar function
---
.../org/apache/iotdb/db/qp/sql/IdentifierParser.g4 | 1 +
.../org/apache/iotdb/db/qp/sql/IoTDBSqlParser.g4 | 10 +
.../antlr4/org/apache/iotdb/db/qp/sql/SqlLexer.g4 | 4 +
docs/UserGuide/Operators-Functions/String.md | 14 +-
docs/zh/UserGuide/Operators-Functions/Overview.md | 28 +--
docs/zh/UserGuide/Operators-Functions/String.md | 14 +-
.../itbase/constant/BuiltinScalarFunctionEnum.java | 1 +
.../BuiltinTimeSeriesGeneratingFunctionEnum.java | 1 -
.../scalar/IoTDBSubStringFunctionIT.java | 267 +++++++++++++++++++++
.../it/udf/IoTDBUDTFBuiltinScalarFunctionIT.java | 5 +-
.../commons/udf/builtin/BuiltinScalarFunction.java | 1 +
.../BuiltinTimeSeriesGeneratingFunction.java | 2 -
.../commons/udf/builtin/String/UDTFSubstr.java | 70 ------
.../org/apache/iotdb/db/constant/SqlConstant.java | 8 +
.../BuiltInScalarFunctionHelperFactory.java | 3 +
.../builtin/helper/SubStringFunctionHelper.java | 129 ++++++++++
.../iotdb/db/mpp/plan/parser/ASTVisitor.java | 29 +++
.../scalar/SubStringFunctionColumnTransformer.java | 63 +++++
.../unary/scalar/SubStringFunctionTransformer.java | 63 +++++
19 files changed, 609 insertions(+), 104 deletions(-)
diff --git a/antlr/src/main/antlr4/org/apache/iotdb/db/qp/sql/IdentifierParser.g4 b/antlr/src/main/antlr4/org/apache/iotdb/db/qp/sql/IdentifierParser.g4
index ce0b227f43..29874313e2 100644
--- a/antlr/src/main/antlr4/org/apache/iotdb/db/qp/sql/IdentifierParser.g4
+++ b/antlr/src/main/antlr4/org/apache/iotdb/db/qp/sql/IdentifierParser.g4
@@ -175,6 +175,7 @@ keyWords
| STATELESS
| STATEMENT
| STOP
+ | SUBSTRING
| SYSTEM
| TAGS
| TASK
diff --git a/antlr/src/main/antlr4/org/apache/iotdb/db/qp/sql/IoTDBSqlParser.g4 b/antlr/src/main/antlr4/org/apache/iotdb/db/qp/sql/IoTDBSqlParser.g4
index e7a8e8b603..00a9d5d481 100644
--- a/antlr/src/main/antlr4/org/apache/iotdb/db/qp/sql/IoTDBSqlParser.g4
+++ b/antlr/src/main/antlr4/org/apache/iotdb/db/qp/sql/IoTDBSqlParser.g4
@@ -983,6 +983,7 @@ functionName
scalarFunctionExpression
: CAST LR_BRACKET castInput=expression AS attributeValue RR_BRACKET
| REPLACE LR_BRACKET text=expression COMMA from=STRING_LITERAL COMMA to=STRING_LITERAL RR_BRACKET
+ | SUBSTRING subStringExpression
| ROUND LR_BRACKET input=expression (COMMA places=constant)? RR_BRACKET
;
@@ -1059,3 +1060,12 @@ connectorAttributesClause
connectorAttributeClause
: connectorKey=STRING_LITERAL OPERATOR_SEQ connectorValue=STRING_LITERAL
;
+
+subStringExpression
+ : LR_BRACKET input=expression COMMA startPosition=signedIntegerLiteral (COMMA length=signedIntegerLiteral)? RR_BRACKET
+ | LR_BRACKET input=expression FROM from=signedIntegerLiteral (FOR forLength=signedIntegerLiteral)? RR_BRACKET
+ ;
+
+signedIntegerLiteral
+ : (PLUS|MINUS)?INTEGER_LITERAL
+ ;
\ No newline at end of file
diff --git a/antlr/src/main/antlr4/org/apache/iotdb/db/qp/sql/SqlLexer.g4 b/antlr/src/main/antlr4/org/apache/iotdb/db/qp/sql/SqlLexer.g4
index 9e5bfcf417..7024c37c0d 100644
--- a/antlr/src/main/antlr4/org/apache/iotdb/db/qp/sql/SqlLexer.g4
+++ b/antlr/src/main/antlr4/org/apache/iotdb/db/qp/sql/SqlLexer.g4
@@ -646,6 +646,10 @@ STOP
: S T O P
;
+SUBSTRING
+ : S U B S T R I N G
+ ;
+
SYSTEM
: S Y S T E M
;
diff --git a/docs/UserGuide/Operators-Functions/String.md b/docs/UserGuide/Operators-Functions/String.md
index baef2100de..30d51bf489 100644
--- a/docs/UserGuide/Operators-Functions/String.md
+++ b/docs/UserGuide/Operators-Functions/String.md
@@ -359,19 +359,19 @@ Output series:
+-----------------------------+--------------+--------------+-----------------------------------------------------------------------------------------------+
```
-## Substr
+## substring
### Usage
-The function is used to get the substring `start` to `end - 1`.
+Extracts a substring of a string, starting with the first specified character and stopping after the specified number of characters.The index start at 1 .The value range of from and for is an INT32.
-**Name:** SUBSTR
+**Name:** SUBSTRING
**Input Series:** Only support a single input series. The data type is TEXT.
**Parameter:**
-+ `start`: Indicates the start position of substring.
-+ `end`: Indicates the end position of substring.
++ `from`: Indicates the start position of substring.
++ `for`: Indicates how many characters to stop after of substring.
**Output Series:** Output a single series. The type is TEXT.
@@ -393,14 +393,14 @@ Input series:
SQL for query:
```sql
-select s1, substr(s1, "start"="0", "end"="2") from root.sg1.d1
+select s1, substring(s1 from 1 for 2) from root.sg1.d1
```
Output series:
```
+-----------------------------+--------------+----------------------------------------------+
-| Time|root.sg1.d1.s1|substr(root.sg1.d1.s1, "start"="0", "end"="2")|
+| Time|root.sg1.d1.s1|SUBSTRING(root.sg1.d1.s1 FROM 1 FOR 2) |
+-----------------------------+--------------+----------------------------------------------+
|1970-01-01T08:00:00.001+08:00| 1test1| 1t|
|1970-01-01T08:00:00.002+08:00| 22test22| 22|
diff --git a/docs/zh/UserGuide/Operators-Functions/Overview.md b/docs/zh/UserGuide/Operators-Functions/Overview.md
index be0b9ff3f9..257b4587f6 100644
--- a/docs/zh/UserGuide/Operators-Functions/Overview.md
+++ b/docs/zh/UserGuide/Operators-Functions/Overview.md
@@ -149,20 +149,20 @@ OR, |, ||
### 字符串处理函数
-| 函数名 | 输入序列类型 | 必要的属性参数 | 输出序列类型 | 功能描述 |
-| --------------- | ------------ | ------------------------------------ | ------------ | ----------------------------------------- |
-| STRING_CONTAINS | TEXT | `s`: 待搜寻的字符串 | BOOLEAN | 判断字符串中是否存在`s` |
-| STRING_MATCHES | TEXT | `regex`: Java 标准库风格的正则表达式 | BOOLEAN | 判断字符串是否能够被正则表达式`regex`匹配 |
-| LENGTH | TEXT | 无 | INT32 | 返回字符串的长度 |
-| LOCATE | TEXT | `target`: 需要被定位的子串 <br/> `reverse`: 指定是否需要倒序定位，默认值为`false`, 即从左至右定位 | INT32 | 获取`target`子串第一次出现在输入序列的位置，如果输入序列中不包含`target`则返回 -1 |
-| STARTSWITH | TEXT | `target`: 需要匹配的前缀 | BOOLEAN | 判断字符串是否有指定前缀 |
-| ENDSWITH | TEXT | `target`: 需要匹配的后缀 | BOOLEAN | 判断字符串是否有指定后缀 |
-| CONCAT | TEXT | `targets`: 一系列 K-V, key需要以`target`为前缀且不重复, value是待拼接的字符串。<br/>`series_behind`: 指定拼接时时间序列是否在后面，默认为`false`。 | TEXT | 拼接字符串和`target`字串 |
-| SUBSTR | TEXT | `start`: 指定子串开始下标 <br/>`end`: 指定子串结束下标 | TEXT | 获取下标从`start`到`end - 1`的子串 |
-| UPPER | TEXT | 无 | TEXT | 将字符串转化为大写 |
-| LOWER | TEXT | 无 | TEXT | 将字符串转化为小写 |
-| TRIM | TEXT | 无 | TEXT | 移除字符串前后的空格 |
-| STRCMP | TEXT | 无 | TEXT | 用于比较两个输入序列，如果值相同返回 `0` , 序列1的值小于序列2的值返回一个`负数`，序列1的值大于序列2的值返回一个`正数` |
+| 函数名 | 输入序列类型 | 必要的属性参数 | 输出序列类型 | 功能描述 |
+|-----------------| ------------ |-----------------------------------------------------------------------------------------------------------| ------------ |-------------------------------------------------------------------------|
+| STRING_CONTAINS | TEXT | `s`: 待搜寻的字符串 | BOOLEAN | 判断字符串中是否存在`s` |
+| STRING_MATCHES | TEXT | `regex`: Java 标准库风格的正则表达式 | BOOLEAN | 判断字符串是否能够被正则表达式`regex`匹配 |
+| LENGTH | TEXT | 无 | INT32 | 返回字符串的长度 |
+| LOCATE | TEXT | `target`: 需要被定位的子串 <br/> `reverse`: 指定是否需要倒序定位，默认值为`false`, 即从左至右定位 | INT32 | 获取`target`子串第一次出现在输入序列的位置，如果输入序列中不包含`target`则返回 -1 |
+| STARTSWITH | TEXT | `target`: 需要匹配的前缀 | BOOLEAN | 判断字符串是否有指定前缀 |
+| ENDSWITH | TEXT | `target`: 需要匹配的后缀 | BOOLEAN | 判断字符串是否有指定后缀 |
+| CONCAT | TEXT | `targets`: 一系列 K-V, key需要以`target`为前缀且不重复, value是待拼接的字符串。<br/>`series_behind`: 指定拼接时时间序列是否在后面，默认为`false`。 | TEXT | 拼接字符串和`target`字串 |
+| SUBSTRING | TEXT | `from`: 指定子串开始下标 <br/>`for`: 指定的字符个数之后停止 | TEXT | 提取字符串的子字符串，从指定的第一个字符开始，并在指定的字符数之后停止。下标从1开始。from 和 for的范围是 INT32 类型取值范围。 |
+| UPPER | TEXT | 无 | TEXT | 将字符串转化为大写 |
+| LOWER | TEXT | 无 | TEXT | 将字符串转化为小写 |
+| TRIM | TEXT | 无 | TEXT | 移除字符串前后的空格 |
+| STRCMP | TEXT | 无 | TEXT | 用于比较两个输入序列，如果值相同返回 `0` , 序列1的值小于序列2的值返回一个`负数`，序列1的值大于序列2的值返回一个`正数` |
详细说明及示例见文档 [字符串处理函数](./String.md)。
diff --git a/docs/zh/UserGuide/Operators-Functions/String.md b/docs/zh/UserGuide/Operators-Functions/String.md
index 2535364a48..f59bcc7abc 100644
--- a/docs/zh/UserGuide/Operators-Functions/String.md
+++ b/docs/zh/UserGuide/Operators-Functions/String.md
@@ -363,19 +363,19 @@ select s1, s2, concat(s1, s2, "target1"="IoT", "target2"="DB", "series_behind"="
+-----------------------------+--------------+--------------+-----------------------------------------------------------------------------------------------+
```
-### Substr
+### Substring
#### 函数简介
+提取字符串的子字符串，从指定的第一个字符开始，并在指定的字符数之后停止。下标从1开始。from 和 for的范围是 INT32 类型取值范围。
-本函数用于获取下标从`start`到`end - 1`的子串
+**函数名:** SUBSTRING
-**函数名:** SUBSTR
**输入序列:** 仅支持单个输入序列，类型为TEXT。
**参数:**
-+ `start`: 指定子串开始下标。
-+ `end`: 指定子串结束下标。
++ `from`: 指定子串开始下标。
++ `for`: 指定多少个字符数后停止。
**输出序列:** 输出单个序列，类型为 TEXT。
@@ -397,14 +397,14 @@ select s1, s2, concat(s1, s2, "target1"="IoT", "target2"="DB", "series_behind"="
用于查询的 SQL 语句:
```sql
-select s1, substr(s1, "start"="0", "end"="2") from root.sg1.d1
+select s1, substring(s1 from 1 for 2) from root.sg1.d1
```
输出序列:
```
+-----------------------------+--------------+----------------------------------------------+
-| Time|root.sg1.d1.s1|substr(root.sg1.d1.s1, "start"="0", "end"="2")|
+| Time|root.sg1.d1.s1| SUBSTRING(root.sg1.d1.s1 FROM 1 FOR 2) |
+-----------------------------+--------------+----------------------------------------------+
|1970-01-01T08:00:00.001+08:00| 1test1| 1t|
|1970-01-01T08:00:00.002+08:00| 22test22| 22|
diff --git a/integration-test/src/main/java/org/apache/iotdb/itbase/constant/BuiltinScalarFunctionEnum.java b/integration-test/src/main/java/org/apache/iotdb/itbase/constant/BuiltinScalarFunctionEnum.java
index 3784af7c61..1010e1cac5 100644
--- a/integration-test/src/main/java/org/apache/iotdb/itbase/constant/BuiltinScalarFunctionEnum.java
+++ b/integration-test/src/main/java/org/apache/iotdb/itbase/constant/BuiltinScalarFunctionEnum.java
@@ -23,6 +23,7 @@ public enum BuiltinScalarFunctionEnum {
DIFF("diff"),
CAST("cast"),
REPLACE("replace"),
+ SUBSTRING("substring"),
ROUND("round");
;
diff --git a/integration-test/src/main/java/org/apache/iotdb/itbase/constant/BuiltinTimeSeriesGeneratingFunctionEnum.java b/integration-test/src/main/java/org/apache/iotdb/itbase/constant/BuiltinTimeSeriesGeneratingFunctionEnum.java
index cf3b130a48..337bdb9763 100644
--- a/integration-test/src/main/java/org/apache/iotdb/itbase/constant/BuiltinTimeSeriesGeneratingFunctionEnum.java
+++ b/integration-test/src/main/java/org/apache/iotdb/itbase/constant/BuiltinTimeSeriesGeneratingFunctionEnum.java
@@ -49,7 +49,6 @@ public enum BuiltinTimeSeriesGeneratingFunctionEnum {
STRING_STARTS_WITH("STARTSWITH"),
STRING_ENDS_WITH("ENDSWITH"),
STRING_CONCAT("CONCAT"),
- STRING_SUBSTR("SUBSTR"),
STRING_UPPER("UPPER"),
STRING_LOWER("LOWER"),
STRING_TRIM("TRIM"),
diff --git a/integration-test/src/test/java/org/apache/iotdb/db/it/builtinfunction/scalar/IoTDBSubStringFunctionIT.java b/integration-test/src/test/java/org/apache/iotdb/db/it/builtinfunction/scalar/IoTDBSubStringFunctionIT.java
new file mode 100644
index 0000000000..aff2fe3d14
--- /dev/null
+++ b/integration-test/src/test/java/org/apache/iotdb/db/it/builtinfunction/scalar/IoTDBSubStringFunctionIT.java
@@ -0,0 +1,267 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.iotdb.db.it.builtinfunction.scalar;
+
+import org.apache.iotdb.it.env.EnvFactory;
+import org.apache.iotdb.it.framework.IoTDBTestRunner;
+import org.apache.iotdb.itbase.category.ClusterIT;
+import org.apache.iotdb.itbase.category.LocalStandaloneIT;
+import org.apache.iotdb.rpc.TSStatusCode;
+
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import org.junit.experimental.categories.Category;
+import org.junit.runner.RunWith;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+import static org.apache.iotdb.db.it.utils.TestUtils.assertTestFail;
+import static org.apache.iotdb.db.it.utils.TestUtils.prepareData;
+import static org.apache.iotdb.db.it.utils.TestUtils.resultSetEqualTest;
+import static org.junit.Assert.fail;
+
+@RunWith(IoTDBTestRunner.class)
+@Category({LocalStandaloneIT.class, ClusterIT.class})
+public class IoTDBSubStringFunctionIT {
+ private static final String[] SQLs =
+ new String[] {
+ "CREATE DATABASE root.sg",
+ "CREATE TIMESERIES root.sg.s1 WITH DATATYPE=TEXT, ENCODING=PLAIN",
+ "CREATE TIMESERIES root.sg.s2 WITH DATATYPE=INT32, ENCODING=PLAIN",
+ "CREATE TIMESERIES root.sg.s3 WITH DATATYPE=INT64, ENCODING=PLAIN",
+ "CREATE TIMESERIES root.sg.s4 WITH DATATYPE=FLOAT, ENCODING=PLAIN",
+ "CREATE TIMESERIES root.sg.s5 WITH DATATYPE=DOUBLE, ENCODING=PLAIN",
+ "CREATE TIMESERIES root.sg.s6 WITH DATATYPE=BOOLEAN, ENCODING=PLAIN",
+ "INSERT INTO root.sg(timestamp,s1,s2,s3,s4,s5,s6) values(1, 'abcd', 1, 1, 1, 1, true)",
+ "INSERT INTO root.sg(timestamp,s1) values(2, 'test')",
+ "INSERT INTO root.sg(timestamp,s1) values(3, 'abcdefg')",
+ "flush"
+ };
+
+ @BeforeClass
+ public static void setUp() throws Exception {
+ EnvFactory.getEnv().initClusterEnvironment();
+ prepareData(SQLs);
+ registerUDF();
+ }
+
+ @AfterClass
+ public static void tearDown() throws Exception {
+ EnvFactory.getEnv().cleanClusterEnvironment();
+ }
+
+ private static void registerUDF() {
+ try (Connection connection = EnvFactory.getEnv().getConnection();
+ Statement statement = connection.createStatement()) {
+ statement.execute(
+ "create function constvalue as 'org.apache.iotdb.db.query.udf.example.ConstValue'");
+ } catch (SQLException throwable) {
+ fail(throwable.getMessage());
+ }
+ }
+
+ @Test
+ public void testNewTransformer() {
+ // normal
+ String[] expectedHeader =
+ new String[] {
+ "Time,root.sg.s1,SUBSTRING(root.sg.s1,1),SUBSTRING(root.sg.s1,1,3),SUBSTRING(root.sg.s1 FROM 1),SUBSTRING(root.sg.s1 FROM 1 FOR 3)"
+ };
+ String[] retArray =
+ new String[] {
+ "1,abcd,abcd,abc,abcd,abc,",
+ "2,test,test,tes,test,tes,",
+ "3,abcdefg,abcdefg,abc,abcdefg,abc,",
+ };
+ resultSetEqualTest(
+ "select s1,SUBSTRING(s1,1),SUBSTRING(s1,1,3),SUBSTRING(s1 from 1),SUBSTRING(s1 from 1 for 3) from root.sg",
+ expectedHeader,
+ retArray);
+
+ // negative start position
+ expectedHeader =
+ new String[] {
+ "Time,root.sg.s1,SUBSTRING(root.sg.s1,-1),SUBSTRING(root.sg.s1,-1,3),SUBSTRING(root.sg.s1 FROM -1),SUBSTRING(root.sg.s1 FROM -1 FOR 3)"
+ };
+ retArray =
+ new String[] {
+ "1,abcd,abcd,a,abcd,a,", "2,test,test,t,test,t,", "3,abcdefg,abcdefg,a,abcdefg,a,",
+ };
+ resultSetEqualTest(
+ "select s1,SUBSTRING(s1,-1),SUBSTRING(s1,-1,3),SUBSTRING(s1 from -1),SUBSTRING(s1 from -1 for 3) from root.sg",
+ expectedHeader,
+ retArray);
+
+ // param 1 greater than input series length
+ expectedHeader =
+ new String[] {
+ "Time,root.sg.s1,SUBSTRING(root.sg.s1,11),SUBSTRING(root.sg.s1,11,13),SUBSTRING(root.sg.s1 FROM 11),SUBSTRING(root.sg.s1 FROM 11 FOR 13)"
+ };
+ retArray =
+ new String[] {
+ "1,abcd,,,,,", "2,test,,,,,", "3,abcdefg,,,,,",
+ };
+ resultSetEqualTest(
+ "select s1,SUBSTRING(s1,11),SUBSTRING(s1,11,13),SUBSTRING(s1 from 11),SUBSTRING(s1 from 11 for 13) from root.sg",
+ expectedHeader,
+ retArray);
+
+ // param 2 greater than input series length
+ expectedHeader =
+ new String[] {
+ "Time,root.sg.s1,SUBSTRING(root.sg.s1,-1),SUBSTRING(root.sg.s1,-1,13),SUBSTRING(root.sg.s1 FROM -1),SUBSTRING(root.sg.s1 FROM -1 FOR 13)"
+ };
+ retArray =
+ new String[] {
+ "1,abcd,abcd,abcd,abcd,abcd,",
+ "2,test,test,test,test,test,",
+ "3,abcdefg,abcdefg,abcdefg,abcdefg,abcdefg,",
+ };
+ resultSetEqualTest(
+ "select s1,SUBSTRING(s1,-1),SUBSTRING(s1,-1,13),SUBSTRING(s1 from -1),SUBSTRING(s1 from -1 for 13) from root.sg",
+ expectedHeader,
+ retArray);
+ }
+
+ @Test
+ public void testOldTransformer() {
+ // normal
+ String[] expectedHeader =
+ new String[] {
+ "Time,root.sg.s1,change_points(root.sg.s1),SUBSTRING(root.sg.s1,1),SUBSTRING(root.sg.s1,1,3),SUBSTRING(root.sg.s1 FROM 1),SUBSTRING(root.sg.s1 FROM 1 FOR 3)"
+ };
+ String[] retArray =
+ new String[] {
+ "1,abcd,abcd,abcd,abc,abcd,abc,",
+ "2,test,test,test,tes,test,tes,",
+ "3,abcdefg,abcdefg,abcdefg,abc,abcdefg,abc,",
+ };
+ resultSetEqualTest(
+ "select s1,change_points(s1),SUBSTRING(s1,1),SUBSTRING(s1,1,3),SUBSTRING(s1 from 1),SUBSTRING(s1 from 1 for 3) from root.sg",
+ expectedHeader,
+ retArray);
+
+ // negative start position
+ expectedHeader =
+ new String[] {
+ "Time,root.sg.s1,change_points(root.sg.s1),SUBSTRING(root.sg.s1,-1),SUBSTRING(root.sg.s1,-1,3),SUBSTRING(root.sg.s1 FROM -1),SUBSTRING(root.sg.s1 FROM -1 FOR 3)"
+ };
+ retArray =
+ new String[] {
+ "1,abcd,abcd,abcd,a,abcd,a,",
+ "2,test,test,test,t,test,t,",
+ "3,abcdefg,abcdefg,abcdefg,a,abcdefg,a,",
+ };
+ resultSetEqualTest(
+ "select s1,change_points(s1),SUBSTRING(s1,-1),SUBSTRING(s1,-1,3),SUBSTRING(s1 from -1),SUBSTRING(s1 from -1 for 3) from root.sg",
+ expectedHeader,
+ retArray);
+
+ // param 1 greater than input series length
+ expectedHeader =
+ new String[] {
+ "Time,root.sg.s1,change_points(root.sg.s1),SUBSTRING(root.sg.s1,11),SUBSTRING(root.sg.s1,11,13),SUBSTRING(root.sg.s1 FROM 11),SUBSTRING(root.sg.s1 FROM 11 FOR 13)"
+ };
+ retArray =
+ new String[] {
+ "1,abcd,abcd,,,,,", "2,test,test,,,,,", "3,abcdefg,abcdefg,,,,,",
+ };
+ resultSetEqualTest(
+ "select s1,change_points(s1),SUBSTRING(s1,11),SUBSTRING(s1,11,13),SUBSTRING(s1 from 11),SUBSTRING(s1 from 11 for 13) from root.sg",
+ expectedHeader,
+ retArray);
+
+ // param 2 greater than input series length
+ expectedHeader =
+ new String[] {
+ "Time,root.sg.s1,change_points(root.sg.s1),SUBSTRING(root.sg.s1,-1),SUBSTRING(root.sg.s1,-1,13),SUBSTRING(root.sg.s1 FROM -1),SUBSTRING(root.sg.s1 FROM -1 FOR 13)"
+ };
+ retArray =
+ new String[] {
+ "1,abcd,abcd,abcd,abcd,abcd,abcd,",
+ "2,test,test,test,test,test,test,",
+ "3,abcdefg,abcdefg,abcdefg,abcdefg,abcdefg,abcdefg,",
+ };
+ resultSetEqualTest(
+ "select s1,change_points(s1),SUBSTRING(s1,-1),SUBSTRING(s1,-1,13),SUBSTRING(s1 from -1),SUBSTRING(s1 from -1 for 13) from root.sg",
+ expectedHeader,
+ retArray);
+ }
+
+ @Test
+ public void testRoundBooleanAndText() {
+ // using substring without start and end position.
+ assertTestFail(
+ "select s1,SUBSTRING(s1) from root.sg",
+ TSStatusCode.SEMANTIC_ERROR.getStatusCode()
+ + ": Argument exception,the scalar function [SUBSTRING] needs at least one argument,it must be a signed integer");
+
+ // wrong input type
+ assertTestFail(
+ "select SUBSTRING(s2,1,1) from root.**",
+ TSStatusCode.SEMANTIC_ERROR.getStatusCode()
+ + ": Input series of Scalar function [SUBSTRING] only supports numeric data types [TEXT]");
+
+ // wrong input type
+ assertTestFail(
+ "select SUBSTRING(s3,1,1) from root.**",
+ TSStatusCode.SEMANTIC_ERROR.getStatusCode()
+ + ": Input series of Scalar function [SUBSTRING] only supports numeric data types [TEXT]");
+
+ // wrong input type
+ assertTestFail(
+ "select SUBSTRING(s4,1,1) from root.**",
+ TSStatusCode.SEMANTIC_ERROR.getStatusCode()
+ + ": Input series of Scalar function [SUBSTRING] only supports numeric data types [TEXT]");
+
+ // wrong input type
+ assertTestFail(
+ "select SUBSTRING(s5,1,1) from root.**",
+ TSStatusCode.SEMANTIC_ERROR.getStatusCode()
+ + ": Input series of Scalar function [SUBSTRING] only supports numeric data types [TEXT]");
+
+ // wrong input type
+ assertTestFail(
+ "select SUBSTRING(s6,1,1) from root.**",
+ TSStatusCode.SEMANTIC_ERROR.getStatusCode()
+ + ": Input series of Scalar function [SUBSTRING] only supports numeric data types [TEXT]");
+
+ // using substring with float start position
+ assertTestFail(
+ "select SUBSTRING(s1,1.0,1) from root.**",
+ TSStatusCode.SEMANTIC_ERROR.getStatusCode()
+ + ": Argument exception,the scalar function [SUBSTRING] needs at least one argument,it must be a signed integer");
+
+ // using substring with float start and length
+ assertTestFail(
+ "select SUBSTRING(s1,1.0,1.1) from root.**",
+ TSStatusCode.SEMANTIC_ERROR.getStatusCode()
+ + ": Argument exception,the scalar function [SUBSTRING] needs at least one argument,it must be a signed integer");
+
+ // negative characters length
+ assertTestFail(
+ "select SUBSTRING(s1,1,-10) from root.**",
+ TSStatusCode.EXECUTE_STATEMENT_ERROR.getStatusCode()
+ + ": Argument exception,the scalar function [SUBSTRING] substring length has to be greater than 0");
+ }
+}
diff --git a/integration-test/src/test/java/org/apache/iotdb/db/it/udf/IoTDBUDTFBuiltinScalarFunctionIT.java b/integration-test/src/test/java/org/apache/iotdb/db/it/udf/IoTDBUDTFBuiltinScalarFunctionIT.java
index 51f894438d..3d63fdd27f 100644
--- a/integration-test/src/test/java/org/apache/iotdb/db/it/udf/IoTDBUDTFBuiltinScalarFunctionIT.java
+++ b/integration-test/src/test/java/org/apache/iotdb/db/it/udf/IoTDBUDTFBuiltinScalarFunctionIT.java
@@ -1131,10 +1131,9 @@ public class IoTDBUDTFBuiltinScalarFunctionIT {
private void testSubStr(Statement statement) throws SQLException {
ResultSet resultSet =
statement.executeQuery(
- "select s1, substr(s1, \"start\"=\"3\", \"end\"=\"7\") "
- + "from root.testStringFunctions.d1");
+ "select s1, substring(s1, 3, 7) " + "from root.testStringFunctions.d1");
while (resultSet.next()) {
- assertEquals(resultSet.getString(2).substring(3, 7), resultSet.getString(3));
+ assertEquals(resultSet.getString(2).substring(2, 9), resultSet.getString(3));
}
}
diff --git a/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/BuiltinScalarFunction.java b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/BuiltinScalarFunction.java
index d42ad6e39b..eaa41f7f39 100644
--- a/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/BuiltinScalarFunction.java
+++ b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/BuiltinScalarFunction.java
@@ -31,6 +31,7 @@ public enum BuiltinScalarFunction {
CAST("cast"),
ROUND("round"),
REPLACE("replace"),
+ SUBSTRING("substring"),
;
private final String functionName;
diff --git a/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/BuiltinTimeSeriesGeneratingFunction.java b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/BuiltinTimeSeriesGeneratingFunction.java
index d790d9aeba..bf7ddd58f2 100644
--- a/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/BuiltinTimeSeriesGeneratingFunction.java
+++ b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/BuiltinTimeSeriesGeneratingFunction.java
@@ -26,7 +26,6 @@ import org.apache.iotdb.commons.udf.builtin.String.UDTFStartsWith;
import org.apache.iotdb.commons.udf.builtin.String.UDTFStrCompare;
import org.apache.iotdb.commons.udf.builtin.String.UDTFStrLength;
import org.apache.iotdb.commons.udf.builtin.String.UDTFStrLocate;
-import org.apache.iotdb.commons.udf.builtin.String.UDTFSubstr;
import org.apache.iotdb.commons.udf.builtin.String.UDTFTrim;
import org.apache.iotdb.commons.udf.builtin.String.UDTFUpper;
@@ -61,7 +60,6 @@ public enum BuiltinTimeSeriesGeneratingFunction {
STRING_STARTS_WITH("STARTSWITH", UDTFStartsWith.class),
STRING_ENDS_WITH("ENDSWITH", UDTFEndsWith.class),
STRING_CONCAT("CONCAT", UDTFConcat.class),
- STRING_SUBSTR("SUBSTR", UDTFSubstr.class),
STRING_UPPER("UPPER", UDTFUpper.class),
STRING_LOWER("LOWER", UDTFLower.class),
STRING_TRIM("TRIM", UDTFTrim.class),
diff --git a/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFSubstr.java b/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFSubstr.java
deleted file mode 100644
index 5fb5df9d20..0000000000
--- a/node-commons/src/main/java/org/apache/iotdb/commons/udf/builtin/String/UDTFSubstr.java
+++ /dev/null
@@ -1,70 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-package org.apache.iotdb.commons.udf.builtin.String;
-
-import org.apache.iotdb.udf.api.UDTF;
-import org.apache.iotdb.udf.api.access.Row;
-import org.apache.iotdb.udf.api.collector.PointCollector;
-import org.apache.iotdb.udf.api.customizer.config.UDTFConfigurations;
-import org.apache.iotdb.udf.api.customizer.parameter.UDFParameterValidator;
-import org.apache.iotdb.udf.api.customizer.parameter.UDFParameters;
-import org.apache.iotdb.udf.api.customizer.strategy.RowByRowAccessStrategy;
-import org.apache.iotdb.udf.api.type.Type;
-
-/*This function return a substring from target string, starting at position start and ending at position end - 1.
-If parameter "end" is not existed or more than length of target, return the substring from start to end of target.*/
-public class UDTFSubstr implements UDTF {
-
- int start;
- int end;
-
- @Override
- public void validate(UDFParameterValidator validator) throws Exception {
- int start = validator.getParameters().getInt("start");
- validator
- .validateInputSeriesNumber(1)
- .validateInputSeriesDataType(0, Type.TEXT)
- .validate(
- startPosition -> ((int) startPosition) >= 0,
- "start should be more or equal than 0",
- start)
- .validate(
- end -> ((int) end) >= start,
- "end should be more or equal than start",
- validator.getParameters().getIntOrDefault("end", Integer.MAX_VALUE));
- }
-
- @Override
- public void beforeStart(UDFParameters parameters, UDTFConfigurations configurations)
- throws Exception {
- start = parameters.getInt("start");
- end = parameters.getIntOrDefault("end", Integer.MAX_VALUE);
- configurations.setAccessStrategy(new RowByRowAccessStrategy()).setOutputDataType(Type.TEXT);
- }
-
- @Override
- public void transform(Row row, PointCollector collector) throws Exception {
- String series = row.getString(0);
- collector.putString(
- row.getTime(),
- (end >= series.length())
- ? row.getString(0).substring(start)
- : row.getString(0).substring(start, end));
- }
-}
diff --git a/server/src/main/java/org/apache/iotdb/db/constant/SqlConstant.java b/server/src/main/java/org/apache/iotdb/db/constant/SqlConstant.java
index 68a3e8f145..4f18776b6b 100644
--- a/server/src/main/java/org/apache/iotdb/db/constant/SqlConstant.java
+++ b/server/src/main/java/org/apache/iotdb/db/constant/SqlConstant.java
@@ -70,6 +70,14 @@ public class SqlConstant {
public static final String REPLACE_FROM = "FROM";
public static final String REPLACE_TO = "TO";
+ public static final String SUBSTRING_FUNCTION = "SUBSTRING";
+ public static final String SUBSTRING_START = "startPosition";
+ public static final String SUBSTRING_LENGTH = "length";
+
+ public static final String SUBSTRING_FROM = "FROM";
+ public static final String SUBSTRING_IS_STANDARD = "isStandard";
+ public static final String SUBSTRING_FOR = "FOR";
+
public static String[] getSingleRootArray() {
return SINGLE_ROOT_ARRAY;
}
diff --git a/server/src/main/java/org/apache/iotdb/db/mpp/plan/expression/multi/builtin/BuiltInScalarFunctionHelperFactory.java b/server/src/main/java/org/apache/iotdb/db/mpp/plan/expression/multi/builtin/BuiltInScalarFunctionHelperFactory.java
index f47128b81e..4726c88a2c 100644
--- a/server/src/main/java/org/apache/iotdb/db/mpp/plan/expression/multi/builtin/BuiltInScalarFunctionHelperFactory.java
+++ b/server/src/main/java/org/apache/iotdb/db/mpp/plan/expression/multi/builtin/BuiltInScalarFunctionHelperFactory.java
@@ -23,6 +23,7 @@ import org.apache.iotdb.db.mpp.plan.expression.multi.builtin.helper.CastFunction
import org.apache.iotdb.db.mpp.plan.expression.multi.builtin.helper.DiffFunctionHelper;
import org.apache.iotdb.db.mpp.plan.expression.multi.builtin.helper.ReplaceFunctionHelper;
import org.apache.iotdb.db.mpp.plan.expression.multi.builtin.helper.RoundFunctionHelper;
+import org.apache.iotdb.db.mpp.plan.expression.multi.builtin.helper.SubStringFunctionHelper;
public class BuiltInScalarFunctionHelperFactory {
public static BuiltInScalarFunctionHelper createHelper(String functionName) {
@@ -34,6 +35,8 @@ public class BuiltInScalarFunctionHelperFactory {
return new CastFunctionHelper();
case "REPLACE":
return new ReplaceFunctionHelper();
+ case "SUBSTRING":
+ return new SubStringFunctionHelper();
case "ROUND":
return new RoundFunctionHelper();
default:
diff --git a/server/src/main/java/org/apache/iotdb/db/mpp/plan/expression/multi/builtin/helper/SubStringFunctionHelper.java b/server/src/main/java/org/apache/iotdb/db/mpp/plan/expression/multi/builtin/helper/SubStringFunctionHelper.java
new file mode 100644
index 0000000000..b6b8ce41fa
--- /dev/null
+++ b/server/src/main/java/org/apache/iotdb/db/mpp/plan/expression/multi/builtin/helper/SubStringFunctionHelper.java
@@ -0,0 +1,129 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.iotdb.db.mpp.plan.expression.multi.builtin.helper;
+
+import org.apache.iotdb.db.exception.sql.SemanticException;
+import org.apache.iotdb.db.mpp.plan.expression.multi.FunctionExpression;
+import org.apache.iotdb.db.mpp.plan.expression.multi.builtin.BuiltInScalarFunctionHelper;
+import org.apache.iotdb.db.mpp.transformation.api.LayerPointReader;
+import org.apache.iotdb.db.mpp.transformation.dag.column.ColumnTransformer;
+import org.apache.iotdb.db.mpp.transformation.dag.column.unary.scalar.SubStringFunctionColumnTransformer;
+import org.apache.iotdb.db.mpp.transformation.dag.transformer.Transformer;
+import org.apache.iotdb.db.mpp.transformation.dag.transformer.unary.scalar.SubStringFunctionTransformer;
+import org.apache.iotdb.tsfile.file.metadata.enums.TSDataType;
+import org.apache.iotdb.tsfile.read.common.type.TypeFactory;
+
+import java.util.LinkedHashMap;
+import java.util.Map;
+
+import static org.apache.iotdb.db.constant.SqlConstant.SUBSTRING_FOR;
+import static org.apache.iotdb.db.constant.SqlConstant.SUBSTRING_FROM;
+import static org.apache.iotdb.db.constant.SqlConstant.SUBSTRING_IS_STANDARD;
+import static org.apache.iotdb.db.constant.SqlConstant.SUBSTRING_LENGTH;
+import static org.apache.iotdb.db.constant.SqlConstant.SUBSTRING_START;
+
+public class SubStringFunctionHelper implements BuiltInScalarFunctionHelper {
+ public static final String BLANK_STRING = " ";
+ public static final String COMMA_STRING = ",";
+
+ @Override
+ public void checkBuiltInScalarFunctionInputSize(FunctionExpression functionExpression)
+ throws SemanticException {
+ if (functionExpression.getFunctionAttributes().isEmpty()
+ || functionExpression.getExpressions().size() != 1) {
+ throw new SemanticException(
+ "Argument exception,the scalar function [SUBSTRING] needs at least one argument,it must be a signed integer");
+ }
+ }
+
+ @Override
+ public void checkBuiltInScalarFunctionInputDataType(TSDataType tsDataType)
+ throws SemanticException {
+ if (tsDataType.getType() == TSDataType.TEXT.getType()) {
+ return;
+ }
+ throw new SemanticException(
+ "Input series of Scalar function [SUBSTRING] only supports numeric data types [TEXT]");
+ }
+
+ @Override
+ public TSDataType getBuiltInScalarFunctionReturnType(FunctionExpression functionExpression) {
+ return TSDataType.TEXT;
+ }
+
+ @Override
+ public ColumnTransformer getBuiltInScalarFunctionColumnTransformer(
+ FunctionExpression expression, ColumnTransformer columnTransformer) {
+ LinkedHashMap<String, String> functionAttributes = expression.getFunctionAttributes();
+ String subStringLength =
+ functionAttributes.getOrDefault(SUBSTRING_LENGTH, String.valueOf(Integer.MAX_VALUE));
+ if (Long.parseLong(subStringLength) < 0) {
+ throw new SemanticException(
+ "Argument exception,the scalar function [SUBSTRING] substring length has to be greater than 0");
+ }
+
+ return new SubStringFunctionColumnTransformer(
+ TypeFactory.getType(this.getBuiltInScalarFunctionReturnType(expression)),
+ columnTransformer,
+ Integer.parseInt(functionAttributes.getOrDefault(SUBSTRING_START, "0")),
+ Integer.parseInt(subStringLength));
+ }
+
+ @Override
+ public Transformer getBuiltInScalarFunctionTransformer(
+ FunctionExpression expression, LayerPointReader layerPointReader) {
+ LinkedHashMap<String, String> functionAttributes = expression.getFunctionAttributes();
+ String subStringLength =
+ functionAttributes.getOrDefault(SUBSTRING_LENGTH, String.valueOf(Integer.MAX_VALUE));
+ if (Long.parseLong(subStringLength) < 0) {
+ throw new SemanticException(
+ "Argument exception,the scalar function [SUBSTRING] substring length has to be greater than 0");
+ }
+ return new SubStringFunctionTransformer(
+ layerPointReader,
+ Integer.parseInt(functionAttributes.getOrDefault(SUBSTRING_START, "0")),
+ Integer.parseInt(subStringLength));
+ }
+
+ @Override
+ public void appendFunctionAttributes(
+ boolean hasExpression, StringBuilder builder, Map<String, String> functionAttributes) {
+
+ if (functionAttributes.containsKey(SUBSTRING_IS_STANDARD)) {
+ builder
+ .append(BLANK_STRING)
+ .append(SUBSTRING_FROM)
+ .append(BLANK_STRING)
+ .append(functionAttributes.get(SUBSTRING_START));
+ if (functionAttributes.containsKey(SUBSTRING_LENGTH)) {
+ builder
+ .append(BLANK_STRING)
+ .append(SUBSTRING_FOR)
+ .append(BLANK_STRING)
+ .append(functionAttributes.get(SUBSTRING_LENGTH));
+ }
+ } else {
+ builder.append(COMMA_STRING).append(functionAttributes.get(SUBSTRING_START));
+ if (functionAttributes.containsKey(SUBSTRING_LENGTH)) {
+ builder.append(COMMA_STRING).append(functionAttributes.get(SUBSTRING_LENGTH));
+ }
+ }
+ }
+}
diff --git a/server/src/main/java/org/apache/iotdb/db/mpp/plan/parser/ASTVisitor.java b/server/src/main/java/org/apache/iotdb/db/mpp/plan/parser/ASTVisitor.java
index 463559c02c..7515f4076d 100644
--- a/server/src/main/java/org/apache/iotdb/db/mpp/plan/parser/ASTVisitor.java
+++ b/server/src/main/java/org/apache/iotdb/db/mpp/plan/parser/ASTVisitor.java
@@ -214,6 +214,10 @@ import static org.apache.iotdb.db.constant.SqlConstant.REPLACE_FUNCTION;
import static org.apache.iotdb.db.constant.SqlConstant.REPLACE_TO;
import static org.apache.iotdb.db.constant.SqlConstant.ROUND_FUNCTION;
import static org.apache.iotdb.db.constant.SqlConstant.ROUND_PLACES;
+import static org.apache.iotdb.db.constant.SqlConstant.SUBSTRING_FUNCTION;
+import static org.apache.iotdb.db.constant.SqlConstant.SUBSTRING_IS_STANDARD;
+import static org.apache.iotdb.db.constant.SqlConstant.SUBSTRING_LENGTH;
+import static org.apache.iotdb.db.constant.SqlConstant.SUBSTRING_START;
import static org.apache.iotdb.db.metadata.MetadataConstant.ALL_RESULT_NODES;
/** Parse AST to Statement. */
@@ -2370,6 +2374,8 @@ public class ASTVisitor extends IoTDBSqlParserBaseVisitor<Statement> {
return parseReplaceFunction(context, canUseFullPath);
} else if (context.ROUND() != null) {
return parseRoundFunction(context, canUseFullPath);
+ } else if (context.SUBSTRING() != null) {
+ return parseSubStrFunction(context, canUseFullPath);
}
throw new UnsupportedOperationException();
}
@@ -2391,6 +2397,29 @@ public class ASTVisitor extends IoTDBSqlParserBaseVisitor<Statement> {
return functionExpression;
}
+ private Expression parseSubStrFunction(
+ IoTDBSqlParser.ScalarFunctionExpressionContext subStrClause, boolean canUseFullPath) {
+ FunctionExpression functionExpression = new FunctionExpression(SUBSTRING_FUNCTION);
+ IoTDBSqlParser.SubStringExpressionContext subStringExpression =
+ subStrClause.subStringExpression();
+ functionExpression.addExpression(parseExpression(subStringExpression.input, canUseFullPath));
+ if (subStringExpression.startPosition != null) {
+ functionExpression.addAttribute(SUBSTRING_START, subStringExpression.startPosition.getText());
+ if (subStringExpression.length != null) {
+ functionExpression.addAttribute(SUBSTRING_LENGTH, subStringExpression.length.getText());
+ }
+ }
+ if (subStringExpression.from != null) {
+ functionExpression.addAttribute(SUBSTRING_IS_STANDARD, "0");
+ functionExpression.addAttribute(
+ SUBSTRING_START, parseStringLiteral(subStringExpression.from.getText()));
+ if (subStringExpression.forLength != null) {
+ functionExpression.addAttribute(SUBSTRING_LENGTH, subStringExpression.forLength.getText());
+ }
+ }
+ return functionExpression;
+ }
+
private Expression parseRoundFunction(
IoTDBSqlParser.ScalarFunctionExpressionContext roundClause, boolean canUseFullPath) {
FunctionExpression functionExpression = new FunctionExpression(ROUND_FUNCTION);
diff --git a/server/src/main/java/org/apache/iotdb/db/mpp/transformation/dag/column/unary/scalar/SubStringFunctionColumnTransformer.java b/server/src/main/java/org/apache/iotdb/db/mpp/transformation/dag/column/unary/scalar/SubStringFunctionColumnTransformer.java
new file mode 100644
index 0000000000..d04a406ab4
--- /dev/null
+++ b/server/src/main/java/org/apache/iotdb/db/mpp/transformation/dag/column/unary/scalar/SubStringFunctionColumnTransformer.java
@@ -0,0 +1,63 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.iotdb.db.mpp.transformation.dag.column.unary.scalar;
+
+import org.apache.iotdb.db.mpp.transformation.dag.column.ColumnTransformer;
+import org.apache.iotdb.db.mpp.transformation.dag.column.unary.UnaryColumnTransformer;
+import org.apache.iotdb.tsfile.read.common.block.column.Column;
+import org.apache.iotdb.tsfile.read.common.block.column.ColumnBuilder;
+import org.apache.iotdb.tsfile.read.common.type.Type;
+import org.apache.iotdb.tsfile.utils.Binary;
+
+public class SubStringFunctionColumnTransformer extends UnaryColumnTransformer {
+
+ private int beginPosition;
+ private int endPosition;
+ public static final String EMPTY_STRING = "";
+
+ public SubStringFunctionColumnTransformer(
+ Type returnType, ColumnTransformer childColumnTransformer, int beginPosition, int length) {
+ super(returnType, childColumnTransformer);
+ this.endPosition =
+ (length == Integer.MAX_VALUE ? Integer.MAX_VALUE : beginPosition + length - 1);
+ this.beginPosition = beginPosition > 0 ? beginPosition - 1 : 0;
+ }
+
+ @Override
+ protected void doTransform(Column column, ColumnBuilder columnBuilder) {
+ for (int i = 0, n = column.getPositionCount(); i < n; i++) {
+ if (!column.isNull(i)) {
+ String currentValue = column.getBinary(i).getStringValue();
+ if (beginPosition >= currentValue.length() || endPosition < 0) {
+ currentValue = EMPTY_STRING;
+ } else {
+ if (endPosition >= currentValue.length()) {
+ currentValue = currentValue.substring(beginPosition);
+ } else {
+ currentValue = currentValue.substring(beginPosition, endPosition);
+ }
+ }
+ columnBuilder.writeBinary(Binary.valueOf(currentValue));
+ } else {
+ columnBuilder.appendNull();
+ }
+ }
+ }
+}
diff --git a/server/src/main/java/org/apache/iotdb/db/mpp/transformation/dag/transformer/unary/scalar/SubStringFunctionTransformer.java b/server/src/main/java/org/apache/iotdb/db/mpp/transformation/dag/transformer/unary/scalar/SubStringFunctionTransformer.java
new file mode 100644
index 0000000000..6786968431
--- /dev/null
+++ b/server/src/main/java/org/apache/iotdb/db/mpp/transformation/dag/transformer/unary/scalar/SubStringFunctionTransformer.java
@@ -0,0 +1,63 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.iotdb.db.mpp.transformation.dag.transformer.unary.scalar;
+
+import org.apache.iotdb.db.exception.query.QueryProcessException;
+import org.apache.iotdb.db.mpp.transformation.api.LayerPointReader;
+import org.apache.iotdb.db.mpp.transformation.dag.transformer.unary.UnaryTransformer;
+import org.apache.iotdb.tsfile.file.metadata.enums.TSDataType;
+import org.apache.iotdb.tsfile.utils.Binary;
+
+import java.io.IOException;
+
+import static org.apache.iotdb.db.mpp.transformation.dag.column.unary.scalar.SubStringFunctionColumnTransformer.EMPTY_STRING;
+
+public class SubStringFunctionTransformer extends UnaryTransformer {
+ private int beginPosition;
+ private int endPosition;
+
+ public SubStringFunctionTransformer(
+ LayerPointReader layerPointReader, int beginPosition, int length) {
+ super(layerPointReader);
+ this.endPosition =
+ (length == Integer.MAX_VALUE ? Integer.MAX_VALUE : beginPosition + length - 1);
+ this.beginPosition = beginPosition > 0 ? beginPosition - 1 : 0;
+ }
+
+ @Override
+ public TSDataType getDataType() {
+ return TSDataType.TEXT;
+ }
+
+ @Override
+ protected void transformAndCache() throws QueryProcessException, IOException {
+ String currentValue = layerPointReader.currentBinary().getStringValue();
+ if (beginPosition >= currentValue.length() || endPosition < 0) {
+ currentValue = EMPTY_STRING;
+ } else {
+ if (endPosition >= currentValue.length()) {
+ currentValue = currentValue.substring(beginPosition);
+ } else {
+ currentValue = currentValue.substring(beginPosition, endPosition);
+ }
+ }
+ cachedBinary = Binary.valueOf(currentValue);
+ }
+}