This is an automated email from the ASF dual-hosted git repository.
mihaibudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new a60ad2019b [CALCITE-7874] RelDecorrelator drops the MATCH_CONDITION of
an ASOF JOIN
a60ad2019b is described below
commit a60ad2019b8d54a8fd2fb9bcb5794996e01fd020
Author: Mihai Budiu <[email protected]>
AuthorDate: Wed Oct 21 20:09:09 2026 -0700
[CALCITE-7874] RelDecorrelator drops the MATCH_CONDITION of an ASOF JOIN
Signed-off-by: Mihai Budiu <[email protected]>
---
.../apache/calcite/sql2rel/RelDecorrelator.java | 16 ++--
core/src/test/resources/sql/asof.iq | 99 ++++++++++++++++++++++
2 files changed, 110 insertions(+), 5 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
index fbf949ac18..5207db8566 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -37,6 +37,7 @@
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.Aggregate;
import org.apache.calcite.rel.core.AggregateCall;
+import org.apache.calcite.rel.core.AsofJoin;
import org.apache.calcite.rel.core.Correlate;
import org.apache.calcite.rel.core.CorrelationId;
import org.apache.calcite.rel.core.Filter;
@@ -2095,11 +2096,16 @@ private static boolean isWidening(RelDataType type,
RelDataType type1) {
? relBuilder.literal(true)
: RexUtil.composeConjunction(relBuilder.getRexBuilder(),
joinConditions);
- RelNode newJoin = relBuilder
- .push(newLeftFrame.r)
- .push(newRightFrame.r)
- .join(rel.getJoinType(), finalCondition, ImmutableSet.of())
- .build();
+ relBuilder.push(newLeftFrame.r).push(newRightFrame.r);
+ if (rel instanceof AsofJoin) {
+ final RexNode matchCondition =
+ decorrelateExpr(castNonNull(currentRel), map, cm,
+ ((AsofJoin) rel).getMatchCondition());
+ relBuilder.asofJoin(rel.getJoinType(), finalCondition, matchCondition);
+ } else {
+ relBuilder.join(rel.getJoinType(), finalCondition, ImmutableSet.of());
+ }
+ RelNode newJoin = relBuilder.build();
// 4. Handle Full Join Projections (Coalesce)
NavigableMap<CorDef, Integer> corDefOutputs = new
TreeMap<>(newLeftFrame.corDefOutputs);
diff --git a/core/src/test/resources/sql/asof.iq
b/core/src/test/resources/sql/asof.iq
index abefdf7746..411890966a 100644
--- a/core/src/test/resources/sql/asof.iq
+++ b/core/src/test/resources/sql/asof.iq
@@ -268,4 +268,103 @@ ON t1.common_join_attribute = t3.common_join_attribute
AND t1.join_key_a2 = t3.j
!ok
+# [CALCITE-7874] RelDecorrelator drops the MATCH_CONDITION of an ASOF JOIN
+# The results of the following queries were validated using Postgres,
+# where ASOF JOIN is written as JOIN LATERAL with ORDER BY t2.t DESC LIMIT 1
+SELECT *
+FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2,
3), (3, 4)) AS t1(k, t)
+ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k, t)
+MATCH_CONDITION t2.t < t1.t
+ON t1.k = t2.k
+WHERE EXISTS (SELECT 1 FROM (VALUES 1) AS t3(k) WHERE t3.k = t1.k);
++---+---+----+----+
+| K | T | K0 | T0 |
++---+---+----+----+
+| 1 | 3 | 1 | 2 |
+| 1 | 4 | 1 | 3 |
++---+---+----+----+
+(2 rows)
+
+!ok
+
+SELECT *
+FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2,
3), (3, 4)) AS t1(k, t)
+LEFT ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k, t)
+MATCH_CONDITION t2.t < t1.t
+ON t1.k = t2.k
+WHERE EXISTS (SELECT 1 FROM (VALUES 1) AS t3(k) WHERE t3.k = t1.k);
++---+---+----+----+
+| K | T | K0 | T0 |
++---+---+----+----+
+| 1 | 0 | | |
+| 1 | 1 | | |
+| 1 | 2 | | |
+| 1 | 3 | 1 | 2 |
+| 1 | 4 | 1 | 3 |
+| 1 | | | |
++---+---+----+----+
+(6 rows)
+
+!ok
+
+# The ASOF JOIN is inside a sub-query that is correlated through its left input
+WITH t1(k, t) AS (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3),
(1, 4), (2, 3), (3, 4)),
+t2(k, t) AS (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0))
+SELECT o.k
+FROM (VALUES 1, 2, 3) AS o(k)
+WHERE EXISTS (
+ SELECT 1 FROM t1 ASOF JOIN t2
+ MATCH_CONDITION t2.t < t1.t
+ ON t1.k = t2.k
+ WHERE t1.k = o.k);
++---+
+| K |
++---+
+| 1 |
+| 2 |
++---+
+(2 rows)
+
+!ok
+
+# As the previous query, but correlated through the right input
+WITH t1(k, t) AS (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3),
(1, 4), (2, 3), (3, 4)),
+t2(k, t) AS (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0))
+SELECT o.k
+FROM (VALUES 1, 2, 3) AS o(k)
+WHERE EXISTS (
+ SELECT 1 FROM t1 ASOF JOIN t2
+ MATCH_CONDITION t2.t < t1.t
+ ON t1.k = t2.k
+ WHERE t2.k = o.k);
++---+
+| K |
++---+
+| 1 |
+| 2 |
++---+
+(2 rows)
+
+!ok
+
+# The ASOF JOIN is inside a correlated scalar sub-query
+WITH t1(k, t) AS (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3),
(1, 4), (2, 3), (3, 4)),
+t2(k, t) AS (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0))
+SELECT o.k,
+ (SELECT COUNT(*) FROM t1 ASOF JOIN t2
+ MATCH_CONDITION t2.t < t1.t
+ ON t1.k = t2.k
+ WHERE t1.k = o.k) AS c
+FROM (VALUES 1, 2, 3) AS o(k);
++---+---+
+| K | C |
++---+---+
+| 1 | 2 |
+| 2 | 1 |
+| 3 | 0 |
++---+---+
+(3 rows)
+
+!ok
+
# End asof.iq
