Re: [PR] [fix](Nereids) fix fe folding constant of string functions and add more cases [doris]

Tue, 24 Dec 2024 22:27:13 -0800 


aytrack commented on code in PR #45233:
URL: https://github.com/apache/doris/pull/45233#discussion_r1897138963


##########
regression-test/suites/nereids_p0/expression/fold_constant/fold_constant_string_arithmatic.groovy:
##########
@@ -16,763 +16,646 @@
 // under the License.
 
 suite("fold_constant_string_arithmatic") {
-    def db = "fold_constant_string_arithmatic"
-    sql "create database if not exists ${db}"
-
     sql "set enable_nereids_planner=true"
     sql "set enable_fallback_to_original_planner=false"
     sql "set enable_fold_constant_by_be=false"
-
-    testFoldConst("SELECT  Concat('Hello', ' ', 'World')")
-    testFoldConst("SELECT  Substring('Hello World', 1, 5)")
-    testFoldConst("SELECT  Substring('1', 1, 1)")
-    testFoldConst("select  100, 'abc', substring('abc', 1, 2), 
substring(substring('abcdefg', 4, 3), 1, 2), null")
-    testFoldConst("SELECT  Length('Hello World')")
-    testFoldConst("SELECT  Lower('Hello World')")
-    testFoldConst("SELECT  Upper('Hello World')")
-    testFoldConst("SELECT  Trim('  Hello World  ')")
-    testFoldConst("SELECT  Trim('11111', 11)")
-    testFoldConst("SELECT  Ltrim('  Hello World  ')")
-    testFoldConst("SELECT  LTrim(' 11111', 11)")
-    testFoldConst("SELECT  LTrim('11111 ', 11)")
-    testFoldConst("SELECT  Rtrim('  Hello World  ')")
-    testFoldConst("SELECT  RTrim('11111 ', 11)")
-    testFoldConst("SELECT  RTrim(' 11111', 11)")
-    testFoldConst("SELECT  Replace('Hello World', 'World', 'Everyone')")
-    testFoldConst("SELECT  Left('Hello World', 5)")
-    testFoldConst("SELECT  Right('Hello World', 5)")
-    testFoldConst("SELECT  Locate('World', 'Hello World')")
-    testFoldConst("SELECT  Instr('Hello World', 'World')")
-    testFoldConst("SELECT  Ascii('A')")
-    testFoldConst("SELECT  Bin(5)")
-    testFoldConst("SELECT  Hex(255)")
-    testFoldConst("SELECT  Unhex('FF')")
-    testFoldConst("SELECT  Concat_Ws('-', '2024', '09', '02')")
-    testFoldConst("SELECT  Char(65)")
-    testFoldConst("SELECT  Character_Length('Hello World')")
-    testFoldConst("SELECT  Length('δ½ ')")
-    testFoldConst("SELECT  Initcap('hello world')")
-    testFoldConst("SELECT  Md5('Hello World')")
-    testFoldConst("SELECT  Md5Sum('Hello World')")
-//    testFoldConst("SELECT  JsonExtract('{"key": "value"}', '$.key')")
-//    testFoldConst("SELECT  JsonbExtractString('{"key": "value"}', '$.key')")
-//    testFoldConst("SELECT  JsonContains('{"key": "value"}', '"key"')")
-//    testFoldConst("SELECT  JsonLength('{"key1": "value1", "key2": 
"value2"}')")
-//    testFoldConst("SELECT  JsonObject('key', 'value')")
-//    testFoldConst("SELECT  JsonArray('value1', 'value2')")
-//    testFoldConst("SELECT  JsonKeys('{"key1": "value1", "key2": "value2"}')")
-//    testFoldConst("SELECT  JsonInsert('{"key1": "value1"}', '$.key2', 
'value2')")
-//    testFoldConst("SELECT  JsonReplace('{"key1": "value1"}', '$.key1', 
'new_value')")
-//    testFoldConst("SELECT  JsonSet('{"key1": "value1"}', '$.key2', 
'value2')")
-//    testFoldConst("SELECT  Json_Quote('Hello World')")
-//    testFoldConst("SELECT  Json_UnQuote('"Hello World"')")
-    testFoldConst("SELECT  Field('b', 'a', 'b', 'c')")
-    testFoldConst("SELECT  Find_In_Set('b', 'a,b,c')")
-    testFoldConst("SELECT  Repeat('Hello', 3)")
-    testFoldConst("SELECT  Reverse('Hello')")
-    testFoldConst("SELECT  length(Space(10))")
-//    testFoldConst("SELECT  Split_By_Char('a,b,c',',')")  has bug in be 
execution
-    testFoldConst("SELECT  Split_By_String('a::b::c', '::')")
-    testFoldConst("SELECT  Split_Part('a,b,c', ',', 2)")
-    testFoldConst("SELECT  Substring_Index('a,b,c', ',', 2)")
-    testFoldConst("SELECT  Strcmp('abc', 'abd')")
-    testFoldConst("SELECT  StrLeft('Hello World', 5)")
-    testFoldConst("SELECT  StrRight('Hello World', 5)")
-    testFoldConst("SELECT  Overlay('abcdef', '123', 3, 2)")
-    testFoldConst("SELECT  Parse_Url('http://www.example.com/path?query=abc', 
'HOST')")
-    testFoldConst("SELECT  Url_Decode('+Hello+World+')")
-    testFoldConst("SELECT  Url_Encode(' Hello World ')")
-
-    // Substring with negative start index
-    // Expected behavior: Depending on the SQL engine, might return an empty 
string or error.
-    testFoldConst("SELECT Substring('Hello World', -1, 5)")
-
-    // Substring with length exceeding the string length
-    // Expected behavior: Return 'Hello' as the length exceeds the string 
length.
-    testFoldConst("SELECT Substring('Hello', 1, 10)")
-
-    // Left with length greater than string length
-    // Expected behavior: Return 'Hello'.
-    testFoldConst("SELECT Left('Hello', 10)")
-
-    // Right with length greater than string length
-    // Expected behavior: Return 'Hello'.
-    testFoldConst("SELECT Right('Hello', 10)")
-
-    // SplitPart with part number greater than the number of parts
-    // Expected behavior: Return an empty string or error.
-    testFoldConst("SELECT Split_Part('a,b,c', ',', 5)")
-
-    // SplitPart with negative part number
-    // Expected behavior: Return an empty string or error.
-    testFoldConst("SELECT Split_Part('a,b,c', ',', -1)")
-
-    // Locate with the substring not present
-    // Expected behavior: Return 0 as 'World' is not found.
-    testFoldConst("SELECT Locate('World', 'Hello')")
-
-    // Instr with the substring not present
-    // Expected behavior: Return 0 as 'World' is not found.
-    testFoldConst("SELECT Instr('Hello', 'World')")
-
-    // Replace with an empty search string
-    // Expected behavior: Some SQL engines may treat this as a no-op, others 
might throw an error.
-    testFoldConst("SELECT Replace('Hello World', '', 'Everyone')")
-
-    // Replace with an empty replacement string
-    // Expected behavior: Return 'Hello '.
-    testFoldConst("SELECT Replace('Hello World', 'World', '')")
-
-    // Concat with NULL values
-    // Expected behavior: Depending on the SQL engine, may return 'HelloWorld' 
or NULL.
-    testFoldConst("SELECT Concat('Hello', NULL, 'World')")
-
-    // Ltrim with a string that has no leading spaces
-    // Expected behavior: Return 'Hello'.
-    testFoldConst("SELECT Ltrim('Hello')")
-
-    // Rtrim with a string that has no trailing spaces
-    // Expected behavior: Return 'Hello'.
-    testFoldConst("SELECT Rtrim('Hello')")
-
-    // JsonExtract with an invalid JSON path
-    // Expected behavior: Return NULL or an empty string.
-//    testFoldConst("SELECT Json_Extract('{"key": "value"}', '$.invalid')")
-
-    // JsonLength with a non-JSON string
-    // Expected behavior: Return NULL or error.
-    testFoldConst("SELECT Json_Length('Hello World')")
-
-    // Field with a value not present in the list
-    // Expected behavior: Return 0 as 'd' is not found.
-    testFoldConst("SELECT Field('d', 'a', 'b', 'c')")
-
-    // FindInSet with a value not present in the set
-    // Expected behavior: Return 0 as 'd' is not found.
-    testFoldConst("SELECT Find_In_Set('d', 'a,b,c')")
-
-    // Repeat with a negative repeat count
-    // Expected behavior: Return an empty string or error.
-    testFoldConst("SELECT Repeat('Hello', -3)")
-
-    // Space with a negative number of spaces
-    // Expected behavior: Return an empty string or error.
-    testFoldConst("SELECT Space(-5)")
-
-    // SplitByChar with a delimiter not present in the string
-    // Expected behavior: Return the original string in a single element array.
-//    testFoldConst("SELECT Split_By_Char('abc', ',')")
-
-    // SplitByString with a delimiter not present in the string
-    // Expected behavior: Return the original string in a single element array.
-    testFoldConst("SELECT Split_By_String('::', 'abc')")
-
-    // Strcmp with two identical strings
-    // Expected behavior: Return 0 as the strings are equal.
-    testFoldConst("SELECT Strcmp('abc', 'abc')")
-
-    // Strcmp with a null string
-    // Expected behavior: Return NULL or -1 depending on the SQL engine.
-    testFoldConst("SELECT Strcmp('abc', NULL)")
-
-    // Hex with a negative number
-    // Expected behavior: Return the hexadecimal representation of the two's 
complement, or an error depending on the SQL engine.
-    testFoldConst("SELECT Hex(-255)")
-
-    // Unhex with an invalid hexadecimal string
-    // Expected behavior: Return NULL or error as 'GHIJ' is not a valid hex 
string.
-    testFoldConst("SELECT Unhex('GHIJ')")
-
-    // JsonReplace with a path that does not exist
-    // Expected behavior: Depending on the engine, might return the original 
JSON or an error.
-//    testFoldConst("SELECT Json_Replace('{"key": "value"}', '$.nonexistent', 
'new_value')")
-
-    // UrlDecode with an invalid percent-encoded string
-    // Expected behavior: Return NULL or error due to invalid encoding.
-    // testFoldConst("SELECT Url_Decode('%ZZHello%20World')")
-
-    testFoldConst("select elt(0, \"hello\", \"doris\")")
-    testFoldConst("select elt(1, \"hello\", \"doris\")")
-    testFoldConst("select elt(2, \"hello\", \"doris\")")
-    testFoldConst("select elt(3, \"hello\", \"doris\")")
-    testFoldConst("select c1, c2, elt(c1, c2) from (select number as c1, 
'varchar' as c2 from numbers('number'='5') where number > 0) a")
-
+    
+    // append_trailing_char_if_absent
+    testFoldConst("select append_trailing_char_if_absent('', '!')")
+    testFoldConst("select append_trailing_char_if_absent(12345, '!')")
+    testFoldConst("select append_trailing_char_if_absent(12345, '!')")
     testFoldConst("select append_trailing_char_if_absent('a','c')")
     testFoldConst("select append_trailing_char_if_absent('ac','c')")
