This is an automated email from the ASF dual-hosted git repository.
rubenada 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 dd119578d7 [CALCITE-7904] SingleValuesOptimizationRules give wrong
results for ASOF and SEMI joins and fail for LEFT ASOF join
dd119578d7 is described below
commit dd119578d7492b976aac215a3830968a1ebbaaab
Author: Ruben Quesada Lopez <[email protected]>
AuthorDate: Thu Oct 29 15:58:45 2026 +0000
[CALCITE-7904] SingleValuesOptimizationRules give wrong results for ASOF
and SEMI joins and fail for LEFT ASOF join
---
.../adapter/enumerable/EnumerableAsofJoin.java | 5 +-
.../rel/rules/SingleValuesOptimizationRules.java | 19 ++++++-
core/src/test/resources/sql/asof.iq | 60 ++++++++++++++++++++++
core/src/test/resources/sql/misc.iq | 55 ++++++++++++++++++++
4 files changed, 136 insertions(+), 3 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableAsofJoin.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableAsofJoin.java
index d7cb7b4cdb..82f05c52f6 100644
---
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableAsofJoin.java
+++
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableAsofJoin.java
@@ -38,6 +38,7 @@
import org.apache.calcite.rex.RexCall;
import org.apache.calcite.rex.RexInputRef;
import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.util.BuiltInMethod;
import org.apache.calcite.util.Pair;
@@ -166,8 +167,8 @@ private int getTimestampFieldIndex(RexCall call) {
int leftFieldCount = left.getRowType().getFieldCount();
List<RexNode> operands = call.getOperands();
assert operands.size() == 2;
- RexNode compareLeft = operands.get(0);
- RexNode compareRight = operands.get(1);
+ RexNode compareLeft = RexUtil.removeCast(operands.get(0));
+ RexNode compareRight = RexUtil.removeCast(operands.get(1));
assert compareLeft instanceof RexInputRef;
assert compareRight instanceof RexInputRef;
RexInputRef leftInputRef = (RexInputRef) compareLeft;
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/SingleValuesOptimizationRules.java
b/core/src/main/java/org/apache/calcite/rel/rules/SingleValuesOptimizationRules.java
index d0e53be44d..a23eb84039 100644
---
a/core/src/main/java/org/apache/calcite/rel/rules/SingleValuesOptimizationRules.java
+++
b/core/src/main/java/org/apache/calcite/rel/rules/SingleValuesOptimizationRules.java
@@ -20,6 +20,7 @@
import org.apache.calcite.plan.RelOptRuleCall;
import org.apache.calcite.plan.RelRule;
import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.AsofJoin;
import org.apache.calcite.rel.core.Join;
import org.apache.calcite.rel.core.JoinRelType;
import org.apache.calcite.rel.core.Project;
@@ -283,12 +284,28 @@ static Predicate<Join> isJoinTransformable(boolean
isLeft) {
jn -> jn.getJoinType() == JoinRelType.ANTI
|| jn.getJoinType() == JoinRelType.FULL;
+ // [LEFT] ASOF joins enforce a MATCH_CONDITION predicate that is carried
outside join's
+ // condition (see AsofJoin#getMatchCondition()). This transformation
would silently delete
+ // that predicate, leading to wrong results. The instanceof check is
deliberately redundant
+ // with the join-type check so that potential Join subclasses carrying
extra semantic state
+ // are never silently rewritten.
+ Predicate<Join> isAsofJoin =
+ jn -> jn instanceof AsofJoin
+ || jn.getJoinType() == JoinRelType.ASOF
+ || jn.getJoinType() == JoinRelType.LEFT_ASOF;
+
if (isLeft) {
return jn -> !(jn.getJoinType() == JoinRelType.LEFT
|| jn.getJoinType() == JoinRelType.LEFT_MARK
+ // SEMI is not transformable when the single-row Values is the
left: the join emits
+ // at most one row, but the rewritten plan would emit one row per
matching right row
+ || jn.getJoinType() == JoinRelType.SEMI
+ || isAsofJoin.test(jn)
|| isFullOrAntiJoin.test(jn));
} else {
- return jn -> !(jn.getJoinType() == JoinRelType.RIGHT ||
isFullOrAntiJoin.test(jn));
+ return jn -> !(jn.getJoinType() == JoinRelType.RIGHT
+ || isAsofJoin.test(jn)
+ || isFullOrAntiJoin.test(jn));
}
}
diff --git a/core/src/test/resources/sql/asof.iq
b/core/src/test/resources/sql/asof.iq
index 411890966a..65a825faf3 100644
--- a/core/src/test/resources/sql/asof.iq
+++ b/core/src/test/resources/sql/asof.iq
@@ -367,4 +367,64 @@ FROM (VALUES 1, 2, 3) AS o(k);
!ok
+# Test cases for https://issues.apache.org/jira/browse/CALCITE-7904
+# SingleValuesOptimizationRules give wrong results for ASOF and SEMI joins and
fail for LEFT ASOF join
+SELECT *
+FROM (VALUES (1, 3), (1, 5), (2, 5)) AS t1(k, t)
+ASOF JOIN (VALUES (1, 4)) AS t2(k, t)
+MATCH_CONDITION t2.t < t1.t
+ON t1.k = t2.k;
++---+---+----+----+
+| K | T | K0 | T0 |
++---+---+----+----+
+| 1 | 5 | 1 | 4 |
++---+---+----+----+
+(1 row)
+
+!ok
+
+SELECT *
+FROM (VALUES (1, 4)) AS t1(k, t)
+ASOF JOIN (VALUES (1, 2), (1, 3), (1, 5), (2, 1)) AS t2(k, t)
+MATCH_CONDITION t2.t < t1.t
+ON t1.k = t2.k;
++---+---+----+----+
+| K | T | K0 | T0 |
++---+---+----+----+
+| 1 | 4 | 1 | 3 |
++---+---+----+----+
+(1 row)
+
+!ok
+
+SELECT *
+FROM (VALUES (1, 3), (1, 5), (2, 5)) AS t1(k, t)
+LEFT ASOF JOIN (VALUES (1, 4)) AS t2(k, t)
+MATCH_CONDITION t2.t < t1.t
+ON t1.k = t2.k;
++---+---+----+----+
+| K | T | K0 | T0 |
++---+---+----+----+
+| 1 | 3 | | |
+| 1 | 5 | 1 | 4 |
+| 2 | 5 | | |
++---+---+----+----+
+(3 rows)
+
+!ok
+
+SELECT *
+FROM (VALUES (1, 1)) AS t1(k, t)
+LEFT ASOF JOIN (VALUES (1, 2), (1, 3), (1, 5), (2, 1)) AS t2(k, t)
+MATCH_CONDITION t2.t < t1.t
+ON t1.k = t2.k;
++---+---+----+----+
+| K | T | K0 | T0 |
++---+---+----+----+
+| 1 | 1 | | |
++---+---+----+----+
+(1 row)
+
+!ok
+
# End asof.iq
diff --git a/core/src/test/resources/sql/misc.iq
b/core/src/test/resources/sql/misc.iq
index e26ce01159..669ef49110 100644
--- a/core/src/test/resources/sql/misc.iq
+++ b/core/src/test/resources/sql/misc.iq
@@ -888,6 +888,61 @@ EnumerableNestedLoopJoin(condition=[true], joinType=[anti])
!plan
!}
+# Test case for https://issues.apache.org/jira/browse/CALCITE-7904
+# SingleValuesOptimizationRules give wrong results for ASOF and SEMI joins and
fail for LEFT ASOF join
+# (SEMI join with a single-row VALUES as its left input must return at most
one row)
+select * from (values (10)) as t(x)
+where exists (select 1 from "hr"."emps");
++----+
+| X |
++----+
+| 10 |
++----+
+(1 row)
+
+!ok
+
+# As above, correlated
+select * from (values (10)) as t(x)
+where exists (select 1 from "hr"."emps" as e where e."deptno" = t.x);
++----+
+| X |
++----+
+| 10 |
++----+
+(1 row)
+
+!ok
+
+# SEMI join with a single-row VALUES as its right input
+select "deptno" from "hr"."depts"
+where exists (select 1 from (values (1)) as t(x));
++--------+
+| deptno |
++--------+
+| 10 |
+| 30 |
+| 40 |
++--------+
+(3 rows)
+
+!ok
+
+# As above, correlated
+select "empid", "name" from "hr"."emps"
+where exists (select 1 from (values (10)) as t(x) where t.x = "emps"."deptno")
+order by "empid";
++-------+-----------+
+| empid | name |
++-------+-----------+
+| 100 | Bill |
+| 110 | Theodore |
+| 150 | Sebastian |
++-------+-----------+
+(3 rows)
+
+!ok
+
# EXISTS
select * from "hr"."emps"
where exists (
