Skip to content

Commit 4eb4325

Browse files
rivudhkgz
rivudhk
authored and
gz
committed
py: add a few tests for ASOF joins and MAP_KEYS function
Signed-off-by: rivudhk <rivudhkr@gmail.com>
1 parent 5c69fd3 commit 4eb4325

File tree

3 files changed

+137
-2
lines changed

3 files changed

+137
-2
lines changed

‎python/tests/runtime_aggtest/asof_tests/test_asof.py‎

Lines changed: 78 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -192,3 +192,81 @@ def __init__(self):
192192
LEFT ASOF JOIN asof_tbl2 t2
193193
MATCH_CONDITION ( t1.datee >= t2.datee)
194194
ON t1.id = t2.id;"""
195+
196+
197+
class asof_test11(TstView):
198+
def __init__(self):
199+
# Validated on DuckDB
200+
self.data = [
201+
{
202+
"id": 1,
203+
"t1_uuidd": "a3b0c442-98fc-1c14-9af7-4c2b95f9c16a",
204+
"t2_uuidd": None,
205+
},
206+
{
207+
"id": 2,
208+
"t1_uuidd": "e3b0c442-98fc-1c14-9af7-4c2b95f9c16a",
209+
"t2_uuidd": "a3b0c442-98fc-1c14-9af7-4c2b95f9c16a",
210+
},
211+
{
212+
"id": 3,
213+
"t1_uuidd": "a9b8c7d6-e5f4-3210-9999-abcdefabcdef",
214+
"t2_uuidd": None,
215+
},
216+
{
217+
"id": 4,
218+
"t1_uuidd": "ffffffff-ffff-ffff-ffff-ffffffffffff",
219+
"t2_uuidd": "efffffff-ffff-ffff-ffff-ffffffffffff",
220+
},
221+
{"id": 5, "t1_uuidd": None, "t2_uuidd": None},
222+
]
223+
self.sql = """CREATE MATERIALIZED VIEW asof_test11 AS SELECT
224+
t1.id, t1.uuidd AS t1_uuidd, t2.uuidd AS t2_uuidd
225+
FROM asof_tbl1 t1
226+
LEFT ASOF JOIN asof_tbl2 t2
227+
MATCH_CONDITION ( t1.uuidd >= t2.uuidd)
228+
ON t1.id = t2.id;"""
229+
230+
231+
class asof_test12(TstView):
232+
def __init__(self):
233+
# Validated on DuckDB
234+
self.data = [
235+
{"id": 1, "t1_arr": ["-0.14", "friends", "See you!"], "t2_arr": None},
236+
{
237+
"id": 2,
238+
"t1_arr": ["42", "sample", "-1.1", "2022-03-03", "yes"],
239+
"t2_arr": ["12", "sample", "-1.1", "2022-03-03", "yes"],
240+
},
241+
{"id": 3, "t1_arr": ["hello", "123", "0.0", None], "t2_arr": None},
242+
{
243+
"id": 4,
244+
"t1_arr": ["end", "2099", "12", "31"],
245+
"t2_arr": ["and", "2099", "12", "31"],
246+
},
247+
{"id": 5, "t1_arr": None, "t2_arr": None},
248+
]
249+
self.sql = """CREATE MATERIALIZED VIEW asof_test12 AS SELECT
250+
t1.id, t1.arr AS t1_arr, t2.arr AS t2_arr
251+
FROM asof_tbl1 t1
252+
LEFT ASOF JOIN asof_tbl2 t2
253+
MATCH_CONDITION ( t1.arr >= t2.arr)
254+
ON t1.id = t2.id;"""
255+
256+
257+
class asof_test13(TstView):
258+
def __init__(self):
259+
# Validated on DuckDB
260+
self.data = [
261+
{"id": 1, "t1_mapp": {"a": 15, "b": None}, "t2_mapp": None},
262+
{"id": 2, "t1_mapp": {"a": 3, "b": 9}, "t2_mapp": {"a": 1, "b": 9}},
263+
{"id": 3, "t1_mapp": {"a": 11, "b": 22}, "t2_mapp": None},
264+
{"id": 4, "t1_mapp": {"a": 200, "b": 200}, "t2_mapp": {"a": 100, "b": 200}},
265+
{"id": 5, "t1_mapp": None, "t2_mapp": None},
266+
]
267+
self.sql = """CREATE MATERIALIZED VIEW asof_test13 AS SELECT
268+
t1.id, t1.mapp AS t1_mapp, t2.mapp AS t2_mapp
269+
FROM asof_tbl1 t1
270+
LEFT ASOF JOIN asof_tbl2 t2
271+
MATCH_CONDITION ( t1.mapp >= t2.mapp)
272+
ON t1.id = t2.id;"""

‎python/tests/runtime_aggtest/asof_tests/test_asof_tbl.py‎

Lines changed: 38 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,10 @@ def __init__(self):
99
id INT, intt INT, str VARCHAR, decimall DECIMAL(6, 2), reall REAL, dbl DOUBLE, booll BOOL, bin BINARY,
1010
tmestmp TIMESTAMP,
1111
datee DATE,
12-
tme TIME)"""
12+
tme TIME,
13+
uuidd UUID,
14+
arr VARCHAR ARRAY,
15+
mapp MAP<VARCHAR, INT>)"""
1316
self.data = [
1417
{
1518
"id": 1,
@@ -23,6 +26,9 @@ def __init__(self):
2326
"tme": "13:23:44.456",
2427
"tmestmp": "2000-06-21 14:23:44.123",
2528
"datee": "2000-06-21",
29+
"uuidd": "a3b0c442-98fc-1c14-9af7-4c2b95f9c16a",
30+
"arr": ["-0.14", "friends", "See you!"],
31+
"mapp": {"a": 15, "b": None},
2632
},
2733
{
2834
"id": 2,
@@ -36,6 +42,9 @@ def __init__(self):
3642
"tme": "19:23:44.456",
3743
"tmestmp": "2019-06-21 14:23:44.123",
3844
"datee": "2019-06-21",
45+
"uuidd": "e3b0c442-98fc-1c14-9af7-4c2b95f9c16a",
46+
"arr": ["42", "sample", "-1.1", "2022-03-03", "yes"],
47+
"mapp": {"a": 3, "b": 9},
3948
},
4049
{
4150
"id": 3,
@@ -49,6 +58,9 @@ def __init__(self):
4958
"tme": "01:23:44.456",
5059
"tmestmp": "1978-06-21 14:23:44.123",
5160
"datee": "1978-06-21",
61+
"uuidd": "a9b8c7d6-e5f4-3210-9999-abcdefabcdef",
62+
"arr": ["hello", "123", "0.0", None],
63+
"mapp": {"a": 11, "b": 22},
5264
},
5365
{
5466
"id": 4,
@@ -62,6 +74,9 @@ def __init__(self):
6274
"tme": "23:23:44.456",
6375
"tmestmp": "2002-06-21 14:23:44.123",
6476
"datee": "2002-06-21",
77+
"uuidd": "ffffffff-ffff-ffff-ffff-ffffffffffff",
78+
"arr": ["end", "2099", "12", "31"],
79+
"mapp": {"a": 200, "b": 200},
6580
},
6681
{
6782
"id": 5,
@@ -75,6 +90,9 @@ def __init__(self):
7590
"tme": None,
7691
"tmestmp": None,
7792
"datee": None,
93+
"uuidd": None,
94+
"arr": None,
95+
"mapp": None,
7896
},
7997
]
8098

@@ -87,7 +105,10 @@ def __init__(self):
87105
id INT, intt INT, str VARCHAR, decimall DECIMAL(6, 2), reall REAL, dbl DOUBLE, booll BOOL, bin BINARY,
88106
tmestmp TIMESTAMP,
89107
datee DATE,
90-
tme TIME)"""
108+
tme TIME,
109+
uuidd UUID,
110+
arr VARCHAR ARRAY,
111+
mapp MAP<VARCHAR, INT>)"""
91112
self.data = [
92113
{
93114
"id": 1,
@@ -101,6 +122,9 @@ def __init__(self):
101122
"tme": "14:23:44.456",
102123
"tmestmp": "2020-06-21 14:23:44.123",
103124
"datee": "2020-06-21",
125+
"uuidd": "b3b0c442-98fc-1c14-9af7-4c2b95f9c16a",
126+
"arr": ["0.14", "friends", "See you!"],
127+
"mapp": {"a": 25, "b": None},
104128
},
105129
{
106130
"id": 2,
@@ -114,6 +138,9 @@ def __init__(self):
114138
"tme": "20:23:44.456",
115139
"tmestmp": "2021-06-21 14:23:44.123",
116140
"datee": "2021-06-21",
141+
"uuidd": "a3b0c442-98fc-1c14-9af7-4c2b95f9c16a",
142+
"arr": ["12", "sample", "-1.1", "2022-03-03", "yes"],
143+
"mapp": {"a": 1, "b": 9},
117144
},
118145
{
119146
"id": 3,
@@ -127,6 +154,9 @@ def __init__(self):
127154
"tme": "00:23:44.456",
128155
"tmestmp": "1977-06-21 14:23:44.123",
129156
"datee": "1977-06-21",
157+
"uuidd": "b9b8c7d6-e5f4-3210-9999-abcdefabcdef",
158+
"arr": ["hi", "123", "0.0", None],
159+
"mapp": {"a": 21, "b": 22},
130160
},
131161
{
132162
"id": 4,
@@ -140,6 +170,9 @@ def __init__(self):
140170
"tme": "22:23:44.456",
141171
"tmestmp": "2001-06-21 14:23:44.123",
142172
"datee": "2001-06-21",
173+
"uuidd": "efffffff-ffff-ffff-ffff-ffffffffffff",
174+
"arr": ["and", "2099", "12", "31"],
175+
"mapp": {"a": 100, "b": 200},
143176
},
144177
{
145178
"id": 5,
@@ -153,5 +186,8 @@ def __init__(self):
153186
"tme": None,
154187
"tmestmp": None,
155188
"datee": None,
189+
"uuidd": None,
190+
"arr": None,
191+
"mapp": None,
156192
},
157193
]

‎python/tests/runtime_aggtest/illarg_tests/test_arr_map_type_fn.py‎

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -685,3 +685,24 @@ def __init__(self):
685685
MAP_CONTAINS_KEY(bin, 'a') AS str
686686
FROM illegal_tbl"""
687687
self.expected_error = "Cannot apply 'MAP_CONTAINS_KEY' to arguments of type"
688+
689+
690+
# MAP_KEYS function
691+
class illarg_map_keys_legal(TstView):
692+
def __init__(self):
693+
# checked manually
694+
self.data = [{"mapp": ["a", "b"]}]
695+
self.sql = """CREATE MATERIALIZED VIEW map_keys_legal AS SELECT
696+
MAP_KEYS(mapp) AS mapp
697+
FROM illegal_tbl
698+
WHERE id = 0"""
699+
700+
701+
# Negative Test
702+
class illarg_map_keys_illegal(TstView):
703+
def __init__(self):
704+
# checked manually
705+
self.sql = """CREATE MATERIALIZED VIEW map_keys_illegal AS SELECT
706+
MAP_KEYS(uuidd) AS uuidd
707+
FROM illegal_tbl"""
708+
self.expected_error = "Cannot apply 'MAP_KEYS' to arguments of type"

0 commit comments

Comments
 (0)