+    testFoldConst("select append_trailing_char_if_absent(cast('a' as string), 
cast('c' as string))")
+    testFoldConst("select append_trailing_char_if_absent(cast('ac' as string), 
cast('c' as string))")
+    testFoldConst("select append_trailing_char_if_absent('h', '!')")
+    testFoldConst("select append_trailing_char_if_absent('hello!', '!')")
+    testFoldConst("select append_trailing_char_if_absent('hello', '😊')")
+    testFoldConst("select append_trailing_char_if_absent('hello', ' ')")
+    testFoldConst("select append_trailing_char_if_absent('hello', ' ')")
+    testFoldConst("select append_trailing_char_if_absent('hello', '!')")
+    testFoldConst("select append_trailing_char_if_absent('hello', '')")
+    testFoldConst("select append_trailing_char_if_absent('hello', '?')")
+    testFoldConst("select append_trailing_char_if_absent('hello?', '!')")
+    testFoldConst("select append_trailing_char_if_absent('hello', '1')")
+    testFoldConst("select append_trailing_char_if_absent('hello', 1)")
+    testFoldConst("select append_trailing_char_if_absent('hello', 'ab')")
+    testFoldConst("select append_trailing_char_if_absent('hello', NULL)")
     testFoldConst("select append_trailing_char_if_absent('it','a')")
