This is an automated email from the ASF dual-hosted git repository.
xiedeyantu 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 0d5bdc8294 [CALCITE-7638] SetOpToFilterRule MINUS drops rows when
right-side filters evaluate to UNKNOWN
0d5bdc8294 is described below
commit 0d5bdc8294fd7bc35ca0264afe64ac18f6201e08
Author: Zhen Chen <[email protected]>
AuthorDate: Sun Jul 5 08:59:52 2026 +0800
[CALCITE-7638] SetOpToFilterRule MINUS drops rows when right-side filters
evaluate to UNKNOWN
Signed-off-by: xiedeyantu <[email protected]>
---
.../calcite/rel/rules/SetOpToFilterRule.java | 18 ++++++++------
.../org/apache/calcite/test/RelOptRulesTest.java | 13 ++++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 27 +++++++++++++++++++++
core/src/test/resources/sql/hep.iq | 28 ++++++++++++++++++++++
4 files changed, 79 insertions(+), 7 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/SetOpToFilterRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/SetOpToFilterRule.java
index e00e3e50f6..848cc6c797 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/SetOpToFilterRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/SetOpToFilterRule.java
@@ -26,6 +26,7 @@
import org.apache.calcite.rel.core.Union;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexUtil;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.tools.RelBuilder;
import org.apache.calcite.util.Pair;
@@ -96,7 +97,7 @@
* is rewritten to
*
* SELECT DISTINCT mgr, comm FROM emp
- * WHERE mgr = 12 AND NOT(comm = 5)
+ * WHERE mgr = 12 AND (comm = 5) IS NOT TRUE
* </pre></blockquote>
*/
@Value.Enclosing
@@ -214,17 +215,19 @@ private static RelBuilder buildSetOp(RelBuilder builder,
int count, RelNode setO
/**
* Creates a combined condition where the first condition
- * is kept as-is and all subsequent conditions are negated,
+ * is kept as-is and all subsequent conditions are IS NOT TRUE,
* then joined with AND operators.
*
* <p>For example, given conditions [cond1, cond2, cond3],
- * this constructs (cond1 AND NOT(cond2) AND NOT(cond3)).
+ * this constructs (cond1 AND cond2 IS NOT TRUE AND cond3 IS NOT TRUE).
+ * The right-side filter matches only TRUE rows, so FALSE and UNKNOWN
+ * both represent rows that are not present in that MINUS input.
*/
- private static RexNode andFirstNotRest(RelBuilder builder, List<RexNode>
conds) {
+ private static RexNode andFirstIsNotTrueRest(RelBuilder builder,
List<RexNode> conds) {
List<RexNode> allConds = new ArrayList<>();
allConds.add(conds.get(0));
for (int i = 1; i < conds.size(); i++) {
- allConds.add(builder.not(conds.get(i)));
+ allConds.add(builder.call(SqlStdOperatorTable.IS_NOT_TRUE,
conds.get(i)));
}
return builder.and(allConds);
}
@@ -233,7 +236,8 @@ private static RexNode andFirstNotRest(RelBuilder builder,
List<RexNode> conds)
* Combines conditions according to set operation:
* UNION: OR combination
* INTERSECT: AND combination
- * MINUS: Special handling where first source uses AND-NOT combination.
+ * MINUS: Special handling where first source keeps rows that match the
+ * first input and do not match any subsequent input.
*/
private static RexNode combineConditions(RelBuilder builder, List<RexNode>
conds,
SetOp setOp, boolean isFirstSource) {
@@ -243,7 +247,7 @@ private static RexNode combineConditions(RelBuilder
builder, List<RexNode> conds
return builder.and(conds);
} else if (setOp instanceof Minus) {
return isFirstSource
- ? andFirstNotRest(builder, conds)
+ ? andFirstIsNotTrueRest(builder, conds)
: builder.or(conds);
}
// unreachable
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index 4fb62fb346..d56bde9808 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -11438,6 +11438,19 @@ private void
checkLoptOptimizeJoinRule(LoptOptimizeJoinRule rule) {
.check();
}
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7638";>[CALCITE-7638]
+ * SetOpToFilterRule MINUS drops rows when right-side filters evaluate to
UNKNOWN</a>. */
+ @Test void testMinusToFilterRuleWithNullableFilter() {
+ final String sql = "SELECT mgr, comm FROM empnullables WHERE mgr = 12\n"
+ + "EXCEPT\n"
+ + "SELECT mgr, comm FROM empnullables WHERE comm = 5\n";
+ sql(sql)
+ .withPreRule(CoreRules.PROJECT_FILTER_TRANSPOSE)
+ .withRule(CoreRules.MINUS_FILTER_TO_FILTER)
+ .check();
+ }
+
/** Test case of
* <a
href="https://issues.apache.org/jira/browse/CALCITE-6973";>[CALCITE-6973]
* Add rule for convert Minus to Filter</a>. */
diff --git
a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index 7178366a67..cdf6956ae4 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -10935,6 +10935,33 @@ LogicalMinus(all=[false])
LogicalFilter(condition=[>($0, 8)])
LogicalProject(DEPTNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMinusToFilterRuleWithNullableFilter">
+ <Resource name="sql">
+ <![CDATA[SELECT mgr, comm FROM empnullables WHERE mgr = 12
+EXCEPT
+SELECT mgr, comm FROM empnullables WHERE comm = 5
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalMinus(all=[false])
+ LogicalFilter(condition=[=($0, 12)])
+ LogicalProject(MGR=[$3], COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalFilter(condition=[=($1, 5)])
+ LogicalProject(MGR=[$3], COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalAggregate(group=[{0, 1}])
+ LogicalFilter(condition=[AND(=($0, 12), IS NOT TRUE(=($1, 5)))])
+ LogicalProject(MGR=[$3], COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/hep.iq
b/core/src/test/resources/sql/hep.iq
index 7fbcbc7f29..18cdaf2722 100644
--- a/core/src/test/resources/sql/hep.iq
+++ b/core/src/test/resources/sql/hep.iq
@@ -77,6 +77,34 @@ EnumerableAggregate(group=[{0, 1}])
!plan
!set hep-rules original
+# [CALCITE-7638] SetOpToFilterRule MINUS drops rows when right-side filters
evaluate to UNKNOWN.
+!set hep-rules "
++CoreRules.PROJECT_FILTER_TRANSPOSE,
++CoreRules.MINUS_FILTER_TO_FILTER"
+
+SELECT mgr, comm FROM (
+ SELECT mgr, comm FROM emp WHERE mgr = 7698
+ EXCEPT
+ SELECT mgr, comm FROM emp WHERE comm = 500
+) ORDER BY comm NULLS LAST;
++------+---------+
+| MGR | COMM |
++------+---------+
+| 7698 | 0.00 |
+| 7698 | 300.00 |
+| 7698 | 1400.00 |
+| 7698 | |
++------+---------+
+(4 rows)
+
+!ok
+EnumerableSort(sort0=[$1], dir0=[ASC])
+ EnumerableAggregate(group=[{0, 1}])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t3):INTEGER],
expr#9=[7698], expr#10=[=($t8, $t9)], expr#11=[CAST($t6):DECIMAL(12, 2)],
expr#12=[500.00:DECIMAL(12, 2)], expr#13=[=($t11, $t12)], expr#14=[IS NOT
TRUE($t13)], expr#15=[AND($t10, $t14)], MGR=[$t3], COMM=[$t6],
$condition=[$t15])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+!set hep-rules original
+
# Testing with the planner-rules shows that due to cost-based selection issues,
# the planner fails to choose a plan that includes the Aggregate operator.
!set planner-rules "
