This is an automated email from the ASF dual-hosted git repository.
rubenql pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new 7f5b313 [CALCITE-3198] Enhance RexSimplify to handle (x<>a or x<>b)
7f5b313 is described below
commit 7f5b313bbc0f98379c1b03a3fb08f296143d64c2
Author: rubenada <[email protected]>
AuthorDate: Wed Jul 17 12:16:08 2019 +0200
[CALCITE-3198] Enhance RexSimplify to handle (x<>a or x<>b)
---
.../java/org/apache/calcite/rex/RexSimplify.java | 50 ++++++++++
.../org/apache/calcite/test/RelOptRulesTest.java | 87 +++++++++++++++-
.../org/apache/calcite/test/RexProgramTest.java | 48 +++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 111 +++++++++++++++++++++
4 files changed, 293 insertions(+), 3 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
index 07f0846..3813087 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
@@ -54,6 +54,7 @@ import java.util.Objects;
import java.util.Set;
import static org.apache.calcite.rex.RexUnknownAs.FALSE;
+import static org.apache.calcite.rex.RexUnknownAs.TRUE;
import static org.apache.calcite.rex.RexUnknownAs.UNKNOWN;
/**
@@ -1644,6 +1645,12 @@ public class RexSimplify {
/** Simplifies a list of terms and combines them into an OR.
* Modifies the list in place. */
private RexNode simplifyOrs(List<RexNode> terms, RexUnknownAs unknownAs) {
+ // CALCITE-3198 Auxiliary map to simplify cases like:
+ // X <> A OR X <> B => X IS NOT NULL or NULL
+ // The map key will be the 'X'; and the value the first call 'X<>A' that
is found,
+ // or 'X IS NOT NULL' if a simplification takes place (because another
'X<>B' is found)
+ final Map<RexNode, RexNode> notEqualsComparisonMap = new HashMap<>();
+ final RexLiteral trueLiteral = rexBuilder.makeLiteral(true);
for (int i = 0; i < terms.size(); i++) {
final RexNode term = terms.get(i);
switch (term.getKind()) {
@@ -1653,6 +1660,8 @@ public class RexSimplify {
terms.remove(i);
--i;
continue;
+ } else if (unknownAs == TRUE) {
+ return trueLiteral;
}
} else {
if (RexLiteral.booleanValue(term)) {
@@ -1663,6 +1672,47 @@ public class RexSimplify {
continue;
}
}
+ break;
+ case NOT_EQUALS:
+ final Comparison notEqualsComparison = Comparison.of(term);
+ if (notEqualsComparison != null) {
+ // We are dealing with a X<>A term, check if we saw before another
NOT_EQUALS involving X
+ final RexNode prevNotEquals =
notEqualsComparisonMap.get(notEqualsComparison.ref);
+ if (prevNotEquals == null) {
+ // This is the first NOT_EQUALS involving X, put it in the map
+ notEqualsComparisonMap.put(notEqualsComparison.ref, term);
+ } else {
+ // There is already in the map another NOT_EQUALS involving X:
+ // - if it is already an IS_NOT_NULL: it was already simplified,
ignore this term
+ // - if it is not an IS_NOT_NULL (i.e. it is a NOT_EQUALS):
check comparison values
+ if (prevNotEquals.getKind() != SqlKind.IS_NOT_NULL) {
+ final Comparable comparable1 =
notEqualsComparison.literal.getValue();
+ //noinspection ConstantConditions
+ final Comparable comparable2 =
Comparison.of(prevNotEquals).literal.getValue();
+ //noinspection unchecked
+ if (comparable1.compareTo(comparable2) != 0) {
+ // X <> A OR X <> B => X IS NOT NULL OR NULL
+ final RexNode isNotNull =
+ rexBuilder.makeCall(SqlStdOperatorTable.IS_NOT_NULL,
notEqualsComparison.ref);
+ final RexNode constantNull =
+ rexBuilder.makeNullLiteral(trueLiteral.getType());
+ final RexNode newCondition = simplify(
+ rexBuilder.makeCall(SqlStdOperatorTable.OR, isNotNull,
constantNull),
+ unknownAs);
+ if (newCondition.isAlwaysTrue()) {
+ return trueLiteral;
+ }
+ notEqualsComparisonMap.put(notEqualsComparison.ref, isNotNull);
+ final int pos = terms.indexOf(prevNotEquals);
+ terms.set(pos, newCondition);
+ }
+ }
+ terms.remove(i);
+ --i;
+ continue;
+ }
+ }
+ break;
}
terms.set(i, term);
}
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 9b1a05a..cee74cc 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -2147,9 +2147,7 @@ public class RelOptRulesTest extends RelOptTestBase {
* ReduceExpressionsRule throws "duplicate key" exception</a>. */
@Test public void testReduceConstantsDup() throws Exception {
HepProgram program = new HepProgramBuilder()
- .addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE)
.addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.JOIN_INSTANCE)
.build();
final String sql = "select d.deptno"
@@ -2165,7 +2163,6 @@ public class RelOptRulesTest extends RelOptTestBase {
HepProgram program = new HepProgramBuilder()
.addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE)
.addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
- .addRuleInstance(ReduceExpressionsRule.JOIN_INSTANCE)
.build();
final String sql = "select *\n"
@@ -2175,6 +2172,90 @@ public class RelOptRulesTest extends RelOptTestBase {
checkPlanning(program, sql);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-3198";>[CALCITE-3198]
+ * Enhance RexSimplify to handle (x<>a or x<>b)</a>. */
+ @Test public void testReduceConstantsDup3() throws Exception {
+ HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
+ .build();
+
+ final String sql = "select d.deptno"
+ + " from dept d"
+ + " where d.deptno<>7 or d.deptno<>8";
+ checkPlanning(new HepPlanner(program), sql);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-3198";>[CALCITE-3198]
+ * Enhance RexSimplify to handle (x<>a or x<>b)</a>. */
+ @Test public void testReduceConstantsDup3Null() throws Exception {
+ HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
+ .build();
+
+ final String sql = "select e.empno"
+ + " from emp e"
+ + " where e.mgr<>7 or e.mgr<>8";
+ checkPlanning(new HepPlanner(program), sql);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-3198";>[CALCITE-3198]
+ * Enhance RexSimplify to handle (x<>a or x<>b)</a>. */
+ @Test public void testReduceConstantsDupNot() throws Exception {
+ HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
+ .build();
+
+ final String sql = "select d.deptno"
+ + " from dept d"
+ + " where not(d.deptno=7 and d.deptno=8)";
+ checkPlanning(new HepPlanner(program), sql);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-3198";>[CALCITE-3198]
+ * Enhance RexSimplify to handle (x<>a or x<>b)</a>. */
+ @Test public void testReduceConstantsDupNotNull() throws Exception {
+ HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
+ .build();
+
+ final String sql = "select e.empno"
+ + " from emp e"
+ + " where not(e.mgr=7 and e.mgr=8)";
+ checkPlanning(new HepPlanner(program), sql);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-3198";>[CALCITE-3198]
+ * Enhance RexSimplify to handle (x<>a or x<>b)</a>. */
+ @Test public void testReduceConstantsDupNot2() throws Exception {
+ HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
+ .build();
+
+ final String sql = "select d.deptno"
+ + " from dept d"
+ + " where not(d.deptno=7 and d.name='foo' and d.deptno=8)";
+ checkPlanning(new HepPlanner(program), sql);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-3198";>[CALCITE-3198]
+ * Enhance RexSimplify to handle (x<>a or x<>b)</a>. */
+ @Test public void testReduceConstantsDupNot2Null() throws Exception {
+ HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE)
+ .build();
+
+ final String sql = "select e.empno"
+ + " from emp e"
+ + " where not(e.mgr=7 and e.deptno=8 and e.mgr=8)";
+ checkPlanning(new HepPlanner(program), sql);
+ }
+
@Test public void testPullNull() throws Exception {
HepProgram program = new HepProgramBuilder()
.addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE)
diff --git a/core/src/test/java/org/apache/calcite/test/RexProgramTest.java
b/core/src/test/java/org/apache/calcite/test/RexProgramTest.java
index 736cfbd..04a5f3b 100644
--- a/core/src/test/java/org/apache/calcite/test/RexProgramTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RexProgramTest.java
@@ -1493,6 +1493,54 @@ public class RexProgramTest extends
RexProgramBuilderBase {
"false");
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-3198";>[CALCITE-3198]
+ * Enhance RexSimplify to handle (x<>a or x<>b)</a>. */
+ @Test public void testSimplifyOrNotEqualsNotNullable() {
+ checkSimplify(
+ or(
+ ne(vIntNotNull(), literal(1)),
+ ne(vIntNotNull(), literal(2))),
+ "true");
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-3198";>[CALCITE-3198]
+ * Enhance RexSimplify to handle (x<>a or x<>b)</a>. */
+ @Test public void testSimplifyOrNotEqualsNotNullable2() {
+ checkSimplify(
+ or(
+ ne(vIntNotNull(0), literal(1)),
+ eq(vIntNotNull(1), literal(10)),
+ ne(vIntNotNull(0), literal(2))),
+ "true");
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-3198";>[CALCITE-3198]
+ * Enhance RexSimplify to handle (x<>a or x<>b)</a>. */
+ @Test public void testSimplifyOrNotEqualsNullable() {
+ checkSimplify3(
+ or(
+ ne(vInt(), literal(1)),
+ ne(vInt(), literal(2))),
+ "OR(IS NOT NULL(?0.int0), null)", "IS NOT NULL(?0.int0)", "true");
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-3198";>[CALCITE-3198]
+ * Enhance RexSimplify to handle (x<>a or x<>b)</a>. */
+ @Test public void testSimplifyOrNotEqualsNullable2() {
+ checkSimplify3(
+ or(
+ ne(vInt(0), literal(1)),
+ eq(vInt(1), literal(10)),
+ ne(vInt(0), literal(2))),
+ "OR(IS NOT NULL(?0.int0), null, =(?0.int1, 10))",
+ "OR(IS NOT NULL(?0.int0), =(?0.int1, 10))",
+ "true");
+ }
+
@Test public void testSimplifyAndPush() {
final RelDataType intType = typeFactory.createSqlType(SqlTypeName.INTEGER);
final RelDataType rowType = typeFactory.builder()
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 e1d6ad6..dc19376 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -2095,6 +2095,117 @@ LogicalProject(DEPTNO=[$0])
]]>
</Resource>
</TestCase>
+ <TestCase name="testReduceConstantsDup3">
+ <Resource name="sql">
+ <![CDATA[select d.deptno from dept d where d.deptno<>7 or
d.deptno<>8]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[OR(<>($0, 7), <>($0, 8))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testReduceConstantsDup3Null">
+ <Resource name="sql">
+ <![CDATA[select e.empno from emp e where e.mgr<>7 or e.mgr<>8]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[OR(<>($3, 7), <>($3, 8))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[IS NOT NULL($3)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testReduceConstantsDupNot">
+ <Resource name="sql">
+ <![CDATA[select d.deptno from dept d where not(d.deptno=7 and
d.deptno=8)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[NOT(AND(=($0, 7), =($0, 8)))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testReduceConstantsDupNotNull">
+ <Resource name="sql">
+ <![CDATA[select e.empno from emp e where not(e.mgr=7 and
e.mgr=8)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[NOT(AND(=($3, 7), =($3, 8)))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[IS NOT NULL($3)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testReduceConstantsDupNot2">
+ <Resource name="sql">
+ <![CDATA[select d.deptno from dept d where not(d.deptno=7 and
d.name='foo' and d.deptno=8)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[NOT(AND(=($0, 7), =($1, 'foo'), =($0, 8)))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testReduceConstantsDupNot2Null">
+ <Resource name="sql">
+ <![CDATA[select e.empno from emp e where not(e.mgr=7 and
e.deptno=8 and e.mgr=8)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[NOT(AND(=($3, 7), =($7, 8), =($3, 8)))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[OR(IS NOT NULL($3), <>($7, 8))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testReduceConstantsEliminatesFilter">
<Resource name="sql">
<![CDATA[select * from (values (1,2)) where 1 + 2 > 3 + CAST(NULL
AS INTEGER)]]>