-
+    testFoldConst("select append_trailing_char_if_absent(NULL, '!')")
+    testFoldConst("select append_trailing_char_if_absent('This is a very long 
string', '.')")
+    testFoldConst("select append_trailing_char_if_absent('ΠŸΡ€ΠΈΠ²Π΅Ρ‚', '!')")
+    testFoldConst("select append_trailing_char_if_absent('こんにけは', '!')")
+    
+    // ascii
+    testFoldConst("select ascii('!')")
     testFoldConst("select ascii('1')")
     testFoldConst("select ascii('a')")
     testFoldConst("select ascii('A')")
-    testFoldConst("select ascii('!')")
-
+    testFoldConst("select ascii('こ')")
+    testFoldConst("select ascii('μ•ˆ')")
+    testFoldConst("select ascii('δΈ­')")
+    testFoldConst("select ascii(cast('1' as string))")
+    testFoldConst("select ascii(cast('a' as string))")
+    testFoldConst("select ascii(cast('A' as string))")
+    testFoldConst("select ascii(cast('!' as string))")
+    testFoldConst("select ascii(cast('こ' as string))")
+    testFoldConst("select ascii(cast('μ•ˆ' as string))")
+    testFoldConst("select ascii(cast('δΈ­' as string))")
+    
+    // bin
+    testFoldConst("select bin(5)")
+    testFoldConst("select bin(-5)")
+    testFoldConst("select bin(9223372036854775807)")
+    testFoldConst("select bin(9223372036854775808)")
+    testFoldConst("select bin(-9223372036854775809)")
+    
+    // bit_length
     testFoldConst("select bit_length(\"abc\")")
+    testFoldConst("select bit_length(cast('abc' as string))")
+    testFoldConst("select bit_length('γ“γ‚“γ«γ‘γ―δΈ–η•Œ')")
+    testFoldConst("select bit_length('μ•ˆλ…•ν•˜μ„Έμš” 세계!')")
+
 
+    testFoldConst("select c1, c2, elt(c1, c2) from (select number as c1, 
'varchar' as c2 from numbers('number'='5') where number > 0) a")
+    
+    // char
+    testFoldConst("select char(65)")
+    testFoldConst("select char(-1)")
+    testFoldConst("select char(65535)")
+    
+    // character_length
+    testFoldConst("select character_length(cast('Hello World' as string))")
+    testFoldConst("select character_length('Hello World')")
+    testFoldConst("select character_length('δ½ ε₯½ δΈ–η•Œ')")
     testFoldConst("select char_length(\"abc\")")
+    testFoldConst("select char_length(cast('abc' as string))")
+    testFoldConst("select char_length('δ½ ε₯½ δΈ–η•Œ')")
 
+    // concat
     testFoldConst("select concat(\"a\", \"b\")")
     testFoldConst("select concat(\"a\", \"b\", \"c\")")
     testFoldConst("select concat(\"a\", null, \"c\")")
-
-    testFoldConst("select concat_ws(\"or\", \"d\", \"is\")")
+    testFoldConst("select concat(cast('a' as string), cast('b' as string))")
+    testFoldConst("select concat(cast('a' as string), cast('b' as string), 
cast('c' as string))")
+    testFoldConst("select concat(cast('a' as string), NULL, cast('c' as 
string))")
+    testFoldConst("select concat(cast('Hello' as string), cast(' ' as string), 
cast('World' as string))")
+    testFoldConst("select concat(CAST('Hello' AS STRING), CAST(' ' AS STRING), 
CAST('World' AS STRING))")
+    testFoldConst("select concat(CAST('Hello' AS STRING), CAST(NULL AS 
STRING))")
+    testFoldConst("select concat(cast('Hello' as string), NULL, cast('World' 
as string))")
+    testFoldConst("select concat(CAST(NULL AS STRING), CAST('World' AS 
STRING))")
+    testFoldConst("select concat('Hello', NULL, 'World')")
+    testFoldConst("select concat('Hello', ' ', 'World')")
+    testFoldConst("select concat('δ½ ε₯½', ' ', 'δΈ–η•Œ')")
+    
+    // concat_ws
+    testFoldConst("select concat_ws('-', '2024', '09', '02')")
+    testFoldConst("select concat_ws(NULL, [\"d\", \"is\"])")
     testFoldConst("select concat_ws(NULL, \"d\", \"is\")")
