This is an automated email from the ASF dual-hosted git repository.
zclll pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new a076fea62af [feature](function) support MySQL dialect of function
substr and position (#55914)
a076fea62af is described below
commit a076fea62afd6f453bb914b7780be891849c7041
Author: admiring_xm <[email protected]>
AuthorDate: Wed Sep 24 15:55:53 2025 +0800
[feature](function) support MySQL dialect of function substr and position
(#55914)
support MySQL dialect of function substr(substring) and position
```sql
SUBSTRING(<str> FROM <pos> [FOR <len>])
SUBSTR(<str> FROM <pos> [FOR <len>])
```
```sql
POSITION ( <substr> IN <str> )
POSITION ( <substr>, <str> [, <pos>] )
```
---
be/src/vec/functions/function_string.cpp | 1 +
.../antlr4/org/apache/doris/nereids/DorisLexer.g4 | 3 +
.../antlr4/org/apache/doris/nereids/DorisParser.g4 | 6 +
.../doris/catalog/BuiltinScalarFunctions.java | 2 +-
.../doris/nereids/parser/LogicalPlanBuilder.java | 19 ++
.../doris/nereids/parser/NereidsParserTest.java | 126 +++++++++++++
.../data/nereids_function_p0/scalar_function/P.out | 174 ++++++++++++++++++
.../data/nereids_function_p0/scalar_function/S.out | 116 ++++++++++++
.../fold_constant_string_arithmatic.out | 202 +++++++++++++++++++++
.../nereids_function_p0/scalar_function/P.groovy | 12 ++
.../nereids_function_p0/scalar_function/S.groovy | 8 +
.../fold_constant_string_arithmatic.groovy | 110 +++++++++++
12 files changed, 778 insertions(+), 1 deletion(-)
diff --git a/be/src/vec/functions/function_string.cpp
b/be/src/vec/functions/function_string.cpp
index b81b4aff385..f991a56e950 100644
--- a/be/src/vec/functions/function_string.cpp
+++ b/be/src/vec/functions/function_string.cpp
@@ -1485,6 +1485,7 @@ void register_function_string(SimpleFunctionFactory&
factory) {
factory.register_alias(FunctionStringUTF8Length::name, "character_length");
factory.register_alias(FunctionStringDigestOneArg<SM3Sum>::name, "sm3");
factory.register_alias(FunctionStringDigestSHA1::name, "sha");
+ factory.register_alias(FunctionStringLocatePos::name, "position");
}
} // namespace doris::vectorized
diff --git a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4
b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4
index df0b3d0234c..d4b3b1b09ce 100644
--- a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4
+++ b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4
@@ -413,6 +413,7 @@ PROCESS: 'PROCESS';
PLUGIN: 'PLUGIN';
PLUGINS: 'PLUGINS';
POLICY: 'POLICY';
+POSITION: 'POSITION';
PRECEDING: 'PRECEDING';
PREPARE: 'PREPARE';
PRIMARY: 'PRIMARY';
@@ -509,6 +510,8 @@ STREAM: 'STREAM';
STREAMING: 'STREAMING';
STRING: 'STRING';
STRUCT: 'STRUCT';
+SUBSTR: 'SUBSTR';
+SUBSTRING: 'SUBSTRING';
SUM: 'SUM';
SUPERUSER: 'SUPERUSER';
SWITCH: 'SWITCH';
diff --git a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
index e24bda7f98b..1441aced294 100644
--- a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
+++ b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
@@ -1601,6 +1601,9 @@ primaryExpression
(OVER windowSpec)?
#groupConcat
| TRIM LEFT_PAREN
((BOTH | LEADING | TRAILING) expression? | expression) FROM expression
RIGHT_PAREN #trim
+ | (SUBSTR | SUBSTRING) LEFT_PAREN
+ expression FROM expression (FOR expression)? RIGHT_PAREN
#substring
+ | POSITION LEFT_PAREN expression IN expression RIGHT_PAREN
#position
| functionCallExpression
#functionCall
| value=primaryExpression LEFT_BRACKET index=valueExpression RIGHT_BRACKET
#elementAt
| value=primaryExpression LEFT_BRACKET begin=valueExpression
@@ -2097,6 +2100,7 @@ nonReserved
| PLUGIN
| PLUGINS
| POLICY
+ | POSITION
| PRIVILEGES
| PROC
| PROCESS
@@ -2167,6 +2171,8 @@ nonReserved
| STREAMING
| STRING
| STRUCT
+ | SUBSTR
+ | SUBSTRING
| SUM
| TABLES
| TAG
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
index 6508fd4dbf2..78c79114f0d 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
@@ -802,7 +802,7 @@ public class BuiltinScalarFunctions implements
FunctionHelper {
scalar(Crc32Internal.class, "crc32_internal"),
scalar(Like.class, "like"),
scalar(Ln.class, "ln", "dlog1"),
- scalar(Locate.class, "locate"),
+ scalar(Locate.class, "position", "locate"),
scalar(Log.class, "log"),
scalar(Log10.class, "log10"),
scalar(Log2.class, "log2"),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
index 007738f4fac..c790a0a50c0 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
@@ -273,6 +273,7 @@ import
org.apache.doris.nereids.DorisParser.PartitionValueListContext;
import org.apache.doris.nereids.DorisParser.PartitionsDefContext;
import org.apache.doris.nereids.DorisParser.PauseMTMVContext;
import org.apache.doris.nereids.DorisParser.PlanTypeContext;
+import org.apache.doris.nereids.DorisParser.PositionContext;
import org.apache.doris.nereids.DorisParser.PredicateContext;
import org.apache.doris.nereids.DorisParser.PredicatedContext;
import org.apache.doris.nereids.DorisParser.PrimitiveDataTypeContext;
@@ -434,6 +435,7 @@ import
org.apache.doris.nereids.DorisParser.StringLiteralContext;
import org.apache.doris.nereids.DorisParser.StructLiteralContext;
import org.apache.doris.nereids.DorisParser.SubqueryContext;
import org.apache.doris.nereids.DorisParser.SubqueryExpressionContext;
+import org.apache.doris.nereids.DorisParser.SubstringContext;
import org.apache.doris.nereids.DorisParser.SupportedUnsetStatementContext;
import org.apache.doris.nereids.DorisParser.SwitchCatalogContext;
import org.apache.doris.nereids.DorisParser.SyncContext;
@@ -3099,6 +3101,23 @@ public class LogicalPlanBuilder extends
DorisParserBaseVisitor<Object> {
});
}
+ @Override
+ public Object visitSubstring(SubstringContext ctx) {
+ return ParserUtils.withOrigin(ctx, () -> {
+ List<Expression> params = visit(ctx.expression(),
Expression.class);
+ String name = ctx.SUBSTR() != null ? "substr" : "substring";
+ return processUnboundFunction(ctx, null, name, false, params,
null, null);
+ });
+ }
+
+ @Override
+ public Object visitPosition(PositionContext ctx) {
+ return ParserUtils.withOrigin(ctx, () -> {
+ List<Expression> params = visit(ctx.expression(),
Expression.class);
+ return processUnboundFunction(ctx, null, "position", false,
params, null, null);
+ });
+ }
+
@Override
public Expression
visitFunctionCallExpression(DorisParser.FunctionCallExpressionContext ctx) {
return ParserUtils.withOrigin(ctx, () -> {
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/parser/NereidsParserTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/parser/NereidsParserTest.java
index b104f34a8bb..fdcda7f1b74 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/parser/NereidsParserTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/parser/NereidsParserTest.java
@@ -31,6 +31,7 @@ import org.apache.doris.nereids.trees.expressions.Cast;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.OrderExpression;
import org.apache.doris.nereids.trees.expressions.literal.DecimalLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.IntegerLikeLiteral;
import org.apache.doris.nereids.trees.expressions.literal.StringLikeLiteral;
import org.apache.doris.nereids.trees.plans.DistributeType;
import org.apache.doris.nereids.trees.plans.JoinType;
@@ -996,6 +997,131 @@ public class NereidsParserTest extends ParserTestBase {
Assertions.assertThrowsExactly(ParseException.class, () ->
parser.parseExpression("trim(both '1')"));
}
+ @Test
+ public void testSubstring() {
+ NereidsParser parser = new NereidsParser();
+ String sql;
+ Expression e;
+ UnboundFunction unboundFunction;
+
+ sql = "substring('Sakila' FROM -4 FOR 2)";
+ e = parser.parseExpression(sql);
+ Assertions.assertInstanceOf(UnboundFunction.class, e);
+ unboundFunction = (UnboundFunction) e;
+ Assertions.assertEquals("substring", unboundFunction.getName());
+ Assertions.assertEquals(3, unboundFunction.arity());
+ Assertions.assertEquals("Sakila", ((StringLikeLiteral)
unboundFunction.child(0)).getStringValue());
+ Assertions.assertEquals(-4, ((IntegerLikeLiteral)
unboundFunction.child(1)).getIntValue());
+ Assertions.assertEquals(2, ((IntegerLikeLiteral)
unboundFunction.child(2)).getIntValue());
+
+ sql = "substring('Sakila', -5, 3)";
+ e = parser.parseExpression(sql);
+ Assertions.assertInstanceOf(UnboundFunction.class, e);
+ unboundFunction = (UnboundFunction) e;
+ Assertions.assertEquals("substring", unboundFunction.getName());
+ Assertions.assertEquals(3, unboundFunction.arity());
+ Assertions.assertEquals("Sakila", ((StringLikeLiteral)
unboundFunction.child(0)).getStringValue());
+ Assertions.assertEquals(-5, ((IntegerLikeLiteral)
unboundFunction.child(1)).getIntValue());
+ Assertions.assertEquals(3, ((IntegerLikeLiteral)
unboundFunction.child(2)).getIntValue());
+
+ sql = "substring('foobarbar' FROM 4)";
+ e = parser.parseExpression(sql);
+ Assertions.assertInstanceOf(UnboundFunction.class, e);
+ unboundFunction = (UnboundFunction) e;
+ Assertions.assertEquals("substring", unboundFunction.getName());
+ Assertions.assertEquals(2, unboundFunction.arity());
+ Assertions.assertEquals("foobarbar", ((StringLikeLiteral)
unboundFunction.child(0)).getStringValue());
+ Assertions.assertEquals(4, ((IntegerLikeLiteral)
unboundFunction.child(1)).getIntValue());
+
+ sql = "substring('Quadratically', 5)";
+ e = parser.parseExpression(sql);
+ Assertions.assertInstanceOf(UnboundFunction.class, e);
+ unboundFunction = (UnboundFunction) e;
+ Assertions.assertEquals("substring", unboundFunction.getName());
+ Assertions.assertEquals(2, unboundFunction.arity());
+ Assertions.assertEquals("Quadratically", ((StringLikeLiteral)
unboundFunction.child(0)).getStringValue());
+ Assertions.assertEquals(5, ((IntegerLikeLiteral)
unboundFunction.child(1)).getIntValue());
+
+ Assertions.assertThrowsExactly(ParseException.class, () ->
parser.parseExpression("substring('Sakila' for 2)"));
+ Assertions.assertThrowsExactly(ParseException.class, () ->
parser.parseExpression("substring('Sakila' from for)"));
+ Assertions.assertThrowsExactly(ParseException.class, () ->
parser.parseExpression("substring('Sakila' from)"));
+ Assertions.assertThrowsExactly(ParseException.class, () ->
parser.parseExpression("substring(from 1)"));
+ Assertions.assertThrowsExactly(ParseException.class, () ->
parser.parseExpression("substring(for 1)"));
+ }
+
+ @Test
+ public void testSubstr() {
+ NereidsParser parser = new NereidsParser();
+ String sql;
+ Expression e;
+ UnboundFunction unboundFunction;
+
+ sql = "substr('Sakila' FROM -4 FOR 2)";
+ e = parser.parseExpression(sql);
+ Assertions.assertInstanceOf(UnboundFunction.class, e);
+ unboundFunction = (UnboundFunction) e;
+ Assertions.assertEquals("substr", unboundFunction.getName());
+ Assertions.assertEquals(3, unboundFunction.arity());
+ Assertions.assertEquals("Sakila", ((StringLikeLiteral)
unboundFunction.child(0)).getStringValue());
+ Assertions.assertEquals(-4, ((IntegerLikeLiteral)
unboundFunction.child(1)).getIntValue());
+ Assertions.assertEquals(2, ((IntegerLikeLiteral)
unboundFunction.child(2)).getIntValue());
+
+ sql = "substr('Sakila', -5, 3)";
+ e = parser.parseExpression(sql);
+ Assertions.assertInstanceOf(UnboundFunction.class, e);
+ unboundFunction = (UnboundFunction) e;
+ Assertions.assertEquals("substr", unboundFunction.getName());
+ Assertions.assertEquals(3, unboundFunction.arity());
+ Assertions.assertEquals("Sakila", ((StringLikeLiteral)
unboundFunction.child(0)).getStringValue());
+ Assertions.assertEquals(-5, ((IntegerLikeLiteral)
unboundFunction.child(1)).getIntValue());
+ Assertions.assertEquals(3, ((IntegerLikeLiteral)
unboundFunction.child(2)).getIntValue());
+
+ sql = "substr('foobarbar' FROM 4)";
+ e = parser.parseExpression(sql);
+ Assertions.assertInstanceOf(UnboundFunction.class, e);
+ unboundFunction = (UnboundFunction) e;
+ Assertions.assertEquals("substr", unboundFunction.getName());
+ Assertions.assertEquals(2, unboundFunction.arity());
+ Assertions.assertEquals("foobarbar", ((StringLikeLiteral)
unboundFunction.child(0)).getStringValue());
+ Assertions.assertEquals(4, ((IntegerLikeLiteral)
unboundFunction.child(1)).getIntValue());
+
+ sql = "substr('Quadratically', 5)";
+ e = parser.parseExpression(sql);
+ Assertions.assertInstanceOf(UnboundFunction.class, e);
+ unboundFunction = (UnboundFunction) e;
+ Assertions.assertEquals("substr", unboundFunction.getName());
+ Assertions.assertEquals(2, unboundFunction.arity());
+ Assertions.assertEquals("Quadratically", ((StringLikeLiteral)
unboundFunction.child(0)).getStringValue());
+ Assertions.assertEquals(5, ((IntegerLikeLiteral)
unboundFunction.child(1)).getIntValue());
+
+ Assertions.assertThrowsExactly(ParseException.class, () ->
parser.parseExpression("substr('Sakila' for 2)"));
+ Assertions.assertThrowsExactly(ParseException.class, () ->
parser.parseExpression("substr('Sakila' from for)"));
+ Assertions.assertThrowsExactly(ParseException.class, () ->
parser.parseExpression("substr('Sakila' from)"));
+ Assertions.assertThrowsExactly(ParseException.class, () ->
parser.parseExpression("substr(from 1)"));
+ Assertions.assertThrowsExactly(ParseException.class, () ->
parser.parseExpression("substr(for 1)"));
+ }
+
+ @Test
+ public void testPositon() {
+ NereidsParser parser = new NereidsParser();
+ String sql;
+ Expression e;
+ UnboundFunction unboundFunction;
+
+ sql = "position('bar' in 'foobarbar')";
+ e = parser.parseExpression(sql);
+ Assertions.assertInstanceOf(UnboundFunction.class, e);
+ unboundFunction = (UnboundFunction) e;
+ Assertions.assertEquals("position", unboundFunction.getName());
+ Assertions.assertEquals(2, unboundFunction.arity());
+ Assertions.assertEquals("bar", ((StringLikeLiteral)
unboundFunction.child(0)).getStringValue());
+ Assertions.assertEquals("foobarbar", ((StringLikeLiteral)
unboundFunction.child(1)).getStringValue());
+
+ Assertions.assertThrowsExactly(ParseException.class, () ->
parser.parseExpression("position('bar' in)"));
+ Assertions.assertThrowsExactly(ParseException.class, () ->
parser.parseExpression("position(in 'foobarbar')"));
+ Assertions.assertThrowsExactly(ParseException.class, () ->
parser.parseExpression("position(in)"));
+ }
+
@Test
public void testNoBackSlashEscapes() {
testNoBackSlashEscapes("''", "", "");
diff --git a/regression-test/data/nereids_function_p0/scalar_function/P.out
b/regression-test/data/nereids_function_p0/scalar_function/P.out
index e69cf1f91d1..7fc20a43bda 100644
--- a/regression-test/data/nereids_function_p0/scalar_function/P.out
+++ b/regression-test/data/nereids_function_p0/scalar_function/P.out
@@ -60,6 +60,180 @@
0.0
0.0
+-- !sql_position_Varchar_Varchar_In --
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+
+-- !sql_position_Varchar_Varchar_notnull_In --
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+
+-- !sql_position_String_String_In --
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+
+-- !sql_position_String_String_notnull_In --
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+
+-- !sql_position_Varchar_Varchar --
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+
+-- !sql_position_Varchar_Varchar_notnull --
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+
+-- !sql_position_String_String --
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+
+-- !sql_position_String_String_notnull --
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+
+-- !sql_position_Varchar_Varchar_Integer --
+\N
+1
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+
+-- !sql_position_Varchar_Varchar_Integer_notnull --
+1
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+
+-- !sql_position_String_String_Integer --
+\N
+1
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+
+-- !sql_position_String_String_Integer_notnull --
+1
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+
-- !sql_positive_BigInt --
\N
1
diff --git a/regression-test/data/nereids_function_p0/scalar_function/S.out
b/regression-test/data/nereids_function_p0/scalar_function/S.out
index 15bd526cbab..b93aa93a0b1 100644
--- a/regression-test/data/nereids_function_p0/scalar_function/S.out
+++ b/regression-test/data/nereids_function_p0/scalar_function/S.out
@@ -2727,6 +2727,122 @@ g3
+-- !sql_substring_Varchar_Integer_From --
+\N
+varchar11
+char11
+r11
+
+archar12
+har12
+12
+
+rchar13
+ar13
+3
+
+
+-- !sql_substring_Varchar_Integer_notnull_From --
+varchar11
+char11
+r11
+
+archar12
+har12
+12
+
+rchar13
+ar13
+3
+
+
+-- !sql_substring_String_Integer_From --
+\N
+string1
+ing1
+1
+
+tring2
+ng2
+
+
+ring3
+g3
+
+
+
+-- !sql_substring_String_Integer_notnull_From --
+string1
+ing1
+1
+
+tring2
+ng2
+
+
+ring3
+g3
+
+
+
+-- !sql_substring_Varchar_Integer_Integer_From_For --
+\N
+v
+char
+r11
+
+ar
+har12
+12
+
+rch
+ar13
+3
+
+
+-- !sql_substring_Varchar_Integer_Integer_notnull_From_For --
+v
+char
+r11
+
+ar
+har12
+12
+
+rch
+ar13
+3
+
+
+-- !sql_substring_String_Integer_Integer_From_For --
+\N
+s
+ing1
+1
+
+tr
+ng2
+
+
+rin
+g3
+
+
+
+-- !sql_substring_String_Integer_Integer_notnull_From_For --
+s
+ing1
+1
+
+tr
+ng2
+
+
+rin
+g3
+
+
+
-- !sql_substring_index_Varchar_Varchar_Integer --
null
varchar11
diff --git
a/regression-test/data/nereids_p0/expression/fold_constant/fold_constant_string_arithmatic.out
b/regression-test/data/nereids_p0/expression/fold_constant/fold_constant_string_arithmatic.out
new file mode 100644
index 00000000000..047323ab910
--- /dev/null
+++
b/regression-test/data/nereids_p0/expression/fold_constant/fold_constant_string_arithmatic.out
@@ -0,0 +1,202 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+5
+
+-- !sql --
+0
+
+-- !sql --
+4
+
+-- !sql --
+5
+
+-- !sql --
+1
+
+-- !sql --
+4
+
+-- !sql --
+0
+
+-- !sql --
+7
+
+-- !sql --
+0
+
+-- !sql --
+1
+
+-- !sql --
+0
+
+-- !sql --
+7
+
+-- !sql --
+0
+
+-- !sql --
+5
+
+-- !sql --
+5
+
+-- !sql --
+2
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+5
+
+-- !sql --
+0
+
+-- !sql --
+5
+
+-- !sql --
+0
+
+-- !sql --
+7
+
+-- !sql --
+9
+
+-- !sql --
+10
+
+-- !sql --
+1
+
+-- !sql --
+5
+
+-- !sql --
+0
+
+-- !sql --
+4
+
+-- !sql --
+5
+
+-- !sql --
+1
+
+-- !sql --
+4
+
+-- !sql --
+0
+
+-- !sql --
+7
+
+-- !sql --
+0
+
+-- !sql --
+1
+
+-- !sql --
+0
+
+-- !sql --
+7
+
+-- !sql --
+0
+
+-- !sql --
+5
+
+-- !sql --
+5
+
+-- !sql --
+0
+
+-- !sql --
+0
+
+-- !sql --
+5
+
+-- !sql --
+4
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+0
+
+-- !sql --
+1
+
+-- !sql --
+5
+
+-- !sql --
+0
+
+-- !sql --
+1
+
+-- !sql --
+0
+
+-- !sql --
+0
+
+-- !sql --
+5
+
+-- !sql --
+0
+
+-- !sql --
+6
+
+-- !sql --
+0
+
+-- !sql --
+7
+
+-- !sql --
+9
+
+-- !sql --
+0
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+0
+
diff --git
a/regression-test/suites/nereids_function_p0/scalar_function/P.groovy
b/regression-test/suites/nereids_function_p0/scalar_function/P.groovy
index 75611efb56c..e721fc4e579 100644
--- a/regression-test/suites/nereids_function_p0/scalar_function/P.groovy
+++ b/regression-test/suites/nereids_function_p0/scalar_function/P.groovy
@@ -32,6 +32,18 @@ suite("nereids_scalar_fn_P") {
qt_sql_pmod_BigInt_BigInt_notnull "select pmod(kbint, kbint) from
fn_test_not_nullable order by kbint, kbint"
qt_sql_pmod_Double_Double "select pmod(kdbl, kdbl) from fn_test order
by kdbl, kdbl"
qt_sql_pmod_Double_Double_notnull "select pmod(kdbl, kdbl) from
fn_test_not_nullable order by kdbl, kdbl"
+ qt_sql_position_Varchar_Varchar_In "select position(kvchrs1 in kvchrs1)
from fn_test order by kvchrs1, kvchrs1"
+ qt_sql_position_Varchar_Varchar_notnull_In "select position(kvchrs1 in
kvchrs1) from fn_test_not_nullable order by kvchrs1, kvchrs1"
+ qt_sql_position_String_String_In "select position(kstr in kstr) from
fn_test order by kstr, kstr"
+ qt_sql_position_String_String_notnull_In "select position(kstr in kstr)
from fn_test_not_nullable order by kstr, kstr"
+ qt_sql_position_Varchar_Varchar "select position(kvchrs1, kvchrs1) from
fn_test order by kvchrs1, kvchrs1"
+ qt_sql_position_Varchar_Varchar_notnull "select position(kvchrs1,
kvchrs1) from fn_test_not_nullable order by kvchrs1, kvchrs1"
+ qt_sql_position_String_String "select position(kstr, kstr) from fn_test
order by kstr, kstr"
+ qt_sql_position_String_String_notnull "select position(kstr, kstr) from
fn_test_not_nullable order by kstr, kstr"
+ qt_sql_position_Varchar_Varchar_Integer "select position(kvchrs1,
kvchrs1, kint) from fn_test order by kvchrs1, kvchrs1, kint"
+ qt_sql_position_Varchar_Varchar_Integer_notnull "select
position(kvchrs1, kvchrs1, kint) from fn_test_not_nullable order by kvchrs1,
kvchrs1, kint"
+ qt_sql_position_String_String_Integer "select position(kstr, kstr,
kint) from fn_test order by kstr, kstr, kint"
+ qt_sql_position_String_String_Integer_notnull "select position(kstr,
kstr, kint) from fn_test_not_nullable order by kstr, kstr, kint"
qt_sql_positive_BigInt "select positive(kbint) from fn_test order by
kbint"
qt_sql_positive_BigInt_notnull "select positive(kbint) from
fn_test_not_nullable order by kbint"
qt_sql_positive_Double "select positive(kdbl) from fn_test order by
kdbl"
diff --git
a/regression-test/suites/nereids_function_p0/scalar_function/S.groovy
b/regression-test/suites/nereids_function_p0/scalar_function/S.groovy
index 953c725d3c8..c3b8b179e70 100644
--- a/regression-test/suites/nereids_function_p0/scalar_function/S.groovy
+++ b/regression-test/suites/nereids_function_p0/scalar_function/S.groovy
@@ -255,6 +255,14 @@ suite("nereids_scalar_fn_S") {
qt_sql_substring_Varchar_Integer_Integer_notnull "select
substring(kvchrs1, kint, kint) from fn_test_not_nullable order by kvchrs1,
kint, kint"
qt_sql_substring_String_Integer_Integer "select substring(kstr, kint,
kint) from fn_test order by kstr, kint, kint"
qt_sql_substring_String_Integer_Integer_notnull "select substring(kstr,
kint, kint) from fn_test_not_nullable order by kstr, kint, kint"
+ qt_sql_substring_Varchar_Integer_From "select substring(kvchrs1 FROM
kint) from fn_test order by kvchrs1, kint"
+ qt_sql_substring_Varchar_Integer_notnull_From "select substring(kvchrs1
FROM kint) from fn_test_not_nullable order by kvchrs1, kint"
+ qt_sql_substring_String_Integer_From "select substring(kstr FROM kint)
from fn_test order by kstr, kint"
+ qt_sql_substring_String_Integer_notnull_From "select substring(kstr
FROM kint) from fn_test_not_nullable order by kstr, kint"
+ qt_sql_substring_Varchar_Integer_Integer_From_For "select
substring(kvchrs1 FROM kint FOR kint) from fn_test order by kvchrs1, kint, kint"
+ qt_sql_substring_Varchar_Integer_Integer_notnull_From_For "select
substring(kvchrs1 FROM kint FOR kint) from fn_test_not_nullable order by
kvchrs1, kint, kint"
+ qt_sql_substring_String_Integer_Integer_From_For "select substring(kstr
FROM kint FOR kint) from fn_test order by kstr, kint, kint"
+ qt_sql_substring_String_Integer_Integer_notnull_From_For "select
substring(kstr FROM kint FOR kint) from fn_test_not_nullable order by kstr,
kint, kint"
qt_sql_substring_index_Varchar_Varchar_Integer "select
substring_index(kvchrs1, ' ', 2) from fn_test order by kvchrs1"
qt_sql_substring_index_Varchar_Varchar_Integer_notnull "select
substring_index(kvchrs1, ' ', 2) from fn_test_not_nullable order by kvchrs1"
qt_sql_substring_index_String_String_Integer "select
substring_index(kstr, ' ', 2) from fn_test order by kstr"
diff --git
a/regression-test/suites/nereids_p0/expression/fold_constant/fold_constant_string_arithmatic.groovy
b/regression-test/suites/nereids_p0/expression/fold_constant/fold_constant_string_arithmatic.groovy
index 68db295f66c..997ac3f9037 100644
---
a/regression-test/suites/nereids_p0/expression/fold_constant/fold_constant_string_arithmatic.groovy
+++
b/regression-test/suites/nereids_p0/expression/fold_constant/fold_constant_string_arithmatic.groovy
@@ -739,6 +739,75 @@ suite("fold_constant_string_arithmatic") {
// testFoldConst("select
PARSE_URL('http://user:[email protected]:8080/path/to/file?query=string#fragment',
'PORT')")
testFoldConst("select
PARSE_URL('http://user:[email protected]:8080/path/to/file?query=string#fragment',
'QUERY')")
+ // position
+ qt_sql "select position('北京' in '上海天津北京杭州')"
+ qt_sql "select position('上海天津北京杭州' in '北京')"
+ qt_sql "select position('bar' in 'foobarbar')"
+ qt_sql "select position(cast('北京' as string) in cast('上海天津北京杭州' as
string))"
+ qt_sql "select position(cast('' as string) in cast('foobar' as string))"
+ qt_sql "select position(cast('bar' as string) in cast('foobarbar' as
string))"
+ qt_sql "select position(cast('World' as string) in cast('Hello' as
string))"
+ qt_sql "select position(cast('World' as string) in cast('Hello World' as
string))"
+ qt_sql "select position(cast('xbar' as string) in cast('foobar' as
string))"
+ qt_sql "select position('' in 'foobar')"
+ qt_sql "select position('World' in 'Hello')"
+ qt_sql "select position('World' in 'Hello World')"
+ qt_sql "select position('xbar' in 'foobar')"
+ qt_sql "select position('北京' in '上海天津北京杭州')"
+ qt_sql "select position('2' in ' 123 ')"
+ qt_sql "select position('bc' in 'abcbcbc')"
+ qt_sql "select position('a' in 'a')"
+ qt_sql "select position('' in '')"
+ qt_sql "select position('abc' in 'abcd')"
+ qt_sql "select position('' in 'hello')"
+ qt_sql "select position('' in '哈哈😊😂🤣🤣😄')"
+ qt_sql "select position('🤣🤣' in '哈哈😊😂🤣🤣😄')"
+ qt_sql "select position('🤣🤣🤣' in '哈哈😊😂🤣🤣😄')"
+ qt_sql "select position('🤣' in '哈哈😊😂🤣🤣😄')"
+ qt_sql "select position('😅' in '哈哈😊😂🤣🤣😄')"
+ qt_sql "select position('안녕' in '哈哈こんにち안녕하세')"
+ qt_sql "select position('하세' in '哈哈こんにち안녕하세')"
+ qt_sql "select position('세' in '哈哈こんにち안녕하세')"
+ qt_sql "select position('' in '你好世界')"
+ qt_sql "select position('北京', '上海天津北京杭州')"
+ qt_sql "select position('上海天津北京杭州', '北京')"
+ qt_sql "select position('bar', 'foobarbar')"
+ qt_sql "select position(cast('北京' as string), cast('上海天津北京杭州' as string))"
+ qt_sql "select position(cast('' as string), cast('foobar' as string))"
+ qt_sql "select position(cast('bar' as string), cast('foobarbar' as
string))"
+ qt_sql "select position(cast('World' as string), cast('Hello' as string))"
+ qt_sql "select position(cast('World' as string), cast('Hello World' as
string))"
+ qt_sql "select position(cast('xbar' as string), cast('foobar' as string))"
+ qt_sql "select position('', 'foobar')"
+ qt_sql "select position('World', 'Hello')"
+ qt_sql "select position('World', 'Hello World')"
+ qt_sql "select position('xbar', 'foobar')"
+ qt_sql "select position('北京', '上海天津北京杭州', 4)"
+ qt_sql "select position('北京', '上海天津北京杭州', 5)"
+ qt_sql "select position('北京', '上海天津北京杭州', -4)"
+ qt_sql "select position('北京', '上海天津北京杭州', -5)"
+ qt_sql "select position('2', ' 123 ', 1)"
+ qt_sql "select position('bc', 'abcbcbc', 4)"
+ qt_sql "select position('a', 'a')"
+ qt_sql "select position('', '')"
+ qt_sql "select position('', '', 2)"
+ qt_sql "select position('abc', 'abcd')"
+ qt_sql "select position('', 'hello', 5)"
+ qt_sql "select position('', 'hello', 6)"
+ qt_sql "select position('', '哈哈😊😂🤣🤣😄')"
+ qt_sql "select position('', '哈哈😊😂🤣🤣😄', 26)"
+ qt_sql "select position('', '哈哈😊😂🤣🤣😄', 27)"
+ qt_sql "select position('🤣🤣', '哈哈😊😂🤣🤣😄', 5)"
+ qt_sql "select position('🤣🤣🤣', '哈哈😊😂🤣🤣😄', 5)"
+ qt_sql "select position('🤣', '哈哈😊😂🤣🤣😄', 6)"
+ qt_sql "select position('😅', '哈哈😊😂🤣🤣😄', 6)"
+ qt_sql "select position('안녕', '哈哈こんにち안녕하세', 6)"
+ qt_sql "select position('하세', '哈哈こんにち안녕하세', 9)"
+ qt_sql "select position('세', '哈哈こんにち안녕하세', 11)"
+ qt_sql "select position('', '', 1)"
+ qt_sql "select position('', '你好世界', 1)"
+ qt_sql "select position('', '你好世界', 5)"
+
// repeat
testFoldConst("select repeat('a', 0)")
testFoldConst("select repeat('a', -1)")
@@ -1198,6 +1267,20 @@ suite("fold_constant_string_arithmatic") {
testFoldConst("select substr('abcdef',-3)")
testFoldConst("select substr('abcdef',3)")
testFoldConst("select substr('',3)")
+ testFoldConst("select substr('a' FROM 0 FOR 1)")
+ testFoldConst("select substr('a' FROM -1 FOR 1)")
+ testFoldConst("select substr('a' FROM 1 FOR 1)")
+ testFoldConst("select substr('a' FROM -2 FOR 1)")
+ testFoldConst("select substr('a' FROM 2 FOR 1)")
+ testFoldConst("select substr('a' FROM -3 FOR 1)")
+ testFoldConst("select substr('a' FROM 3 FOR 1)")
+ testFoldConst("select substr('abcdef' FROM -3 FOR -1)")
+ testFoldConst("select substr('abcdef' FROM 3 FOR -1)")
+ testFoldConst("select substr('' FROM 3 FOR -1)")
+ testFoldConst("select substr('abcdef' FROM 3 FOR 10)")
+ testFoldConst("select substr('abcdef' FROM -3)")
+ testFoldConst("select substr('abcdef' FROM 3)")
+ testFoldConst("select substr('' FROM 3)")
// substring
testFoldConst("select substring('1', 1, 1)")
@@ -1227,6 +1310,33 @@ suite("fold_constant_string_arithmatic") {
testFoldConst("select substring('abcdef',-3)")
testFoldConst("select substring('abcdef',3)")
testFoldConst("select substring('',3)")
+ testFoldConst("select substring('1' FROM 1 FOR 1)")
+ testFoldConst("select substring('abc1' FROM -2)")
+ testFoldConst("select substring('abc1' FROM 2)")
+ testFoldConst("select substring('abc1' FROM 5)")
+ testFoldConst("select substring('abc1def' FROM 2 FOR 2)")
+ testFoldConst("select substring('abcdef' FROM 10 FOR 1)")
+ testFoldConst("select substring('abcdef' FROM -3 FOR -1)")
+ testFoldConst("select substring('abcdef' FROM 3 FOR -1)")
+ testFoldConst("select substring(cast('1' as string) FROM 1 FOR 1)")
+ testFoldConst("select substring(CAST('abc1' AS STRING) FROM -2)")
+ testFoldConst("select substring(CAST('abc1' AS STRING) FROM 2)")
+ testFoldConst("select substring(CAST('abc1' AS STRING) FROM 5)")
+ testFoldConst("select substring(CAST('abc1def' AS STRING) FROM 2 FOR 2)")
+ testFoldConst("select substring(CAST('abcdef' AS STRING) FROM 10 FOR 1)")
+ testFoldConst("select substring(CAST('abcdef' AS STRING) FROM -3 FOR -1)")
+ testFoldConst("select substring(CAST('abcdef' AS STRING) FROM 3 FOR -1)")
+ testFoldConst("select substring(cast('Hello' as string) FROM 1 FOR 10)")
+ testFoldConst("select substring(cast('Hello World' as string) FROM -1 FOR
5)")
+ testFoldConst("select substring(cast('Hello World' as string) FROM 1 FOR
5)")
+ testFoldConst("select substring('Hello' FROM 1 FOR 10)")
+ testFoldConst("select substring('Hello World' FROM -1 FOR 5)")
+ testFoldConst("select substring('Hello World' FROM 1 FOR 5)")
+ testFoldConst("select substring('' FROM 1 FOR 5)")
+ testFoldConst("select substring('Hello World' FROM 1 FOR 50)")
+ testFoldConst("select substring('abcdef' FROM -3)")
+ testFoldConst("select substring('abcdef' FROM 3)")
+ testFoldConst("select substring('' FROM 3)")
// substring_index
testFoldConst("select substring_index('a,b,c', ',', 2)")
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
