py: add tests for AS OF joins with different ON clauses #5335

rivudhk · 2025-12-24T16:32:42Z

Copilot

Pull request overview

This PR adds comprehensive test coverage for AS OF joins with various ON clause configurations, addressing issue #4901. The tests validate different scenarios including multiple joins, complex conditions, aggregations, and error handling.

Key Changes:

Added new test file with 7 test classes covering different AS OF join ON clause patterns
Added validation for illegal ON clause usage (non-equality comparisons)
Integrated new tests into the test suite via main.py import

Reviewed changes

Copilot reviewed 3 out of 3 changed files in this pull request and generated 5 comments.

File	Description
python/tests/runtime_aggtest/asof_tests/test_asof_multi_on.py	New test file containing 7 test classes for AS OF joins with various ON clause scenarios including multi-joins, complex types, aggregations, and computed values
python/tests/runtime_aggtest/asof_tests/test_asof_illarg.py	Added test case for illegal ON clause condition (inequality comparison) to validate error handling
python/tests/runtime_aggtest/asof_tests/main.py	Imported new test module to include it in test execution

Copilot · 2025-12-24T16:34:05Z

python/tests/runtime_aggtest/asof_tests/test_asof_multi_on.py

+from tests.runtime_aggtest.aggtst_base import TstView
+
+
+class asof_multi_asof(TstView):


Class name 'asof_multi_asof' contains redundant 'asof' repetition. Consider renaming to 'asof_multi_join' or 'multi_asof_join' for clarity.

Suggested change

class asof_multi_asof(TstView):

class asof_multi_join(TstView):

Copilot · 2025-12-24T16:34:05Z

python/tests/runtime_aggtest/asof_tests/test_asof_multi_on.py

+            {"id": 4, "t1_intt": 25, "t2_intt": None},
+            {"id": 5, "t1_intt": None, "t2_intt": None},
+        ]
+        self.sql = """CREATE MATERIALIZED VIEW asof_multiasof AS SELECT


View name 'asof_multiasof' should match the class name pattern or use underscores consistently. Consider 'asof_multi_asof' to match the class naming convention.

Suggested change

self.sql = """CREATE MATERIALIZED VIEW asof_multiasof AS SELECT

self.sql = """CREATE MATERIALIZED VIEW asof_multi_asof AS SELECT

Copilot · 2025-12-24T16:34:05Z

python/tests/runtime_aggtest/asof_tests/test_asof_multi_on.py

+            {"id": 1, "t1_intt": 10, "intervall": 20},
+            {"id": 2, "t1_intt": 15, "intervall": 2},
+            {"id": 3, "t1_intt": 20, "intervall": -1},
+            {"id": 4, "t1_intt": 25, "intervall": -1},
+            {"id": 5, "t1_intt": None, "intervall": None},
+        ]
+        self.sql = """CREATE MATERIALIZED VIEW asof_computed_int_on AS
+                      WITH joined AS (
+                        SELECT t1.id,
+                               t1.intt AS t1_intt,
+                               t2.intt AS t2_intt,
+                               TIMESTAMPDIFF(YEAR, t1.tmestmp, t2.tmestmp) AS intervall
+                        FROM asof_tbl1 t1
+                        JOIN asof_tbl2 t2
+                        ON t1.id = t2.id
+                      )
+                      SELECT j.id, j.t1_intt, intervall
+                      FROM joined j
+                      LEFT ASOF JOIN asof_tbl3 t3
+                      MATCH_CONDITION (j.intervall >= t3.intt)


Corrected spelling of 'intervall' to 'interval'.

Suggested change

{"id": 1, "t1_intt": 10, "intervall": 20},

{"id": 2, "t1_intt": 15, "intervall": 2},

{"id": 3, "t1_intt": 20, "intervall": -1},

{"id": 4, "t1_intt": 25, "intervall": -1},

{"id": 5, "t1_intt": None, "intervall": None},

]

self.sql = """CREATE MATERIALIZED VIEW asof_computed_int_on AS

WITH joined AS (

SELECT t1.id,

t1.intt AS t1_intt,

t2.intt AS t2_intt,

TIMESTAMPDIFF(YEAR, t1.tmestmp, t2.tmestmp) AS intervall

FROM asof_tbl1 t1

JOIN asof_tbl2 t2

ON t1.id = t2.id

)

SELECT j.id, j.t1_intt, intervall

FROM joined j

LEFT ASOF JOIN asof_tbl3 t3

MATCH_CONDITION (j.intervall >= t3.intt)

{"id": 1, "t1_intt": 10, "interval": 20},

{"id": 2, "t1_intt": 15, "interval": 2},

{"id": 3, "t1_intt": 20, "interval": -1},

{"id": 4, "t1_intt": 25, "interval": -1},

{"id": 5, "t1_intt": None, "interval": None},

]

self.sql = """CREATE MATERIALIZED VIEW asof_computed_int_on AS

