Skip to content

Commit 489eae0

Browse files
rivudhkmihaibudiu
rivudhk
authored and
mihaibudiu
committed
py: add tests for AS OF joins with different ON clauses
Signed-off-by: rivudhk <rivudhkr@gmail.com>
1 parent bd38784 commit 489eae0

File tree

3 files changed

+171
-0
lines changed

3 files changed

+171
-0
lines changed

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

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8,6 +8,7 @@
88
from test_asof_multijoins import * # noqa: F403
99
from test_asof_illarg import * # noqa: F403
1010
from test_asof_subq import * # noqa: F403
11+
from test_asof_multi_on import * # noqa: F403
1112

1213

1314
def main():

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

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -76,3 +76,17 @@ def __init__(self):
7676
MATCH_CONDITION (t2.intt = (SELECT t2_sub.intt))
7777
ON t1.id = t2.id;"""
7878
self.expected_error = "table 't2_sub' not found"
79+
80+
81+
class asof_on_illegal(TstView):
82+
def __init__(self):
83+
# checked manually
84+
self.sql = """CREATE MATERIALIZED VIEW asof_on_illegal AS SELECT
85+
t1.id, t1.intt AS t1_intt, t2.intt AS t2_intt
86+
FROM asof_tbl1 t1
87+
LEFT ASOF JOIN asof_tbl2 t2
88+
MATCH_CONDITION (t1.intt >= t2.intt )
89+
ON t1.id != t2.id;"""
90+
self.expected_error = (
91+
"ASOF JOIN condition must be a conjunction of equality comparisons"
92+
)

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

Lines changed: 156 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,156 @@
1+
from tests.runtime_aggtest.aggtst_base import TstView
2+
3+
4+
class asof_multi_asof_joins(TstView):
5+
def __init__(self):
6+
# Validated on DUCKDB
7+
self.data = [
8+
{"id": 1, "t1_intt": 10, "t2_intt": None},
9+
{"id": 2, "t1_intt": 15, "t2_intt": None},
10+
{"id": 3, "t1_intt": 20, "t2_intt": None},
11+
{"id": 4, "t1_intt": 25, "t2_intt": None},
12+
{"id": 5, "t1_intt": None, "t2_intt": None},
13+
]
14+
self.sql = """CREATE MATERIALIZED VIEW asof_multi_asof_joins AS SELECT
15+
t1.id, t1.intt AS t1_intt, t2.intt AS t2_intt
16+
FROM asof_tbl1 t1
17+
LEFT ASOF JOIN asof_tbl2 t2
18+
MATCH_CONDITION (t1.intt >= t2.intt)
19+
ON t1.id = t2.id
20+
AND t1.decimall = t2.decimall
21+
LEFT ASOF JOIN asof_tbl3 t3
22+
MATCH_CONDITION (t2.intt >= t3.intt)
23+
ON t2.id = t3.id
24+
AND t2.booll = t3.booll;"""
25+
26+
27+
class asof_cmpx_on(TstView):
28+
def __init__(self):
29+
# Validated on DUCKDB
30+
self.data = [
31+
{"id": 1, "intt": 10},
32+
{"id": 2, "intt": 15},
33+
{"id": 3, "intt": 20},
34+
{"id": 4, "intt": 25},
35+
{"id": 5, "intt": None},
36+
]
37+
self.sql = """CREATE MATERIALIZED VIEW asof_cmpx_on AS SELECT
38+
t1.id, t1.intt
39+
FROM asof_tbl1 t1
40+
LEFT ASOF JOIN asof_tbl2 t2
41+
MATCH_CONDITION (t1.intt >= t2.intt)
42+
ON t1.arr = t2.arr
43+
AND t1.mapp = t2.mapp;"""
44+
45+
46+
class asof_cmpx_agg_on(TstView):
47+
def __init__(self):
48+
# Validated on DUCKDB
49+
self.data = [
50+
{"id": 1, "t1_sum": 10, "t2_sum": None},
51+
{"id": 2, "t1_sum": 15, "t2_sum": None},
52+
{"id": 3, "t1_sum": 20, "t2_sum": None},
53+
{"id": 4, "t1_sum": 25, "t2_sum": None},
54+
{"id": 5, "t1_sum": None, "t2_sum": None},
55+
]
56+
self.sql = """CREATE MATERIALIZED VIEW asof_cmpx_agg_on AS
57+
WITH t1_agg AS (
58+
SELECT id, SUM(intt) AS sum_intt
59+
FROM asof_tbl1
60+
GROUP BY id
61+
),
62+
t2_agg AS (
63+
SELECT id, SUM(intt) AS sum_intt
64+
FROM asof_tbl2
65+
GROUP BY id
66+
)
67+
SELECT t1_agg.id, t1_agg.sum_intt AS t1_sum, t2_agg.sum_intt AS t2_sum
68+
FROM t1_agg
69+
LEFT ASOF JOIN t2_agg
70+
MATCH_CONDITION (t1_agg.sum_intt >= t2_agg.sum_intt)
71+
ON t1_agg.sum_intt = t2_agg.sum_intt;"""
72+
73+
74+
class asof_same_match_on_condition(TstView):
75+
def __init__(self):
76+
# Validated on DUCKDB
77+
self.data = [
78+
{"intt": 10},
79+
{"intt": 15},
80+
{"intt": 20},
81+
{"intt": 25},
82+
{"intt": None},
83+
]
84+
self.sql = """CREATE MATERIALIZED VIEW asof_same_match_on_condition AS
85+
WITH t1_desc AS (
86+
SELECT intt FROM asof_tbl1 ORDER BY intt DESC
87+
)
88+
SELECT t1.intt
89+
FROM t1_desc t1
90+
LEFT ASOF JOIN asof_tbl2 t2
91+
MATCH_CONDITION (t1.intt >= t2.intt)
92+
ON t1.intt = t2.intt;"""
93+
94+
95+
class asof_computed_int_on(TstView):
96+
def __init__(self):
97+
# Validated on DUCKDB
98+
self.data = [
99+
{"id": 1, "t1_intt": 10, "interval_yr": 20},
100+
{"id": 2, "t1_intt": 15, "interval_yr": 2},
101+
{"id": 3, "t1_intt": 20, "interval_yr": -1},
102+
{"id": 4, "t1_intt": 25, "interval_yr": -1},
103+
{"id": 5, "t1_intt": None, "interval_yr": None},
104+
]
105+
self.sql = """CREATE MATERIALIZED VIEW asof_computed_int_on AS
106+
WITH joined AS (
107+
SELECT t1.id,
108+
t1.intt AS t1_intt,
109+
t2.intt AS t2_intt,
110+
TIMESTAMPDIFF(YEAR, t1.tmestmp, t2.tmestmp) AS interval_yr
111+
FROM asof_tbl1 t1
112+
JOIN asof_tbl2 t2
113+
ON t1.id = t2.id
114+
)
115+
SELECT j.id, j.t1_intt, interval_yr
116+
FROM joined j
117+
LEFT ASOF JOIN asof_tbl3 t3
118+
MATCH_CONDITION (j.interval_yr>= t3.intt)
119+
ON j.id = t3.id
120+
AND j.t1_intt = t3.intt;"""
121+
122+
123+
class asof_null_on_multiasof(TstView):
124+
def __init__(self):
125+
# Validated on DUCKDB
126+
self.data = [{"id": 5, "t1_intt": None, "t2_intt": None}]
127+
self.sql = """CREATE MATERIALIZED VIEW asof_null_on_multiasof AS SELECT
128+
t1.id, t1.intt AS t1_intt, t2.intt AS t2_intt
129+
FROM asof_tbl1 t1
130+
LEFT ASOF JOIN asof_tbl2 t2
131+
MATCH_CONDITION (t1.intt >= t2.intt)
132+
ON t1.bin = t2.bin
133+
AND t1.decimall = t2.decimall
134+
LEFT ASOF JOIN asof_tbl3 t3
135+
MATCH_CONDITION (t2.intt >= t3.intt)
136+
ON t2.uuidd = t3.uuidd
137+
AND t2.booll = t3.booll
138+
WHERE t1.id = 5;"""
139+
140+
141+
class asof_decimal_match_dbl_on(TstView):
142+
def __init__(self):
143+
# Validated on DUCKDB
144+
self.data = [
145+
{"id": 1, "t2_decimall": None},
146+
{"id": 2, "t2_decimall": None},
147+
{"id": 3, "t2_decimall": None},
148+
{"id": 4, "t2_decimall": None},
149+
{"id": 5, "t2_decimall": None},
150+
]
151+
self.sql = """CREATE MATERIALIZED VIEW asof_decimal_match_dbl_on AS SELECT
152+
t1.id, t2.decimall AS t2_decimall
153+
FROM asof_tbl1 t1
154+
LEFT ASOF JOIN asof_tbl2 t2
155+
MATCH_CONDITION (t1.decimall >= t2.decimall)
156+
ON t1.dbl = t2.dbl;"""

0 commit comments

Comments
 (0)