-    testFoldConst("select concat_ws(\"or\", \"d\", NULL,\"is\")")
+    testFoldConst("select concat_ws(\"or\", [\"d\", \"\",\"is\"])")
     testFoldConst("select concat_ws(\"or\", [\"d\", \"is\"])")
-    testFoldConst("select concat_ws(NULL, [\"d\", \"is\"])")
+    testFoldConst("select concat_ws(\"or\", \"d\", \"is\")")
     testFoldConst("select concat_ws(\"or\", [\"d\", NULL,\"is\"])")
-    testFoldConst("select concat_ws(\"or\", [\"d\", \"\",\"is\"])")
+    testFoldConst("select concat_ws(\"or\", \"d\", NULL,\"is\")")
+    testFoldConst("select concat_ws(' ', 'δ½ ε₯½', 'δΈ–η•Œ')")
+    
+    // elt
+    testFoldConst("select elt(0, cast('hello' as string), cast('doris' as 
string))")
+    testFoldConst("select elt(0, \"hello\", \"doris\")")
+    testFoldConst("select elt(1, cast('hello' as string), cast('doris' as 
string))")
+    testFoldConst("select elt(1, \"hello\", \"doris\")")
+    testFoldConst("select elt(2, cast('hello' as string), cast('doris' as 
string))")
+    testFoldConst("select elt(2, \"hello\", \"doris\")")
+    testFoldConst("select elt(3, cast('hello' as string), cast('doris' as 
string))")
+    testFoldConst("select elt(3, \"hello\", \"doris\")")
 
