This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.0 by this push:
new be4153d6b2e branch-4.0: [chore](function) add alias for split
functions #59200 (#59243)
be4153d6b2e is described below
commit be4153d6b2eab15f7f263a8754af999fa33c789b
Author: github-actions[bot]
<41898282+github-actions[bot]@users.noreply.github.com>
AuthorDate: Tue Dec 23 14:30:37 2025 +0800
branch-4.0: [chore](function) add alias for split functions #59200 (#59243)
Cherry-picked from #59200
Co-authored-by: TengJianPing <[email protected]>
---
.../doris/catalog/BuiltinScalarFunctions.java | 4 +-
.../string_functions/test_split_by_string.out | 146 ++++++++++++++----
.../string_functions/test_split_by_regexp.out | 12 ++
.../string_functions/test_split_by_string.out | 113 --------------
.../string_functions/test_split_by_string.groovy | 142 ++++++++++++++----
.../string_functions/test_split_by_regexp.groovy | 5 +
.../string_functions/test_split_by_string.groovy | 167 ---------------------
7 files changed, 253 insertions(+), 336 deletions(-)
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 1178b68965a..92cabf7f59d 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
@@ -991,8 +991,8 @@ public class BuiltinScalarFunctions implements
FunctionHelper {
scalar(Soundex.class, "soundex"),
scalar(Space.class, "space"),
scalar(SplitByChar.class, "split_by_char"),
- scalar(SplitByRegexp.class, "split_by_regexp"),
- scalar(SplitByString.class, "split_by_string"),
+ scalar(SplitByRegexp.class, "split_by_regexp",
"regexp_split_to_array"),
+ scalar(SplitByString.class, "split_by_string", "split"),
scalar(SplitPart.class, "split_part"),
scalar(Sqrt.class, "sqrt"),
scalar(StAsBinary.class, "st_asbinary"),
diff --git
a/regression-test/data/nereids_p0/sql_functions/string_functions/test_split_by_string.out
b/regression-test/data/nereids_p0/sql_functions/string_functions/test_split_by_string.out
index fd69d943f34..f070c8d695b 100644
---
a/regression-test/data/nereids_p0/sql_functions/string_functions/test_split_by_string.out
+++
b/regression-test/data/nereids_p0/sql_functions/string_functions/test_split_by_string.out
@@ -1,9 +1,12 @@
-- This file is automatically generated. You should know what you did if you
want to edit this
-- !sql --
-['a', 'b', 'c', 'd', 'e']
+["a", "b", "c", "d", "e"]
-- !sql --
-['1', '2', '5', '5', '3']
+["你", "a", "好", "b", "世", "c", "界"]
+
+-- !sql --
+["1", "2", "5", "5", "3"]
-- !sql --
[]
@@ -15,25 +18,25 @@
[]
-- !sql --
-['a', 'b', 'c', 'd']
+["a", "b", "c", "d"]
-- !sql --
-['', '', '', '']
+["", "", "", ""]
-- !sql --
-['a', 'b', 'c', 'abcde']
+["a", "b", "c", "abcde"]
-- !sql --
-['', '', 'a', 'b', 'c', '']
+["", "", "a", "b", "c", ""]
-- !sql --
-['null']
+["null"]
-- !sql --
-['1', '2,3', '4,5', 'abcde']
+["1", "2,3", "4,5", "abcde"]
-- !sql --
-['a', 'b', 'c', 'd', 'e']
+["a", "b", "c", "d", "e"]
-- !sql --
[]
@@ -45,45 +48,132 @@
[]
-- !sql --
-['1', '2,3', '', '', '4,5, abcde']
+["1", "2,3", "", "", "4,5, abcde"]
-- !sql --
-['', '', '']
+["", "", ""]
-- !sql --
-['a', 'b', 'c']
+["a", "b", "c"]
-- !sql --
-['a', 'b', 'c', '']
+["a", "b", "c", ""]
-- !sql --
-['', 'a', 'b', 'c', '']
+["", "a", "b", "c", ""]
-- !sql --
-['null']
+["null"]
-- !sql --
-1 abcde ['a', 'b', 'c', 'd', 'e']
-2 12553 ['1', '2', '5', '5', '3']
+1 abcde ["a", "b", "c", "d", "e"]
+2 12553 ["1", "2", "5", "5", "3"]
3 []
4 , []
5 a []
-6 a1b1c1d 1 ['a', 'b', 'c', 'd']
-7 ,,, , ['', '', '', '']
-8 a,b,c , ['a', 'b', 'c']
-9 a,b,c, , ['a', 'b', 'c', '']
+6 a1b1c1d 1 ["a", "b", "c", "d"]
+7 ,,, , ["", "", "", ""]
+8 a,b,c , ["a", "b", "c"]
+9 a,b,c, , ["a", "b", "c", ""]
10 \N , \N
-11 a,b,c,12345, , ['a', 'b', 'c', '12345', '']
+11 a,b,c,12345, , ["a", "b", "c", "12345", ""]
+12 你a好b世c界 ["你", "a", "好", "b", "世", "c", "界"]
-- !sql --
-1 1,,2,3,,4,5,,abcde ,, ['1', '2,3', '4,5', 'abcde']
-2 abcde ['a', 'b', 'c', 'd', 'e']
+1 1,,2,3,,4,5,,abcde ,, ["1", "2,3", "4,5", "abcde"]
+2 abcde ["a", "b", "c", "d", "e"]
3 []
4 , []
5 a []
-6 1,,2,3,,,,,,4,5,,abcde ,, ['1', '2,3', '', '', '4,5', 'abcde']
-7 ,,, , ['', '', '', '']
-8 a,b,c , ['a', 'b', 'c']
-9 a,b,c, , ['a', 'b', 'c', '']
+6 1,,2,3,,,,,,4,5,,abcde ,, ["1", "2,3", "", "", "4,5", "abcde"]
+7 ,,, , ["", "", "", ""]
+8 a,b,c , ["a", "b", "c"]
+9 a,b,c, , ["a", "b", "c", ""]
10 \N , \N
+-- !sql_1 --
+1 ["a", "b", "c"] ["a", "b", "c"] ["a", "b", "c"] ["a", "b", "c"]
+2 ["a", "b", "c"] ["a", "b", "c"] ["a", "b", "c"] ["a", "b", "c"]
+
+-- !sql_2 --
+3 ["a", "b", "c"] ["a", "b", "c"] ["a", "b", "c"] ["a", "b", "c"]
+4 ["a", "b", "c"] ["a", "b", "c"] ["a", "b", "c"] ["a", "b", "c"]
+
+-- !sql_3 --
+1 [] [] [] []
+2 [] [] [] []
+3 ["a", ",", "b", ",", "c"] ["a", ",", "b", ",", "c"] ["a",
",", "b", ",", "c"] ["a", ",", "b", ",", "c"]
+4 ["a", ",", "b", ",", "c"] ["a", ",", "b", ",", "c"] ["a",
",", "b", ",", "c"] ["a", ",", "b", ",", "c"]
+
+-- !sql_4 --
+1 [] [] [] []
+2 [] [] [] []
+3 [] [] [] []
+4 [] [] [] []
+
+-- !sql_alias --
+["a", "b", "c", "d", "e"]
+
+-- !sql_alias --
+["你", "a", "好", "b", "世", "c", "界"]
+
+-- !sql_alias --
+["1", "2", "5", "5", "3"]
+
+-- !sql_alias --
+[]
+
+-- !sql_alias --
+[]
+
+-- !sql_alias --
+[]
+
+-- !sql_alias --
+["a", "b", "c", "d"]
+
+-- !sql_alias --
+["", "", "", ""]
+
+-- !sql_alias --
+["a", "b", "c", "abcde"]
+
+-- !sql_alias --
+["", "", "a", "b", "c", ""]
+
+-- !sql_alias --
+["null"]
+
+-- !sql_alias --
+["1", "2,3", "4,5", "abcde"]
+
+-- !sql_alias --
+["a", "b", "c", "d", "e"]
+
+-- !sql_alias --
+[]
+
+-- !sql_alias --
+[]
+
+-- !sql_alias --
+[]
+
+-- !sql_alias --
+["1", "2,3", "", "", "4,5, abcde"]
+
+-- !sql_alias --
+["", "", ""]
+
+-- !sql_alias --
+["a", "b", "c"]
+
+-- !sql_alias --
+["a", "b", "c", ""]
+
+-- !sql_alias --
+["", "a", "b", "c", ""]
+
+-- !sql_alias --
+["null"]
+
diff --git
a/regression-test/data/query_p0/sql_functions/string_functions/test_split_by_regexp.out
b/regression-test/data/query_p0/sql_functions/string_functions/test_split_by_regexp.out
index b6c790e82a4..fa6e31abd94 100644
---
a/regression-test/data/query_p0/sql_functions/string_functions/test_split_by_regexp.out
+++
b/regression-test/data/query_p0/sql_functions/string_functions/test_split_by_regexp.out
@@ -94,3 +94,15 @@ a ["a"]
, [","]
, [","]
+-- !select_alias1 --
+["a", "b", "c", "d", "e"]
+
+-- !select_alias2 --
+["a", "bc", "de", "f"]
+
+-- !select_alias3 --
+\N
+
+-- !select_alias4 --
+\N
+
diff --git
a/regression-test/data/query_p0/sql_functions/string_functions/test_split_by_string.out
b/regression-test/data/query_p0/sql_functions/string_functions/test_split_by_string.out
deleted file mode 100644
index 819feea4937..00000000000
---
a/regression-test/data/query_p0/sql_functions/string_functions/test_split_by_string.out
+++ /dev/null
@@ -1,113 +0,0 @@
--- This file is automatically generated. You should know what you did if you
want to edit this
--- !sql --
-["a", "b", "c", "d", "e"]
-
--- !sql --
-["你", "a", "好", "b", "世", "c", "界"]
-
--- !sql --
-["1", "2", "5", "5", "3"]
-
--- !sql --
-[]
-
--- !sql --
-[]
-
--- !sql --
-[]
-
--- !sql --
-["a", "b", "c", "d"]
-
--- !sql --
-["", "", "", ""]
-
--- !sql --
-["a", "b", "c", "abcde"]
-
--- !sql --
-["", "", "a", "b", "c", ""]
-
--- !sql --
-["null"]
-
--- !sql --
-["1", "2,3", "4,5", "abcde"]
-
--- !sql --
-["a", "b", "c", "d", "e"]
-
--- !sql --
-[]
-
--- !sql --
-[]
-
--- !sql --
-[]
-
--- !sql --
-["1", "2,3", "", "", "4,5, abcde"]
-
--- !sql --
-["", "", ""]
-
--- !sql --
-["a", "b", "c"]
-
--- !sql --
-["a", "b", "c", ""]
-
--- !sql --
-["", "a", "b", "c", ""]
-
--- !sql --
-["null"]
-
--- !sql --
-1 abcde ["a", "b", "c", "d", "e"]
-2 12553 ["1", "2", "5", "5", "3"]
-3 []
-4 , []
-5 a []
-6 a1b1c1d 1 ["a", "b", "c", "d"]
-7 ,,, , ["", "", "", ""]
-8 a,b,c , ["a", "b", "c"]
-9 a,b,c, , ["a", "b", "c", ""]
-10 \N , \N
-11 a,b,c,12345, , ["a", "b", "c", "12345", ""]
-12 你a好b世c界 ["你", "a", "好", "b", "世", "c", "界"]
-
--- !sql --
-1 1,,2,3,,4,5,,abcde ,, ["1", "2,3", "4,5", "abcde"]
-2 abcde ["a", "b", "c", "d", "e"]
-3 []
-4 , []
-5 a []
-6 1,,2,3,,,,,,4,5,,abcde ,, ["1", "2,3", "", "", "4,5", "abcde"]
-7 ,,, , ["", "", "", ""]
-8 a,b,c , ["a", "b", "c"]
-9 a,b,c, , ["a", "b", "c", ""]
-10 \N , \N
-
--- !sql_1 --
-1 ["a", "b", "c"] ["a", "b", "c"] ["a", "b", "c"] ["a", "b", "c"]
-2 ["a", "b", "c"] ["a", "b", "c"] ["a", "b", "c"] ["a", "b", "c"]
-
--- !sql_2 --
-3 ["a", "b", "c"] ["a", "b", "c"] ["a", "b", "c"] ["a", "b", "c"]
-4 ["a", "b", "c"] ["a", "b", "c"] ["a", "b", "c"] ["a", "b", "c"]
-
--- !sql_3 --
-1 [] [] [] []
-2 [] [] [] []
-3 ["a", ",", "b", ",", "c"] ["a", ",", "b", ",", "c"] ["a",
",", "b", ",", "c"] ["a", ",", "b", ",", "c"]
-4 ["a", ",", "b", ",", "c"] ["a", ",", "b", ",", "c"] ["a",
",", "b", ",", "c"] ["a", ",", "b", ",", "c"]
-
--- !sql_4 --
-1 [] [] [] []
-2 [] [] [] []
-3 [] [] [] []
-4 [] [] [] []
-
diff --git
a/regression-test/suites/nereids_p0/sql_functions/string_functions/test_split_by_string.groovy
b/regression-test/suites/nereids_p0/sql_functions/string_functions/test_split_by_string.groovy
index 98dfaaaa53e..c5bffb57c42 100644
---
a/regression-test/suites/nereids_p0/sql_functions/string_functions/test_split_by_string.groovy
+++
b/regression-test/suites/nereids_p0/sql_functions/string_functions/test_split_by_string.groovy
@@ -19,31 +19,32 @@ suite("test_split_by_string") {
sql "SET enable_nereids_planner=true"
sql "SET enable_fallback_to_original_planner=false"
// split by char
- // qt_sql "select split_by_string('abcde','');"
- // qt_sql "select split_by_string('12553','');"
- // qt_sql "select split_by_string('','');"
- // qt_sql "select split_by_string('',',');"
- // qt_sql "select split_by_string('','a');"
-
- // qt_sql "select split_by_string('a1b1c1d','1');"
- // qt_sql "select split_by_string(',,,',',');"
- // qt_sql "select split_by_string('a,b,c,abcde',',');"
- // qt_sql "select split_by_string(',,a,b,c,',',');"
- // qt_sql "select split_by_string('null',',');"
+ qt_sql "select split_by_string('abcde','');"
+ qt_sql "select split_by_string('你a好b世c界','');"
+ qt_sql "select split_by_string('12553','');"
+ qt_sql "select split_by_string('','');"
+ qt_sql "select split_by_string('',',');"
+ qt_sql "select split_by_string('','a');"
+
+ qt_sql "select split_by_string('a1b1c1d','1');"
+ qt_sql "select split_by_string(',,,',',');"
+ qt_sql "select split_by_string('a,b,c,abcde',',');"
+ qt_sql "select split_by_string(',,a,b,c,',',');"
+ qt_sql "select split_by_string('null',',');"
- // // split by string
- // qt_sql "select split_by_string('1,,2,3,,4,5,,abcde', ',,');"
- // qt_sql "select split_by_string('abcde','');"
- // qt_sql "select split_by_string('','');"
- // qt_sql "select split_by_string('',',');"
- // qt_sql "select split_by_string('','a');"
-
- // qt_sql "select split_by_string('1,,2,3,,,,,,4,5, abcde', ',,');"
- // qt_sql "select split_by_string(',,,,',',,');"
- // qt_sql "select split_by_string('a,,b,,c',',,');"
- // qt_sql "select split_by_string('a,,b,,c,,',',,');"
- // qt_sql "select split_by_string(',,a,,b,,c,,',',,');"
- // qt_sql "select split_by_string('null',',');"
+ // split by string
+ qt_sql "select split_by_string('1,,2,3,,4,5,,abcde', ',,');"
+ qt_sql "select split_by_string('abcde','');"
+ qt_sql "select split_by_string('','');"
+ qt_sql "select split_by_string('',',');"
+ qt_sql "select split_by_string('','a');"
+
+ qt_sql "select split_by_string('1,,2,3,,,,,,4,5, abcde', ',,');"
+ qt_sql "select split_by_string(',,,,',',,');"
+ qt_sql "select split_by_string('a,,b,,c',',,');"
+ qt_sql "select split_by_string('a,,b,,c,,',',,');"
+ qt_sql "select split_by_string(',,a,,b,,c,,',',,');"
+ qt_sql "select split_by_string('null',',');"
def tableName1 = "test_split_by_char"
@@ -72,8 +73,9 @@ suite("test_split_by_string") {
sql """ INSERT INTO ${tableName1} VALUES(9, 'a,b,c,', ',') """
sql """ INSERT INTO ${tableName1} VALUES(10, null, ',') """
sql """ INSERT INTO ${tableName1} VALUES(11, 'a,b,c,12345,', ',') """
+ sql """ INSERT INTO ${tableName1} VALUES(12, '你a好b世c界', '') """
- // qt_sql "SELECT *, split_by_string(v1, v2) FROM ${tableName1} ORDER BY
k1"
+ qt_sql "SELECT *, split_by_string(v1, v2) FROM ${tableName1} ORDER BY k1"
def tableName2 = "test_split_by_string"
@@ -103,5 +105,93 @@ suite("test_split_by_string") {
sql """ INSERT INTO ${tableName2} VALUES(10, null, ',') """
- // qt_sql "SELECT *, split_by_string(v1, v2) FROM ${tableName2} ORDER BY
k1"
+ qt_sql "SELECT *, split_by_string(v1, v2) FROM ${tableName2} ORDER BY k1"
+
+ // Case where both of operator are column string is covered by above test.
+ sql """DROP TABLE IF EXISTS test_split_by_string_2"""
+ sql """
+ CREATE TABLE IF NOT EXISTS test_split_by_string_2 (
+ `rid` INT NULL,
+ `str` TEXT NULL,
+ `vc` VARCHAR(5) NULL,
+ `chr` CHAR(5) NULL,
+ `txt` TEXT NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`rid`)
+ DISTRIBUTED BY HASH(`rid`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "storage_format" = "V2"
+ )
+ """
+ sql """ INSERT INTO test_split_by_string_2
+ VALUES (1, "", "", "", ""),
+ (2, "", "", "", ""),
+ (3, "a,b,c", "a,b,c", "a,b,c", "a,b,c"),
+ (4, "a,b,c", "a,b,c", "a,b,c", "a,b,c")
+ """
+ // Left operator is const, right operator is column string
+ qt_sql_1 """
+ SELECT rid,
+ split_by_string("abc", str),
+ split_by_string("abc", vc),
+ split_by_string("abc", chr),
+ split_by_string("abc", txt)
+ FROM test_split_by_string_2 WHERE rid=1 OR rid=2 ORDER BY rid;
+ """
+ // Left operator is column string, right operator is const
+ qt_sql_2 """
+ SELECT rid,
+ split_by_string(str, ","),
+ split_by_string(vc, ","),
+ split_by_string(chr, ","),
+ split_by_string(txt, ",")
+ FROM test_split_by_string_2 WHERE rid=3 OR rid=4 ORDER BY rid;
+ """
+
+ // Empty string
+ qt_sql_3 """
+ SELECT rid,
+ split_by_string(str, ""),
+ split_by_string(vc, ""),
+ split_by_string(chr, ""),
+ split_by_string(txt, "")
+ FROM test_split_by_string_2 ORDER BY rid;
+ """
+ qt_sql_4 """
+ SELECT rid,
+ split_by_string("", str),
+ split_by_string("", vc),
+ split_by_string("", chr),
+ split_by_string("", txt)
+ FROM test_split_by_string_2 ORDER BY rid;
+ """
+
+ qt_sql_alias "select split('abcde','');"
+ qt_sql_alias "select split('你a好b世c界','');"
+ qt_sql_alias "select split('12553','');"
+ qt_sql_alias "select split('','');"
+ qt_sql_alias "select split('',',');"
+ qt_sql_alias "select split('','a');"
+
+ qt_sql_alias "select split('a1b1c1d','1');"
+ qt_sql_alias "select split(',,,',',');"
+ qt_sql_alias "select split('a,b,c,abcde',',');"
+ qt_sql_alias "select split(',,a,b,c,',',');"
+ qt_sql_alias "select split('null',',');"
+
+ // split by string
+ qt_sql_alias "select split('1,,2,3,,4,5,,abcde', ',,');"
+ qt_sql_alias "select split('abcde','');"
+ qt_sql_alias "select split('','');"
+ qt_sql_alias "select split('',',');"
+ qt_sql_alias "select split('','a');"
+
+ qt_sql_alias "select split('1,,2,3,,,,,,4,5, abcde', ',,');"
+ qt_sql_alias "select split(',,,,',',,');"
+ qt_sql_alias "select split('a,,b,,c',',,');"
+ qt_sql_alias "select split('a,,b,,c,,',',,');"
+ qt_sql_alias "select split(',,a,,b,,c,,',',,');"
+ qt_sql_alias "select split('null',',');"
+
}
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/sql_functions/string_functions/test_split_by_regexp.groovy
b/regression-test/suites/query_p0/sql_functions/string_functions/test_split_by_regexp.groovy
index 394a8e721aa..678f7221642 100644
---
a/regression-test/suites/query_p0/sql_functions/string_functions/test_split_by_regexp.groovy
+++
b/regression-test/suites/query_p0/sql_functions/string_functions/test_split_by_regexp.groovy
@@ -72,5 +72,10 @@ suite("test_split_by_regexp") {
qt_select13 "select split_by_regexp('aa,bbb,cccc', ',', 10000000000000);"
qt_select14 "select v1,split_by_regexp(v1, '') from test_split_by_regexp
order by k1;"
qt_select15 "select v2,split_by_regexp(v2, '') from test_split_by_regexp
order by k1;"
+
+ qt_select_alias1 "select regexp_split_to_array('abcde','');"
+ qt_select_alias2 "select regexp_split_to_array('a12bc23de345f','\\\\d+');"
+ qt_select_alias3 "select regexp_split_to_array('a12bc23de345f',NULL);"
+ qt_select_alias4 "select regexp_split_to_array(NULL, 'a12bc23de345f');"
}
diff --git
a/regression-test/suites/query_p0/sql_functions/string_functions/test_split_by_string.groovy
b/regression-test/suites/query_p0/sql_functions/string_functions/test_split_by_string.groovy
deleted file mode 100644
index 6b7f32b5aae..00000000000
---
a/regression-test/suites/query_p0/sql_functions/string_functions/test_split_by_string.groovy
+++ /dev/null
@@ -1,167 +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.
-
-suite("test_split_by_string") {
- // split by char
- qt_sql "select split_by_string('abcde','');"
- qt_sql "select split_by_string('你a好b世c界','');"
- qt_sql "select split_by_string('12553','');"
- qt_sql "select split_by_string('','');"
- qt_sql "select split_by_string('',',');"
- qt_sql "select split_by_string('','a');"
-
- qt_sql "select split_by_string('a1b1c1d','1');"
- qt_sql "select split_by_string(',,,',',');"
- qt_sql "select split_by_string('a,b,c,abcde',',');"
- qt_sql "select split_by_string(',,a,b,c,',',');"
- qt_sql "select split_by_string('null',',');"
-
- // split by string
- qt_sql "select split_by_string('1,,2,3,,4,5,,abcde', ',,');"
- qt_sql "select split_by_string('abcde','');"
- qt_sql "select split_by_string('','');"
- qt_sql "select split_by_string('',',');"
- qt_sql "select split_by_string('','a');"
-
- qt_sql "select split_by_string('1,,2,3,,,,,,4,5, abcde', ',,');"
- qt_sql "select split_by_string(',,,,',',,');"
- qt_sql "select split_by_string('a,,b,,c',',,');"
- qt_sql "select split_by_string('a,,b,,c,,',',,');"
- qt_sql "select split_by_string(',,a,,b,,c,,',',,');"
- qt_sql "select split_by_string('null',',');"
-
- def tableName1 = "test_split_by_char"
-
- sql """DROP TABLE IF EXISTS ${tableName1}"""
- sql """
- CREATE TABLE IF NOT EXISTS ${tableName1} (
- `k1` int(11) NULL COMMENT "",
- `v1` varchar(20) NULL COMMENT "",
- `v2` varchar(1) NOT NULL COMMENT ""
- ) ENGINE=OLAP
- DUPLICATE KEY(`k1`)
- DISTRIBUTED BY HASH(`k1`) BUCKETS 1
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1",
- "storage_format" = "V2"
- )
- """
- sql """ INSERT INTO ${tableName1} VALUES(1, 'abcde', '') """
- sql """ INSERT INTO ${tableName1} VALUES(2, '12553', '') """
- sql """ INSERT INTO ${tableName1} VALUES(3, '', '') """
- sql """ INSERT INTO ${tableName1} VALUES(4, '', ',') """
- sql """ INSERT INTO ${tableName1} VALUES(5, '', 'a') """
- sql """ INSERT INTO ${tableName1} VALUES(6, 'a1b1c1d', '1') """
- sql """ INSERT INTO ${tableName1} VALUES(7, ',,,', ',') """
- sql """ INSERT INTO ${tableName1} VALUES(8, 'a,b,c', ',') """
- sql """ INSERT INTO ${tableName1} VALUES(9, 'a,b,c,', ',') """
- sql """ INSERT INTO ${tableName1} VALUES(10, null, ',') """
- sql """ INSERT INTO ${tableName1} VALUES(11, 'a,b,c,12345,', ',') """
- sql """ INSERT INTO ${tableName1} VALUES(12, '你a好b世c界', '') """
-
- qt_sql "SELECT *, split_by_string(v1, v2) FROM ${tableName1} ORDER BY k1"
-
- def tableName2 = "test_split_by_string"
-
- sql """DROP TABLE IF EXISTS ${tableName2}"""
- sql """
- CREATE TABLE IF NOT EXISTS ${tableName2} (
- `k1` int(11) NULL COMMENT "",
- `v1` varchar(50) NULL COMMENT "",
- `v2` varchar(10) NOT NULL COMMENT ""
- ) ENGINE=OLAP
- DUPLICATE KEY(`k1`)
- DISTRIBUTED BY HASH(`k1`) BUCKETS 1
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1",
- "storage_format" = "V2"
- )
- """
- sql """ INSERT INTO ${tableName2} VALUES(1, '1,,2,3,,4,5,,abcde', ',,') """
- sql """ INSERT INTO ${tableName2} VALUES(2, 'abcde','') """
- sql """ INSERT INTO ${tableName2} VALUES(3, '', '') """
- sql """ INSERT INTO ${tableName2} VALUES(4, '', ',') """
- sql """ INSERT INTO ${tableName2} VALUES(5, '', 'a') """
- sql """ INSERT INTO ${tableName2} VALUES(6, '1,,2,3,,,,,,4,5,,abcde',
',,') """
- sql """ INSERT INTO ${tableName2} VALUES(7, ',,,', ',') """
- sql """ INSERT INTO ${tableName2} VALUES(8, 'a,b,c', ',') """
- sql """ INSERT INTO ${tableName2} VALUES(9, 'a,b,c,', ',') """
- sql """ INSERT INTO ${tableName2} VALUES(10, null, ',') """
-
-
- qt_sql "SELECT *, split_by_string(v1, v2) FROM ${tableName2} ORDER BY k1"
-
- // Case where both of operator are column string is covered by above test.
- sql """DROP TABLE IF EXISTS test_split_by_string_2"""
- sql """
- CREATE TABLE IF NOT EXISTS test_split_by_string_2 (
- `rid` INT NULL,
- `str` TEXT NULL,
- `vc` VARCHAR(5) NULL,
- `chr` CHAR(5) NULL,
- `txt` TEXT NULL
- ) ENGINE=OLAP
- DUPLICATE KEY(`rid`)
- DISTRIBUTED BY HASH(`rid`) BUCKETS 1
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1",
- "storage_format" = "V2"
- )
- """
- sql """ INSERT INTO test_split_by_string_2
- VALUES (1, "", "", "", ""),
- (2, "", "", "", ""),
- (3, "a,b,c", "a,b,c", "a,b,c", "a,b,c"),
- (4, "a,b,c", "a,b,c", "a,b,c", "a,b,c")
- """
- // Left operator is const, right operator is column string
- qt_sql_1 """
- SELECT rid,
- split_by_string("abc", str),
- split_by_string("abc", vc),
- split_by_string("abc", chr),
- split_by_string("abc", txt)
- FROM test_split_by_string_2 WHERE rid=1 OR rid=2 ORDER BY rid;
- """
- // Left operator is column string, right operator is const
- qt_sql_2 """
- SELECT rid,
- split_by_string(str, ","),
- split_by_string(vc, ","),
- split_by_string(chr, ","),
- split_by_string(txt, ",")
- FROM test_split_by_string_2 WHERE rid=3 OR rid=4 ORDER BY rid;
- """
-
- // Empty string
- qt_sql_3 """
- SELECT rid,
- split_by_string(str, ""),
- split_by_string(vc, ""),
- split_by_string(chr, ""),
- split_by_string(txt, "")
- FROM test_split_by_string_2 ORDER BY rid;
- """
- qt_sql_4 """
- SELECT rid,
- split_by_string("", str),
- split_by_string("", vc),
- split_by_string("", chr),
- split_by_string("", txt)
- FROM test_split_by_string_2 ORDER BY rid;
- """
-}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
