DayuanX commented on code in PR #54036:
URL: https://github.com/apache/doris/pull/54036#discussion_r2325723702
##########
regression-test/suites/load_p0/stream_load/test_map_load_and_function.groovy:
##########
@@ -170,6 +170,148 @@ suite("test_map_load_and_function", "p0") {
qt_select_map_values1 "SELECT map_values(map('k11', 1000, 'k22', 2000))"
qt_select_map_values2 "SELECT id, m, map_values(m) FROM ${testTable} ORDER
BY id"
+ // map_contains_entry: basic tests
+ qt_select_map_contains_entry1 "SELECT map_contains_entry(map('k11', 1000,
'k22', 2000), 'k11', 1000)"
+ qt_select_map_contains_entry2 "SELECT map_contains_entry(map('k11', 1000,
'k22', 2000), 'k22', 2000)"
+ qt_select_map_contains_entry3 "SELECT map_contains_entry(map('k11', 1000,
'k22', 2000), 'k11', 2000)"
+ qt_select_map_contains_entry4 "SELECT map_contains_entry(map('k11', 1000,
'k22', 2000), 'k33', 1000)"
+ qt_select_map_contains_entry5 "SELECT map_contains_entry(map('k11', 1000,
'k22', 2000), 'nokey', 1000)"
+ qt_select_map_contains_entry6 "SELECT map_contains_entry(map('k11', 1000,
'k22', 2000), '', 1000)"
+ qt_select_map_contains_entry7 "SELECT map_contains_entry(map('k11', 1000,
'k22', 2000), NULL, 1000)"
+ qt_select_map_contains_entry8 "SELECT map_contains_entry(map('k11', 1000,
'k22', 2000), 'k11', NULL)"
+ qt_select_map_contains_entry9 "SELECT map_contains_entry(map('k11', 1000,
'k22', 2000), NULL, NULL)"
+ qt_select_map_contains_entry10 "SELECT map_contains_entry(map('', 0), '',
0)"
+ qt_select_map_contains_entry11 "SELECT map_contains_entry(map(), 'k1',
100)"
+ qt_select_map_contains_entry12 "SELECT map_contains_entry(map('k1', 100),
'', 100)"
+ qt_select_map_contains_entry13 "SELECT map_contains_entry(map(NULL, 100),
NULL, 100)"
+ qt_select_map_contains_entry14 "SELECT map_contains_entry(map('k1', NULL),
'k1', NULL)"
+
+ // map_contains_entry: tests with actual data from first table
+ qt_select_map_contains_entry101 "SELECT id, m, map_contains_entry(m, 'k1',
23) FROM ${testTable} ORDER BY id"
+ qt_select_map_contains_entry102 "SELECT id, m, map_contains_entry(m, 'k2',
20) FROM ${testTable} ORDER BY id"
+ qt_select_map_contains_entry103 "SELECT id, m, map_contains_entry(m, '
11amory ', 66) FROM ${testTable} ORDER BY id"
+ qt_select_map_contains_entry104 "SELECT id, m, map_contains_entry(m,
'beat', 31) FROM ${testTable} ORDER BY id"
+ qt_select_map_contains_entry105 "SELECT id, m, map_contains_entry(m, '
clever ', 300) FROM ${testTable} ORDER BY id"
+ qt_select_map_contains_entry106 "SELECT id, m, map_contains_entry(m, '
33,amory ', 41) FROM ${testTable} ORDER BY id"
+ qt_select_map_contains_entry107 "SELECT id, m, map_contains_entry(m, ' bet
', 400) FROM ${testTable} ORDER BY id"
+ qt_select_map_contains_entry108 "SELECT id, m, map_contains_entry(m, '
cler ', 33) FROM ${testTable} ORDER BY id"
+ qt_select_map_contains_entry109 "SELECT id, m, map_contains_entry(m, '
1,amy ', 2) FROM ${testTable} ORDER BY id"
+ qt_select_map_contains_entry110 "SELECT id, m, map_contains_entry(m, ' k2
', 26) FROM ${testTable} ORDER BY id"
+ qt_select_map_contains_entry111 "SELECT id, m, map_contains_entry(m,
'null', 90) FROM ${testTable} ORDER BY id"
+ qt_select_map_contains_entry112 "SELECT id, m, map_contains_entry(m, '',
33) FROM ${testTable} ORDER BY id"
+ qt_select_map_contains_entry113 "SELECT id, m, map_contains_entry(m, NULL,
1) FROM ${testTable} ORDER BY id"
+ qt_select_map_contains_entry114 "SELECT id, m, map_contains_entry(m,
'nokey', 100) FROM ${testTable} ORDER BY id"
+ qt_select_map_contains_entry115 "SELECT id, m, map_contains_entry(m, 'k1',
NULL) FROM ${testTable} ORDER BY id"
+ qt_select_map_contains_entry116 "SELECT id, m, map_contains_entry(m, NULL,
NULL) FROM ${testTable} ORDER BY id"
+ qt_select_map_contains_entry_var2 "SELECT id, m, map_keys(m)[1],
map_values(m)[1], map_contains_entry(m, map_keys(m)[1], map_values(m)[1]) FROM
${testTable} ORDER BY id"
+
+ // map_contains_entry: tests with duplicate keys
+ qt_select_map_contains_entry_dup1 "SELECT map_contains_entry(map('k1',
100, 'k1', 200), 'k1', 100)"
+ qt_select_map_contains_entry_dup2 "SELECT map_contains_entry(map('k1',
100, 'k1', 200), 'k1', 200)"
+ qt_select_map_contains_entry_dup3 "SELECT map_contains_entry(map('k1',
100, 'k1', 200), 'k1', 300)"
+ qt_select_map_contains_entry_dup4 "SELECT map_contains_entry(map('k1',
100, 'k2', 200, 'k1', 300), 'k1', 100)"
+ qt_select_map_contains_entry_dup5 "SELECT map_contains_entry(map('k1',
100, 'k2', 200, 'k1', 300), 'k1', 300)"
+ qt_select_map_contains_entry_dup6 "SELECT map_contains_entry(map('k1',
100, 'k2', 200, 'k1', 300), 'k2', 200)"
+ qt_select_map_contains_entry_dup7 "SELECT map_contains_entry(map(1, 'a',
2, 'b', 1, 'c'), 1, 'a')"
+ qt_select_map_contains_entry_dup8 "SELECT map_contains_entry(map(1, 'a',
2, 'b', 1, 'c'), 1, 'c')"
+ qt_select_map_contains_entry_dup9 "SELECT map_contains_entry(map(1, 'a',
2, 'b', 1, 'c'), 1, 'b')"
+ qt_select_map_contains_entry_dup10 "SELECT map_contains_entry(map('k1',
NULL, 'k1', 100), 'k1', NULL)"
+ qt_select_map_contains_entry_dup11 "SELECT map_contains_entry(map('k1',
NULL, 'k1', 100), 'k1', 100)"
+ qt_select_map_contains_entry_dup12 "SELECT map_contains_entry(map(NULL,
100, NULL, 200), NULL, 100)"
+ qt_select_map_contains_entry_dup13 "SELECT map_contains_entry(map(NULL,
100, NULL, 200), NULL, 200)"
+ qt_select_map_contains_entry_dup14 "SELECT map_contains_entry(map('a', 1,
'b', 2, 'a', 3, 'c', 4, 'a', 5), 'a', 1)"
+ qt_select_map_contains_entry_dup15 "SELECT map_contains_entry(map('a', 1,
'b', 2, 'a', 3, 'c', 4, 'a', 5), 'a', 3)"
+ qt_select_map_contains_entry_dup16 "SELECT map_contains_entry(map('a', 1,
'b', 2, 'a', 3, 'c', 4, 'a', 5), 'a', 5)"
+ qt_select_map_contains_entry_dup17 "SELECT map_contains_entry(map('a', 1,
'b', 2, 'a', 3, 'c', 4, 'a', 5), 'a', 2)"
+
+ // map_contains_entry: tests with time/date type
+ qt_select_map_contains_entry_date1 "SELECT
map_contains_entry(map('2023-01-01', CAST('2023-01-01' AS DATE), '2023-12-31',
CAST('2023-12-31' AS DATE)), '2023-01-01', CAST('2023-01-01' AS DATE))"
+ qt_select_map_contains_entry_date2 "SELECT
map_contains_entry(map('2023-01-01', CAST('2023-01-01' AS DATE), '2023-12-31',
CAST('2023-12-31' AS DATE)), '2023-01-01', CAST('2023-01-02' AS DATE))"
+ qt_select_map_contains_entry_date3 "SELECT
map_contains_entry(map(CAST('2023-01-01' AS DATE), 'start', CAST('2023-12-31'
AS DATE), 'end'), CAST('2023-01-01' AS DATE), 'start')"
+ qt_select_map_contains_entry_date4 "SELECT
map_contains_entry(map(CAST('2023-01-01' AS DATE), 'start', CAST('2023-12-31'
AS DATE), 'end'), CAST('2023-01-01' AS DATE), 'end')"
+ qt_select_map_contains_entry_datetime1 "SELECT
map_contains_entry(map('dt1', CAST('2023-01-01 10:30:00' AS DATETIME), 'dt2',
CAST('2023-12-31 23:59:59' AS DATETIME)), 'dt1', CAST('2023-01-01 10:30:00' AS
DATETIME))"
+ qt_select_map_contains_entry_datetime2 "SELECT
map_contains_entry(map('dt1', CAST('2023-01-01 10:30:00' AS DATETIME), 'dt2',
CAST('2023-12-31 23:59:59' AS DATETIME)), 'dt1', CAST('2023-01-01 10:30:01' AS
DATETIME))"
+ qt_select_map_contains_entry_datetime3 "SELECT
map_contains_entry(map(CAST('2023-01-01 10:30:00' AS DATETIME), 'morning',
CAST('2023-12-31 23:59:59' AS DATETIME), 'night'), CAST('2023-01-01 10:30:00'
AS DATETIME), 'morning')"
+ qt_select_map_contains_entry_datetime4 "SELECT
map_contains_entry(map(CAST('2023-01-01 10:30:00' AS DATETIME), 'morning',
CAST('2023-12-31 23:59:59' AS DATETIME), 'night'), CAST('2023-01-01 10:30:00'
AS DATETIME), 'night')"
+ qt_select_map_contains_entry_datev2_1 "SELECT
map_contains_entry(map('start', datev2('2023-01-01'), 'end',
datev2('2023-12-31')), 'start', datev2('2023-01-01'))"
+ qt_select_map_contains_entry_datev2_2 "SELECT
map_contains_entry(map('start', datev2('2023-01-01'), 'end',
datev2('2023-12-31')), 'start', datev2('2023-01-02'))"
+ qt_select_map_contains_entry_datev2_3 "SELECT
map_contains_entry(map(datev2('2023-01-01'), 100, datev2('2023-12-31'), 200),
datev2('2023-01-01'), 100)"
+ qt_select_map_contains_entry_datev2_4 "SELECT
map_contains_entry(map(datev2('2023-01-01'), 100, datev2('2023-12-31'), 200),
datev2('2023-01-01'), 200)"
+ qt_select_map_contains_entry_datetimev2_1 "SELECT
map_contains_entry(map('event1', CAST('2023-01-01 10:30:00.123' AS
DATETIMEV2(3)), 'event2', CAST('2023-12-31 23:59:59.999' AS DATETIMEV2(3))),
'event1', CAST('2023-01-01 10:30:00.123' AS DATETIMEV2(3)))"
+ qt_select_map_contains_entry_datetimev2_2 "SELECT
map_contains_entry(map('event1', CAST('2023-01-01 10:30:00.123' AS
DATETIMEV2(3)), 'event2', CAST('2023-12-31 23:59:59.999' AS DATETIMEV2(3))),
'event1', CAST('2023-01-01 10:30:00.124' AS DATETIMEV2(3)))"
+ qt_select_map_contains_entry_datetimev2_3 "SELECT
map_contains_entry(map(CAST('2023-01-01 10:30:00.123' AS DATETIMEV2(3)), 'A',
CAST('2023-12-31 23:59:59.999' AS DATETIMEV2(3)), 'B'), CAST('2023-01-01
10:30:00.123' AS DATETIMEV2(3)), 'A')"
+ qt_select_map_contains_entry_datetimev2_4 "SELECT
map_contains_entry(map(CAST('2023-01-01 10:30:00.123' AS DATETIMEV2(3)), 'A',
CAST('2023-12-31 23:59:59.999' AS DATETIMEV2(3)), 'B'), CAST('2023-01-01
10:30:00.123' AS DATETIMEV2(3)), 'B')"
+
+ // map_contains_entry: test all type combination (N^2 auto-generated)
+ def allTypes = [
+ [type: "STRING", testKey: "'str1'", testValue: "'str2'"],
+ [type: "BOOLEAN", testKey: "true", testValue: "false"],
+ [type: "TINYINT", testKey: "CAST(1 AS TINYINT)", testValue: "CAST(2 AS
TINYINT)"],
+ [type: "SMALLINT", testKey: "CAST(100 AS SMALLINT)", testValue:
"CAST(200 AS SMALLINT)"],
+ [type: "INT", testKey: "CAST(1000 AS INT)", testValue: "CAST(2000 AS
INT)"],
+ [type: "BIGINT", testKey: "CAST(10000 AS BIGINT)", testValue:
"CAST(20000 AS BIGINT)"],
+ [type: "LARGEINT", testKey: "CAST(100000 AS LARGEINT)", testValue:
"CAST(200000 AS LARGEINT)"],
+ [type: "FLOAT", testKey: "CAST(1.1 AS FLOAT)", testValue: "CAST(2.2 AS
FLOAT)"],
+ [type: "DOUBLE", testKey: "CAST(10.1 AS DOUBLE)", testValue:
"CAST(20.2 AS DOUBLE)"],
+ [type: "DATE", testKey: "CAST('2023-01-01' AS DATE)", testValue:
"CAST('2023-12-31' AS DATE)"],
+ [type: "DATETIME", testKey: "CAST('2023-01-01 10:30:00' AS DATETIME)",
testValue: "CAST('2023-12-31 23:59:59' AS DATETIME)"],
+ [type: "DATEV2", testKey: "datev2('2023-01-01')", testValue:
"datev2('2023-12-31')"],
+ [type: "DATETIMEV2(3)", testKey: "CAST('2023-01-01 10:30:00.123' AS
DATETIMEV2(3))", testValue: "CAST('2023-12-31 23:59:59.999' AS DATETIMEV2(3))"],
+ [type: "CHAR(5)", testKey: "CAST('abc' AS CHAR(5))", testValue:
"CAST('xyz' AS CHAR(5))"],
+ [type: "VARCHAR(10)", testKey: "CAST('hello' AS VARCHAR(10))",
testValue: "CAST('world' AS VARCHAR(10))"]
+ ]
+
+ def testCounter = 1
+ for (int i = 0; i < allTypes.size(); i++) {
+ for (int j = 0; j < allTypes.size(); j++) {
+ def keyType = allTypes[i]
+ def valueType = allTypes[j]
+ def testName =
"qt_select_map_contains_entry_type_combo_${testCounter}"
+ def mapExpr = "map(${keyType.testKey}, ${valueType.testValue})"
+ def sqlQuery = "SELECT map_contains_entry(${mapExpr},
${keyType.testKey}, ${valueType.testValue})"
+
+ def sqlResult = sql sqlQuery
+ assertTrue(sqlResult[0][0], "MapContainsEntry: Combination
${testCounter} failed, KeyType: ${keyType.type}, ValueType: ${valueType.type}")
+ testCounter++
+ }
+ }
+
+ // map_contains_entry: decimal-only tests
+ def decimalTypes = [
+ [type: "DECIMALV2(10,2)", testKey: "CAST(123.45 AS DECIMALV2(10,2))",
testValue: "CAST(234.56 AS DECIMALV2(10,2))"],
+ [type: "DECIMAL(7,3)", testKey: "CAST(123.456 AS DECIMAL(7,3))",
testValue: "CAST(234.567 AS DECIMAL(7,3))"],
+ [type: "DECIMAL(10,2)", testKey: "CAST(123.45 AS DECIMAL(10,2))",
testValue: "CAST(234.56 AS DECIMAL(10,2))"],
+ [type: "DECIMAL(30,10)", testKey: "CAST(12345.6789 AS
DECIMAL(30,10))", testValue: "CAST(23456.7891 AS DECIMAL(30,10))"]
+ ]
+
+ for (def d : decimalTypes) {
+ def mapExpr = "map(${d.testKey}, ${d.testValue})"
+ def sqlQuery = "SELECT map_contains_entry(${mapExpr}, ${d.testKey},
${d.testValue})"
+ def sqlResult = sql sqlQuery
+ assertTrue(sqlResult[0][0], "MapContainsEntry: Decimal tests failed
for ${d.type}")
+ }
+
+ // map_entries: basic tests
+ qt_select_map_entries1 "SELECT map_entries(map('k11', 1000, 'k22', 2000))"
Review Comment:
done
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
