Skip to content

Commit 3afc646

Browse files
rivudhkrivudhk
committed
py: added as-of tests with sub queries on left input
Signed-off-by: rivudhk <rivudhkr@gmail.com>
1 parent b6bf6c1 commit 3afc646

File tree

3 files changed

+148
-0
lines changed

3 files changed

+148
-0
lines changed

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2,10 +2,12 @@
22

33
from tests.runtime_aggtest.aggtst_base import * # noqa: F403
44
from tests.runtime_aggtest.atest_run import run # noqa: F403
5+
56
from test_asof_tbl import * # noqa: F403
67
from test_asof import * # noqa: F403
78
from test_asof_multijoins import * # noqa: F403
89
from test_asof_illarg import * # noqa: F403
10+
from test_asof_subq import * # noqa: F403
911

1012

1113
def main():

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

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -47,3 +47,32 @@ def __init__(self):
4747
MATCH_CONDITION (t1.intt >= t2.intt )
4848
ON t1.id = t2.id;"""
4949
self.expected_error = "Currently only left asof joins are supported."
50+
51+
52+
class asof_test_illarg5(TstView):
53+
def __init__(self):
54+
# checked manually
55+
self.sql = """CREATE MATERIALIZED VIEW illarg5 AS
56+
WITH combined AS (SELECT
57+
t1.id AS t1_id, t1.intt AS t1_intt
58+
FROM asof_tbl1 t1)
59+
SELECT
60+
c.t1_id, c.t1_intt
61+
FROM combined c
62+
LEFT ASOF JOIN asof_tbl2 t2
63+
MATCH_CONDITION (t2.intt >= c.t1_intt)
64+
ON c.t1_id = t2.id;"""
65+
self.expected_error = "currently the only match_condition comparison supported by asof joins is 'leftcol >= rightcol'"
66+
67+
68+
class asof_test_illarg6(TstView):
69+
def __init__(self):
70+
# checked manually
71+
self.sql = """CREATE MATERIALIZED VIEW illarg6 AS SELECT
72+
t1.id,
73+
t2.intt AS t2_int
74+
FROM asof_tbl1 AS t1
75+
LEFT ASOF JOIN asof_tbl2 AS t2
76+
MATCH_CONDITION (t2.intt = (SELECT t2_sub.intt))
77+
ON t1.id = t2.id;"""
78+
self.expected_error = "table 't2_sub' not found"

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

Lines changed: 117 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,117 @@
1+
from tests.runtime_aggtest.aggtst_base import TstView
2+
3+
4+
class asof_test_select_subquery(TstView):
5+
def __init__(self):
6+
# Validated on DuckDB
7+
self.data = [
8+
{"id": 1, "filtered_intt": 10, "t2_intt": 5},
9+
{"id": 2, "filtered_intt": 15, "t2_intt": None},
10+
{"id": 3, "filtered_intt": 20, "t2_intt": None},
11+
{"id": 4, "filtered_intt": 25, "t2_intt": 12},
12+
]
13+
self.sql = """CREATE MATERIALIZED VIEW asof_test_select_subquery AS SELECT
14+
sub.id, sub.filtered_intt, t2.intt AS t2_intt
15+
FROM (
16+
SELECT id, intt AS filtered_intt
17+
FROM asof_tbl1
18+
WHERE intt IS NOT NULL AND id < 5
19+
) sub
20+
LEFT ASOF JOIN asof_tbl2 t2
21+
MATCH_CONDITION (sub.filtered_intt >= t2.intt)
22+
ON sub.id = t2.id;"""
23+
24+
25+
class asof_test_aggregate_subquery(TstView):
26+
def __init__(self):
27+
# Validated on DuckDB
28+
self.data = [
29+
{"id": 1, "avg_intt": 10, "t2_intt": 5},
30+
{"id": 2, "avg_intt": 15, "t2_intt": None},
31+
{"id": 3, "avg_intt": 20, "t2_intt": None},
32+
{"id": 4, "avg_intt": 25, "t2_intt": 12},
33+
]
34+
self.sql = """CREATE MATERIALIZED VIEW asof_test_aggregate_subquery AS SELECT
35+
sub.id, sub.avg_intt, t2.intt AS t2_intt
36+
FROM (
37+
SELECT id, AVG(intt) AS avg_intt
38+
FROM asof_tbl1
39+
WHERE intt IS NOT NULL
40+
GROUP BY id
41+
) sub
42+
LEFT ASOF JOIN asof_tbl2 t2
43+
MATCH_CONDITION (sub.avg_intt >= t2.intt)
44+
ON sub.id = t2.id;"""
45+
46+
47+
class asof_test_with_cte(TstView):
48+
def __init__(self):
49+
# Validated on DuckDB
50+
self.data = [
51+
{"id": 1, "doubled": 20, "t2_intt": 5},
52+
{"id": 2, "doubled": 30, "t2_intt": 16},
53+
{"id": 3, "doubled": 40, "t2_intt": None},
54+
{"id": 4, "doubled": 50, "t2_intt": 12},
55+
]
56+
self.sql = """CREATE MATERIALIZED VIEW asof_test_with_cte AS
57+
WITH doubled_values AS (
58+
SELECT id, intt * 2 AS doubled
59+
FROM asof_tbl1
60+
WHERE intt IS NOT NULL
61+
)
62+
SELECT
63+
dv.id, dv.doubled, t2.intt AS t2_intt
64+
FROM doubled_values dv
65+
LEFT ASOF JOIN asof_tbl2 t2
66+
MATCH_CONDITION (dv.doubled >= t2.intt)
67+
ON dv.id = t2.id;"""
68+
69+
70+
class asof_test_union_subquery(TstView):
71+
def __init__(self):
72+
# Validated on DuckDB
73+
self.data = [
74+
{"id": 1, "combined_intt": 10, "source": "tbl1", "t2_intt": 5},
75+
{"id": 1, "combined_intt": 5, "source": "tbl3", "t2_intt": 5},
76+
{"id": 2, "combined_intt": 15, "source": "tbl1", "t2_intt": None},
77+
{"id": 2, "combined_intt": 16, "source": "tbl3", "t2_intt": 16},
78+
{"id": 3, "combined_intt": 20, "source": "tbl1", "t2_intt": None},
79+
{"id": 3, "combined_intt": 70, "source": "tbl3", "t2_intt": 70},
80+
{"id": 4, "combined_intt": 12, "source": "tbl3", "t2_intt": 12},
81+
{"id": 4, "combined_intt": 25, "source": "tbl1", "t2_intt": 12},
82+
]
83+
self.sql = """CREATE MATERIALIZED VIEW asof_test_union_subquery AS SELECT
84+
sub.id, sub.combined_intt, sub.source, t2.intt AS t2_intt
85+
FROM (
86+
SELECT id, intt AS combined_intt, 'tbl1' AS source
87+
FROM asof_tbl1
88+
WHERE intt IS NOT NULL
89+
UNION ALL
90+
SELECT id, intt AS combined_intt, 'tbl3' AS source
91+
FROM asof_tbl3
92+
WHERE id < 5
93+
) sub
94+
LEFT ASOF JOIN asof_tbl2 t2
95+
MATCH_CONDITION (sub.combined_intt >= t2.intt)
96+
ON sub.id = t2.id;"""
97+
98+
99+
class asof_test_window_function_subquery(TstView):
100+
def __init__(self):
101+
# Validated on DuckDB
102+
self.data = [
103+
{"id": 1, "intt": 10, "avg_intt": 17, "t2_intt": 5},
104+
{"id": 2, "intt": 15, "avg_intt": 17, "t2_intt": None},
105+
{"id": 3, "intt": 20, "avg_intt": 17, "t2_intt": None},
106+
{"id": 4, "intt": 25, "avg_intt": 17, "t2_intt": 12},
107+
]
108+
self.sql = """CREATE MATERIALIZED VIEW asof_test_window_function_subquery AS SELECT
109+
sub.id, sub.intt, sub.avg_intt, t2.intt AS t2_intt
110+
FROM (
111+
SELECT id, intt, AVG(intt) OVER () AS avg_intt
112+
FROM asof_tbl1
113+
WHERE intt IS NOT NULL
114+
) sub
115+
LEFT ASOF JOIN asof_tbl2 t2
116+
MATCH_CONDITION (sub.intt >= t2.intt)
117+
ON sub.id = t2.id;"""

0 commit comments

Comments
 (0)