WITH joined AS (

SELECT t1.id,

t1.intt AS t1_intt,

t2.intt AS t2_intt,

TIMESTAMPDIFF(YEAR, t1.tmestmp, t2.tmestmp) AS interval

FROM asof_tbl1 t1

JOIN asof_tbl2 t2

ON t1.id = t2.id

)

SELECT j.id, j.t1_intt, interval

FROM joined j

LEFT ASOF JOIN asof_tbl3 t3

MATCH_CONDITION (j.interval >= t3.intt)

Copilot · 2025-12-24T16:34:06Z

python/tests/runtime_aggtest/asof_tests/test_asof_multi_on.py

+            {"id": 1, "t1_intt": 10, "intervall": 20},
+            {"id": 2, "t1_intt": 15, "intervall": 2},
+            {"id": 3, "t1_intt": 20, "intervall": -1},
+            {"id": 4, "t1_intt": 25, "intervall": -1},
+            {"id": 5, "t1_intt": None, "intervall": None},
+        ]
+        self.sql = """CREATE MATERIALIZED VIEW asof_computed_int_on AS
+                      WITH joined AS (
+                        SELECT t1.id,
+                               t1.intt AS t1_intt,
+                               t2.intt AS t2_intt,
+                               TIMESTAMPDIFF(YEAR, t1.tmestmp, t2.tmestmp) AS intervall
+                        FROM asof_tbl1 t1
+                        JOIN asof_tbl2 t2
+                        ON t1.id = t2.id
+                      )
+                      SELECT j.id, j.t1_intt, intervall
+                      FROM joined j
+                      LEFT ASOF JOIN asof_tbl3 t3
+                      MATCH_CONDITION (j.intervall >= t3.intt)


Field name 'intervall' is misspelled. Should be 'interval' to match standard SQL terminology.

Suggested change

{"id": 1, "t1_intt": 10, "intervall": 20},

{"id": 2, "t1_intt": 15, "intervall": 2},

{"id": 3, "t1_intt": 20, "intervall": -1},

{"id": 4, "t1_intt": 25, "intervall": -1},

{"id": 5, "t1_intt": None, "intervall": None},

]

self.sql = """CREATE MATERIALIZED VIEW asof_computed_int_on AS

WITH joined AS (

SELECT t1.id,

t1.intt AS t1_intt,

t2.intt AS t2_intt,

TIMESTAMPDIFF(YEAR, t1.tmestmp, t2.tmestmp) AS intervall

FROM asof_tbl1 t1

JOIN asof_tbl2 t2

ON t1.id = t2.id

)

SELECT j.id, j.t1_intt, intervall

FROM joined j

LEFT ASOF JOIN asof_tbl3 t3

MATCH_CONDITION (j.intervall >= t3.intt)

{"id": 1, "t1_intt": 10, "interval": 20},

{"id": 2, "t1_intt": 15, "interval": 2},

{"id": 3, "t1_intt": 20, "interval": -1},

{"id": 4, "t1_intt": 25, "interval": -1},

{"id": 5, "t1_intt": None, "interval": None},

]

self.sql = """CREATE MATERIALIZED VIEW asof_computed_int_on AS

WITH joined AS (

SELECT t1.id,

t1.intt AS t1_intt,

t2.intt AS t2_intt,

TIMESTAMPDIFF(YEAR, t1.tmestmp, t2.tmestmp) AS interval

FROM asof_tbl1 t1

JOIN asof_tbl2 t2

ON t1.id = t2.id

)

SELECT j.id, j.t1_intt, interval

FROM joined j

LEFT ASOF JOIN asof_tbl3 t3

MATCH_CONDITION (j.interval >= t3.intt)

Copilot · 2025-12-24T16:34:06Z

python/tests/runtime_aggtest/asof_tests/test_asof_multi_on.py

+            {"id": 1, "t1_intt": 10, "intervall": 20},
+            {"id": 2, "t1_intt": 15, "intervall": 2},
+            {"id": 3, "t1_intt": 20, "intervall": -1},
+            {"id": 4, "t1_intt": 25, "intervall": -1},
+            {"id": 5, "t1_intt": None, "intervall": None},
+        ]
+        self.sql = """CREATE MATERIALIZED VIEW asof_computed_int_on AS
+                      WITH joined AS (
+                        SELECT t1.id,
+                               t1.intt AS t1_intt,
+                               t2.intt AS t2_intt,
+                               TIMESTAMPDIFF(YEAR, t1.tmestmp, t2.tmestmp) AS intervall
+                        FROM asof_tbl1 t1
+                        JOIN asof_tbl2 t2
+                        ON t1.id = t2.id
+                      )
+                      SELECT j.id, j.t1_intt, intervall
+                      FROM joined j
+                      LEFT ASOF JOIN asof_tbl3 t3
+                      MATCH_CONDITION (j.intervall >= t3.intt)