+    // ends_with
+    testFoldConst("select ends_with(cast('Hello doris' as string), 
cast('doris' as string))")
     testFoldConst("select ends_with(\"Hello doris\", \"doris\")")
-    testFoldConst("select ends_with(\"Hello doris\", \"Hello\")")
-
+    testFoldConst("select ends_with(\"γ“γ‚“γ«γ‘γ―δΈ–η•ŒοΌμ•ˆλ…•ν•˜μ„Έμš” 세계\", \"μ•ˆλ…•ν•˜μ„Έμš” 세계\")")
+    testFoldConst("select ends_with(\"μ•ˆλ…•ν•˜μ„Έμš” μ„Έκ³„γ“γ‚“γ«γ‘γ―δΈ–η•ŒοΌ\", \"γ“γ‚“γ«γ‘γ―δΈ–η•ŒοΌ\")")
+    testFoldConst("select ends_with(\"μ•ˆλ…•ν•˜μ„Έμš” μ„Έκ³„γ“γ‚“γ«γ‘γ―δΈ–η•Œ\", \"γ“γ‚“γ«γ‘γ―δΈ–η•Œ\")")
+
+    // field
+    testFoldConst("select field('b', 'a', 'b', 'c')")
+    testFoldConst("select field('d', 'a', 'b', 'c')")
+    testFoldConst("select field('こ', 'γ‚“', 'に', 'け', 'こ')")
+    testFoldConst("select field('=', '+', '=', '=', 'こ')")
+    testFoldConst("select field('==', '+', '=', '==', 'こ')")
+    testFoldConst("select field('=', '+', '==', '==', 'こ')")
+    
+    // find_in_set
+    testFoldConst("select find_in_set(\"a\", null)")
+    testFoldConst("select find_in_set('b', 'a,b,c')")
     testFoldConst("select find_in_set(\"b\", \"a,b,c\")")
+    testFoldConst("select find_in_set(cast('a' as string), NULL)")
+    testFoldConst("select find_in_set(cast('b' as string), cast('a,b,c' as 
string))")
+    testFoldConst("select find_in_set(cast('b' as string), cast('a,b,c' as 
string))")
+    testFoldConst("select find_in_set(cast('d' as string), cast('a,b,c' as 
string))")
+    testFoldConst("select find_in_set(cast('d' as string), cast('a,b,c' as 
string))")
+    testFoldConst("select find_in_set('d', 'a,b,c')")
     testFoldConst("select find_in_set(\"d\", \"a,b,c\")")
     testFoldConst("select find_in_set(null, \"a,b,c\")")
-    testFoldConst("select find_in_set(\"a\", null)")
-
+    testFoldConst("select find_in_set(NULL, cast('a,b,c' as string))")
+    testFoldConst("SELECT find_in_set('A', 'ε“ˆε“ˆε“ˆAAA')")
+    testFoldConst("SELECT find_in_set('ε“ˆ','ε“ˆε“ˆε“ˆAAA')")
+    testFoldConst("SELECT find_in_set(' ','ε“ˆε“ˆε“ˆAAA')")
+    testFoldConst("SELECT find_in_set('','ε“ˆε“ˆε“ˆAAA')")
+    testFoldConst("SELECT find_in_set(',','a,')")

Review Comment:
   1. the second param not separate by `'`
   2. the first param has `'`



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org

Reply via email to