You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by ch...@apache.org on 2022/06/15 01:41:14 UTC
[calcite] branch main updated: [CALCITE-5169] 'xx < 1 OR xx > 1' cannot be simplified to 'xx <> 1'
This is an automated email from the ASF dual-hosted git repository.
chunwei 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 e64a21ee2 [CALCITE-5169] 'xx < 1 OR xx > 1' cannot be simplified to 'xx <> 1'
e64a21ee2 is described below
commit e64a21ee27600537e0498cd1f72475ad1a1a0d2b
Author: Benchao Li <li...@gmail.com>
AuthorDate: Sun May 29 15:39:51 2022 +0800
[CALCITE-5169] 'xx < 1 OR xx > 1' cannot be simplified to 'xx <> 1'
Close apache/calcite#2821
---
.../java/org/apache/calcite/rex/RexSimplify.java | 4 ++-
.../org/apache/calcite/rex/RexProgramTest.java | 9 +++--
.../org/apache/calcite/test/RelOptRulesTest.java | 34 ++++++++++++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 40 ++++++++++++++++++++++
4 files changed, 84 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 559d251bd..006ce2535 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
@@ -2919,7 +2919,9 @@ public class RexSimplify {
* the IN call or single comparison.
*/
private static boolean simpleSarg(Sarg sarg) {
- return sarg.isPoints() || RangeSets.isOpenInterval(sarg.rangeSet);
+ return sarg.isPoints()
+ || RangeSets.isOpenInterval(sarg.rangeSet)
+ || sarg.isComplementedPoints();
}
/** If a term is a call to {@code SEARCH} on a {@link RexSargBuilder},
diff --git a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
index 081d18696..b3aea3413 100644
--- a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
+++ b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
@@ -1443,11 +1443,16 @@ class RexProgramTest extends RexProgramTestBase {
"true");
// "a = 1 or a <> 2" could (and should) be simplified to "a <> 2"
- // but can't do that right now
checkSimplifyFilter(
or(eq(aRef, literal1),
ne(aRef, literal2)),
- "OR(=(?0.a, 1), <>(?0.a, 2))");
+ "<>(?0.a, 2)");
+
+ // "a < 1 or a > 1" ==> "a <> 1"
+ checkSimplifyFilter(
+ or(lt(aRef, literal1),
+ gt(aRef, literal1)),
+ "<>(?0.a, 1)");
// "(a >= 1 and a <= 3) or a <> 2", or equivalently
// "a between 1 and 3 or a <> 2" ==> "true"
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 dc4b1a41d..127662f4a 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -1298,6 +1298,40 @@ class RelOptRulesTest extends RelOptTestBase {
.check();
}
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-5169">[CALCITE-5169]
+ * 'xx < 1 OR xx > 1' cannot be simplified to 'xx <> 1'</a>.
+ *
+ * <p>{@code ename <> '' and ename <> '3'} should be simplified to Sarg.
+ */
+ @Test void testExpressionSimplification1() {
+ final String sql = "select * from emp\n"
+ + "where ename <> '' and ename <> '3'";
+ sql(sql)
+ .withRule(CoreRules.FILTER_REDUCE_EXPRESSIONS)
+ .check();
+ }
+
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-5169">[CALCITE-5169]
+ * 'xx < 1 OR xx > 1' cannot be simplified to 'xx <> 1'</a>.
+ *
+ * <p>{@code (ename < '' or ename > '') and (ename < '3' or ename > '3')} should be
+ * simplified to Sarg too.
+ * The difference between this and {@link #testExpressionSimplification1()} is
+ * that '<' and '<>' have different
+ * {@link org.apache.calcite.sql.type.SqlOperandTypeChecker.Consistency} which
+ * will lead to different type inference result for literals. These two tests
+ * show that the simplification could handle this case.
+ */
+ @Test void testExpressionSimplification2() {
+ final String sql = "select * from emp\n"
+ + "where (ename < '' or ename > '') and (ename < '3' or ename > '3')";
+ sql(sql)
+ .withRule(CoreRules.FILTER_REDUCE_EXPRESSIONS)
+ .check();
+ }
+
@Test void testReduceAverage() {
final String sql = "select name, max(name), avg(deptno), min(name)\n"
+ "from sales.dept group by name";
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 fa18df0f9..2e72d4370 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -3791,6 +3791,46 @@ LogicalProject($0=[$3], $1=[$4])
<![CDATA[
LogicalProject(SUM1=[SUM($7) OVER (PARTITION BY $7 ORDER BY $5)], SUM2=[SUM(+($7, $5)) OVER (PARTITION BY $7 ORDER BY $5)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testExpressionSimplification1">
+ <Resource name="sql">
+ <![CDATA[select * from emp
+where ename <> '' and ename <> '3']]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[AND(<>($1, ''), <>($1, '3'))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[SEARCH($1, Sarg[(-∞..'':VARCHAR(20)), ('':VARCHAR(20)..'3':VARCHAR(20)), ('3':VARCHAR(20)..+∞)]:VARCHAR(20))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testExpressionSimplification2">
+ <Resource name="sql">
+ <![CDATA[select * from emp
+where (ename < '' or ename > '') and (ename < '3' or ename > '3')]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[AND(OR(<($1, ''), >($1, '')), OR(<($1, '3'), >($1, '3')))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[SEARCH($1, Sarg[(-∞..'':CHAR(1)), ('':CHAR(1)..'3'), ('3'..+∞)]:CHAR(1))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>