Column reference 'intervall' should be 'interval' to match corrected naming.

Suggested change

{"id": 1, "t1_intt": 10, "intervall": 20},

{"id": 2, "t1_intt": 15, "intervall": 2},

{"id": 3, "t1_intt": 20, "intervall": -1},

{"id": 4, "t1_intt": 25, "intervall": -1},

{"id": 5, "t1_intt": None, "intervall": None},

]

self.sql = """CREATE MATERIALIZED VIEW asof_computed_int_on AS

WITH joined AS (

SELECT t1.id,

t1.intt AS t1_intt,

t2.intt AS t2_intt,

TIMESTAMPDIFF(YEAR, t1.tmestmp, t2.tmestmp) AS intervall

FROM asof_tbl1 t1

JOIN asof_tbl2 t2

ON t1.id = t2.id

)

SELECT j.id, j.t1_intt, intervall

FROM joined j

LEFT ASOF JOIN asof_tbl3 t3

MATCH_CONDITION (j.intervall >= t3.intt)

{"id": 1, "t1_intt": 10, "interval": 20},

{"id": 2, "t1_intt": 15, "interval": 2},

{"id": 3, "t1_intt": 20, "interval": -1},

{"id": 4, "t1_intt": 25, "interval": -1},

{"id": 5, "t1_intt": None, "interval": None},

]

self.sql = """CREATE MATERIALIZED VIEW asof_computed_int_on AS

WITH joined AS (

SELECT t1.id,

t1.intt AS t1_intt,

t2.intt AS t2_intt,

TIMESTAMPDIFF(YEAR, t1.tmestmp, t2.tmestmp) AS interval

FROM asof_tbl1 t1

JOIN asof_tbl2 t2

ON t1.id = t2.id

)

SELECT j.id, j.t1_intt, interval

FROM joined j

LEFT ASOF JOIN asof_tbl3 t3

MATCH_CONDITION (j.interval >= t3.intt)

Signed-off-by: rivudhk <[email protected]>

Copilot AI review requested due to automatic review settings December 24, 2025 16:32

Copilot AI reviewed Dec 24, 2025

View reviewed changes

py: add tests for AS OF joins with different ON clauses

cd389e8

Signed-off-by: rivudhk <[email protected]>

rivudhk force-pushed the as-of-new branch from a078b2d to cd389e8 Compare December 24, 2025 17:38

rivudhk requested a review from mihaibudiu December 24, 2025 17:38

mihaibudiu approved these changes Dec 24, 2025

View reviewed changes

mihaibudiu added this pull request to the merge queue Dec 24, 2025

Merged via the queue into main with commit 489eae0 Dec 24, 2025
1 check passed

mihaibudiu deleted the as-of-new branch December 24, 2025 19:38

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

py: add tests for AS OF joins with different ON clauses #5335

py: add tests for AS OF joins with different ON clauses #5335

Uh oh!

rivudhk commented Dec 24, 2025

Uh oh!

Copilot AI left a comment

Uh oh!

Copilot AI Dec 24, 2025

Uh oh!

Copilot AI Dec 24, 2025

Uh oh!

Copilot AI Dec 24, 2025

Uh oh!

Copilot AI Dec 24, 2025

Uh oh!

Copilot AI Dec 24, 2025

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

		from tests.runtime_aggtest.aggtst_base import TstView


		class asof_multi_asof(TstView):

	class asof_multi_asof(TstView):
	class asof_multi_join(TstView):

	self.sql = """CREATE MATERIALIZED VIEW asof_multiasof AS SELECT
	self.sql = """CREATE MATERIALIZED VIEW asof_multi_asof AS SELECT

py: add tests for AS OF joins with different ON clauses #5335

py: add tests for AS OF joins with different ON clauses #5335

Uh oh!

Conversation

rivudhk commented Dec 24, 2025

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Pull request overview

Reviewed changes

Uh oh!

Copilot AI Dec 24, 2025

Choose a reason for hiding this comment

Uh oh!

Copilot AI Dec 24, 2025

Choose a reason for hiding this comment

Uh oh!

Copilot AI Dec 24, 2025

Choose a reason for hiding this comment

Uh oh!

Copilot AI Dec 24, 2025

Choose a reason for hiding this comment

Uh oh!

Copilot AI Dec 24, 2025

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants