Skip to content

Commit 85393b2

Browse files
rivudhkgz
rivudhk
authored and
gz
committed
py: add ASOF join tests for NULL and non-NULL columns
Signed-off-by: rivudhk <rivudhkr@gmail.com>
1 parent 3ab16fc commit 85393b2

File tree

2 files changed

+393
-7
lines changed

2 files changed

+393
-7
lines changed

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

Lines changed: 290 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -270,3 +270,293 @@ def __init__(self):
270270
LEFT ASOF JOIN asof_tbl2 t2
271271
MATCH_CONDITION ( t1.mapp >= t2.mapp)
272272
ON t1.id = t2.id;"""
273+
274+
275+
class asof_test14(TstView):
276+
def __init__(self):
277+
# Validated on DuckDB
278+
self.data = [
279+
{"id": 1, "t3_intt": 5, "t1_intt": None},
280+
{"id": 2, "t3_intt": 16, "t1_intt": 15},
281+
{"id": 3, "t3_intt": 70, "t1_intt": 20},
282+
{"id": 4, "t3_intt": 12, "t1_intt": None},
283+
{"id": 5, "t3_intt": 112, "t1_intt": None},
284+
]
285+
self.sql = """CREATE MATERIALIZED VIEW asof_test14 AS SELECT
286+
t3.id, t3.intt AS t3_intt, t1.intt AS t1_intt
287+
FROM asof_tbl3 t3
288+
LEFT ASOF JOIN asof_tbl1 t1
289+
MATCH_CONDITION (t3.intt >= t1.intt )
290+
ON t3.id = t1.id;"""
291+
292+
293+
class asof_test15(TstView):
294+
def __init__(self):
295+
# Validated on DuckDB
296+
self.data = [
297+
{"id": 1, "t3_str": "bye", "t1_str": "apple"},
298+
{"id": 2, "t3_str": "hi", "t1_str": "cat"},
299+
{"id": 3, "t3_str": "ciao", "t1_str": None},
300+
{"id": 4, "t3_str": "c you!", "t1_str": None},
301+
{"id": 5, "t3_str": "sayonara!", "t1_str": None},
302+
]
303+
self.sql = """CREATE MATERIALIZED VIEW asof_test15 AS SELECT
304+
t3.id, t3.str AS t3_str, t1.str AS t1_str
305+
FROM asof_tbl3 t3
306+
LEFT ASOF JOIN asof_tbl1 t1
307+
MATCH_CONDITION ( t3.str >= t1.str )
308+
ON t3.id = t1.id;"""
309+
310+
311+
class asof_test16(TstView):
312+
def __init__(self):
313+
# Validated on DuckDB
314+
self.data = [
315+
{
316+
"id": 1,
317+
"t3_decimall": Decimal("10.10"),
318+
"t1_decimall": Decimal("-1111.52"),
319+
},
320+
{"id": 2, "t3_decimall": Decimal("-256.25"), "t1_decimall": None},
321+
{
322+
"id": 3,
323+
"t3_decimall": Decimal("64.32"),
324+
"t1_decimall": Decimal("-123.45"),
325+
},
326+
{"id": 4, "t3_decimall": Decimal("0.01"), "t1_decimall": Decimal("0.00")},
327+
{"id": 5, "t3_decimall": Decimal("1.01"), "t1_decimall": None},
328+
]
329+
self.sql = """CREATE MATERIALIZED VIEW asof_test17 AS SELECT
330+
t3.id, t3.decimall AS t3_decimall, t1.decimall AS t1_decimall
331+
FROM asof_tbl3 t3
332+
LEFT ASOF JOIN asof_tbl1 t1
333+
MATCH_CONDITION ( t3.decimall >= t1.decimall )
334+
ON t3.id = t1.id;"""
335+
336+
337+
class asof_test18(TstView):
338+
def __init__(self):
339+
# Validated on DuckDB
340+
self.data = [
341+
{"id": 1, "t3_reall": Decimal("10.001"), "t1_reall": Decimal("-57681.18")},
342+
{"id": 2, "t3_reall": Decimal("-0.1234567"), "t1_reall": None},
343+
{"id": 3, "t3_reall": Decimal("-987.0"), "t1_reall": None},
344+
{"id": 4, "t3_reall": Decimal("1.618"), "t1_reall": Decimal("0.0")},
345+
{"id": 5, "t3_reall": Decimal("12.618"), "t1_reall": None},
346+
]
347+
self.sql = """CREATE MATERIALIZED VIEW asof_test18 AS SELECT
348+
t3.id, t3.reall AS t3_reall, t1.reall AS t1_reall
349+
FROM asof_tbl3 t3
350+
LEFT ASOF JOIN asof_tbl1 t1
351+
MATCH_CONDITION ( t3.reall >= t1.reall )
352+
ON t3.id = t1.id;"""
353+
354+
355+
class asof_test19(TstView):
356+
def __init__(self):
357+
# Validated on DuckDB
358+
self.data = [
359+
{
360+
"id": 1,
361+
"t3_dbl": Decimal("10.000001"),
362+
"t1_dbl": Decimal("-38.2711234601246"),
363+
},
364+
{
365+
"id": 2,
366+
"t3_dbl": Decimal("-0.00256"),
367+
"t1_dbl": Decimal("-0.82711234601246"),
368+
},
369+
{"id": 3, "t3_dbl": Decimal("-999.9999999"), "t1_dbl": None},
370+
{"id": 4, "t3_dbl": Decimal("3.14159265358979"), "t1_dbl": Decimal("0.0")},
371+
{"id": 5, "t3_dbl": Decimal("13.14159265358979"), "t1_dbl": None},
372+
]
373+
self.sql = """CREATE MATERIALIZED VIEW asof_test19 AS SELECT
374+
t3.id, t3.dbl AS t3_dbl, t1.dbl AS t1_dbl
375+
FROM asof_tbl3 t3
376+
LEFT ASOF JOIN asof_tbl1 t1
377+
MATCH_CONDITION ( t3.dbl >= t1.dbl)
378+
ON t3.id = t1.id;"""
379+
380+
381+
class asof_test20(TstView):
382+
def __init__(self):
383+
# Validated on DuckDB
384+
self.data = [
385+
{"id": 1, "t3_booll": True, "t1_booll": False},
386+
{"id": 2, "t3_booll": False, "t1_booll": None},
387+
{"id": 3, "t3_booll": True, "t1_booll": False},
388+
{"id": 4, "t3_booll": False, "t1_booll": None},
389+
{"id": 5, "t3_booll": False, "t1_booll": None},
390+
]
391+
self.sql = """CREATE MATERIALIZED VIEW asof_test20 AS SELECT
392+
t3.id, t3.booll AS t3_booll, t1.booll AS t1_booll
393+
FROM asof_tbl3 t3
394+
LEFT ASOF JOIN asof_tbl1 t1
395+
MATCH_CONDITION ( t3.booll >= t1.booll)
396+
ON t3.id = t1.id;"""
397+
398+
399+
class asof_test21(TstView):
400+
def __init__(self):
401+
# Validated on DuckDB
402+
self.data = [
403+
{"id": 1, "t3_bin": "0b1620", "t1_bin": "0a1620"},
404+
{"id": 2, "t3_bin": "0f3716", "t1_bin": None},
405+
{"id": 3, "t3_bin": "0c1037", "t1_bin": None},
406+
{"id": 4, "t3_bin": "2c5863", "t1_bin": "16172c"},
407+
{"id": 5, "t3_bin": "90bcc7", "t1_bin": None},
408+
]
409+
self.sql = """CREATE MATERIALIZED VIEW asof_test21 AS SELECT
410+
t3.id, t3.bin AS t3_bin, t1.bin AS t1_bin
411+
FROM asof_tbl3 t3
412+
LEFT ASOF JOIN asof_tbl1 t1
413+
MATCH_CONDITION ( t3.bin >= t1.bin)
414+
ON t3.id = t1.id;"""
415+
416+
417+
class asof_test22(TstView):
418+
def __init__(self):
419+
# Validated on DuckDB
420+
self.data = [
421+
{"id": 1, "t3_tme": "14:23:44.456", "t1_tme": "13:23:44.456"},
422+
{"id": 2, "t3_tme": "20:23:44.456", "t1_tme": "19:23:44.456"},
423+
{"id": 3, "t3_tme": "00:23:44.456", "t1_tme": None},
424+
{"id": 4, "t3_tme": "22:23:44.456", "t1_tme": None},
425+
{"id": 5, "t3_tme": "20:23:44.456", "t1_tme": None},
426+
]
427+
self.sql = """CREATE MATERIALIZED VIEW asof_test22 AS SELECT
428+
t3.id, t3.tme AS t3_tme, t1.tme AS t1_tme
429+
FROM asof_tbl3 t3
430+
LEFT ASOF JOIN asof_tbl1 t1
431+
MATCH_CONDITION ( t3.tme >= t1.tme)
432+
ON t3.id = t1.id;"""
433+
434+
435+
class asof_test23(TstView):
436+
def __init__(self):
437+
# Validated on DuckDB
438+
self.data = [
439+
{
440+
"id": 1,
441+
"t3_tmestmp": "2020-06-21T14:23:44.123",
442+
"t1_tmestmp": "2000-06-21T14:23:44.123",
443+
},
444+
{
445+
"id": 2,
446+
"t3_tmestmp": "2021-06-21T14:23:44.123",
447+
"t1_tmestmp": "2019-06-21T14:23:44.123",
448+
},
449+
{"id": 3, "t3_tmestmp": "1977-06-21T14:23:44.123", "t1_tmestmp": None},
450+
{"id": 4, "t3_tmestmp": "2001-06-21T14:23:44.123", "t1_tmestmp": None},
451+
{"id": 5, "t3_tmestmp": "2000-06-21T14:23:44.123", "t1_tmestmp": None},
452+
]
453+
self.sql = """CREATE MATERIALIZED VIEW asof_test23 AS SELECT
454+
t3.id, t3.tmestmp AS t3_tmestmp, t1.tmestmp AS t1_tmestmp
455+
FROM asof_tbl3 t3
456+
LEFT ASOF JOIN asof_tbl1 t1
457+
MATCH_CONDITION ( t3.tmestmp >= t1.tmestmp)
458+
ON t3.id = t1.id;"""
459+
460+
461+
class asof_test24(TstView):
462+
def __init__(self):
463+
# Validated on DuckDB
464+
self.data = [
465+
{"id": 1, "t3_datee": "2020-06-21", "t1_datee": "2000-06-21"},
466+
{"id": 2, "t3_datee": "2021-06-21", "t1_datee": "2019-06-21"},
467+
{"id": 3, "t3_datee": "1977-06-21", "t1_datee": None},
468+
{"id": 4, "t3_datee": "2001-06-21", "t1_datee": None},
469+
{"id": 5, "t3_datee": "2000-06-21", "t1_datee": None},
470+
]
471+
self.sql = """CREATE MATERIALIZED VIEW asof_test24 AS SELECT
472+
t3.id, t3.datee AS t3_datee, t1.datee AS t1_datee
473+
FROM asof_tbl3 t3
474+
LEFT ASOF JOIN asof_tbl1 t1
475+
MATCH_CONDITION ( t3.datee >= t1.datee)
476+
ON t3.id = t1.id;"""
477+
478+
479+
class asof_test25(TstView):
480+
def __init__(self):
481+
# Validated on DuckDB
482+
self.data = [
483+
{
484+
"id": 1,
485+
"t3_uuidd": "b3b0c442-98fc-1c14-9af7-4c2b95f9c16a",
486+
"t1_uuidd": "a3b0c442-98fc-1c14-9af7-4c2b95f9c16a",
487+
},
488+
{
489+
"id": 2,
490+
"t3_uuidd": "a3b0c442-98fc-1c14-9af7-4c2b95f9c16a",
491+
"t1_uuidd": None,
492+
},
493+
{
494+
"id": 3,
495+
"t3_uuidd": "b9b8c7d6-e5f4-3210-9999-abcdefabcdef",
496+
"t1_uuidd": "a9b8c7d6-e5f4-3210-9999-abcdefabcdef",
497+
},
498+
{
499+
"id": 4,
500+
"t3_uuidd": "efffffff-ffff-ffff-ffff-ffffffffffff",
501+
"t1_uuidd": None,
502+
},
503+
{
504+
"id": 5,
505+
"t3_uuidd": "ffffffff-ffff-ffff-ffff-ffffffffffff",
506+
"t1_uuidd": None,
507+
},
508+
]
509+
self.sql = """CREATE MATERIALIZED VIEW asof_test25 AS SELECT
510+
t3.id, t3.uuidd AS t3_uuidd, t1.uuidd AS t1_uuidd
511+
FROM asof_tbl3 t3
512+
LEFT ASOF JOIN asof_tbl1 t1
513+
MATCH_CONDITION ( t3.uuidd >= t1.uuidd)
514+
ON t3.id = t1.id;"""
515+
516+
517+
class asof_test26(TstView):
518+
def __init__(self):
519+
# Validated on DuckDB
520+
self.data = [
521+
{
522+
"id": 1,
523+
"t3_arr": ["0.14", "friends", "See you!"],
524+
"t1_arr": ["-0.14", "friends", "See you!"],
525+
},
526+
{
527+
"id": 2,
528+
"t3_arr": ["12", "sample", "-1.1", "2022-03-03", "yes"],
529+
"t1_arr": None,
530+
},
531+
{
532+
"id": 3,
533+
"t3_arr": ["hi", "123", "0.0", None],
534+
"t1_arr": ["hello", "123", "0.0", None],
535+
},
536+
{"id": 4, "t3_arr": ["and", "2099", "12", "31"], "t1_arr": None},
537+
{"id": 5, "t3_arr": ["12", "31"], "t1_arr": None},
538+
]
539+
self.sql = """CREATE MATERIALIZED VIEW asof_test26 AS SELECT
540+
t3.id, t3.arr AS t3_arr, t1.arr AS t1_arr
541+
FROM asof_tbl3 t3
542+
LEFT ASOF JOIN asof_tbl1 t1
543+
MATCH_CONDITION ( t3.arr >= t1.arr)
544+
ON t3.id = t1.id;"""
545+
546+
547+
class asof_test27(TstView):
548+
def __init__(self):
549+
# Validated on DuckDB
550+
self.data = [
551+
{"id": 1, "t3_mapp": {"a": 25, "b": None}, "t1_mapp": {"a": 15, "b": None}},
552+
{"id": 2, "t3_mapp": {"a": 1, "b": 9}, "t1_mapp": None},
553+
{"id": 3, "t3_mapp": {"a": 21, "b": 22}, "t1_mapp": {"a": 11, "b": 22}},
554+
{"id": 4, "t3_mapp": {"a": 100, "b": 200}, "t1_mapp": None},
555+
{"id": 5, "t3_mapp": {"a": 1000, "b": 2000}, "t1_mapp": None},
556+
]
557+
self.sql = """CREATE MATERIALIZED VIEW asof_test27 AS SELECT
558+
t3.id, t3.mapp AS t3_mapp, t1.mapp AS t1_mapp
559+
FROM asof_tbl3 t3
560+
LEFT ASOF JOIN asof_tbl1 t1
561+
MATCH_CONDITION ( t3.mapp >= t1.mapp)
562+
ON t3.id = t1.id;"""

0 commit comments

Comments
 (0)