This is an automated email from the ASF dual-hosted git repository.
xiong 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 3db4dd1ebf [CALCITE-5117] Optimize the EXISTS sub-query by Metadata
RowCount
3db4dd1ebf is described below
commit 3db4dd1ebf04abaf74d29464ae311a598ae929f7
Author: nobigo <[email protected]>
AuthorDate: Wed Apr 27 09:35:40 2022 +0800
[CALCITE-5117] Optimize the EXISTS sub-query by Metadata RowCount
---
.../calcite/rel/rules/SubQueryRemoveRule.java | 12 +++-
.../org/apache/calcite/test/RelOptRulesTest.java | 17 +++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 57 ++++++++++++++++
core/src/test/resources/sql/sub-query.iq | 79 ++++++++++++++++++++++
4 files changed, 164 insertions(+), 1 deletion(-)
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java
index ba2eeb592e..b387724153 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java
@@ -450,8 +450,18 @@ public class SubQueryRemoveRule
*/
private static RexNode rewriteExists(RexSubQuery e, Set<CorrelationId>
variablesSet,
RelOptUtil.Logic logic, RelBuilder builder) {
+ // If the sub-query is guaranteed to produce at least one row, just return
+ // TRUE.
+ final RelMetadataQuery mq = e.rel.getCluster().getMetadataQuery();
+ final Double minRowCount = mq.getMinRowCount(e.rel);
+ if (minRowCount != null && minRowCount >= 1D) {
+ return builder.literal(true);
+ }
+ final Double maxRowCount = mq.getMaxRowCount(e.rel);
+ if (maxRowCount != null && maxRowCount < 1D) {
+ return builder.literal(false);
+ }
builder.push(e.rel);
-
builder.project(builder.alias(builder.literal(true), "i"));
switch (logic) {
case 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 ad714d26d3..1431407c89 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -3333,6 +3333,23 @@ class RelOptRulesTest extends RelOptTestBase {
.check();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5117";>[CALCITE-5117]
+ * Optimize the EXISTS sub-query by Metadata RowCount</a>. */
+ @Test void testExistsWithAtLeastOneRowSubQuery() {
+ final String sql = "select * from dept as d\n"
+ + "where EXISTS (\n"
+ + " select count(*) from emp e where d.deptno = e.deptno)";
+ sql(sql).withSubQueryRules().check();
+ }
+
+ @Test void testExistsWithNoRowSubQuery() {
+ final String sql = "select * from dept as d\n"
+ + "where NOT EXISTS (\n"
+ + " select count(*) from emp e having false)";
+ sql(sql).withSubQueryRules().check();
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-4848";>[CALCITE-4848]
* Adding a HAVING condition to a query with a dynamic parameter makes the
result always empty
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 e2774111bb..c30ab69e9a 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -3097,6 +3097,63 @@ LogicalSortExchange(distribution=[hash[1]],
collation=[[1]])
LogicalExchange(distribution=[single])
LogicalFilter(condition=[=($0, 10)])
LogicalTableScan(table=[[scott, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testExistsWithAtLeastOneRowSubQuery">
+ <Resource name="sql">
+ <![CDATA[select * from dept as d
+where EXISTS(select count(*) from emp e where d.deptno = e.deptno)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], NAME=[$1])
+ LogicalFilter(condition=[EXISTS({
+LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
+ LogicalProject($f0=[0])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})], variablesSet=[[$cor0]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], NAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], NAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testExistsWithNoRowSubQuery">
+ <Resource name="sql">
+ <![CDATA[select * from dept as d
+where NOT EXISTS(select count(*) from emp e having false)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], NAME=[$1])
+ LogicalFilter(condition=[NOT(EXISTS({
+LogicalValues(tuples=[[]])
+}))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], NAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], NAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index bbad2b6f2b..2d4936b696 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -3383,4 +3383,83 @@ EnumerableCalc(expr#0..12=[{inputs}], expr#13=[IS NOT
TRUE($t12)], expr#14=[IS N
EnumerableValues(tuples=[[{ 7369, 20 }, { 7499, 30 }]])
!plan
+# [CALCITE-5117] Optimize the EXISTS sub-query by Metadata RowCount
+
+# Test case about sub-query is guaranteed to produce at least one row
+select *
+from dept as d
+where EXISTS (select count(*) from emp e where d.deptno = e.deptno);
++--------+------------+----------+
+| DEPTNO | DNAME | LOC |
++--------+------------+----------+
+| 10 | ACCOUNTING | NEW YORK |
+| 20 | RESEARCH | DALLAS |
+| 30 | SALES | CHICAGO |
+| 40 | OPERATIONS | BOSTON |
++--------+------------+----------+
+(4 rows)
+
+!ok
+
+EnumerableTableScan(table=[[scott, DEPT]])
+!plan
+
+# As above, but the filter condition always false
+select *
+from dept as d
+where EXISTS (select count(*) from emp e where d.deptno = e.deptno and 1 = 2);
++--------+------------+----------+
+| DEPTNO | DNAME | LOC |
++--------+------------+----------+
+| 10 | ACCOUNTING | NEW YORK |
+| 20 | RESEARCH | DALLAS |
+| 30 | SALES | CHICAGO |
+| 40 | OPERATIONS | BOSTON |
++--------+------------+----------+
+(4 rows)
+
+!ok
+
+EnumerableTableScan(table=[[scott, DEPT]])
+!plan
+
+
+# As above, but the Sum aggregation function
+select *
+from dept as d
+where EXISTS (select sum(1) from emp e where d.deptno = e.deptno and 1 = 2);
++--------+------------+----------+
+| DEPTNO | DNAME | LOC |
++--------+------------+----------+
+| 10 | ACCOUNTING | NEW YORK |
+| 20 | RESEARCH | DALLAS |
+| 30 | SALES | CHICAGO |
+| 40 | OPERATIONS | BOSTON |
++--------+------------+----------+
+(4 rows)
+
+!ok
+
+EnumerableTableScan(table=[[scott, DEPT]])
+!plan
+
+# Test case about sub-query is guaranteed to produce no row
+select *
+from dept as d
+where NOT EXISTS (select count(*) from emp e having false);
++--------+------------+----------+
+| DEPTNO | DNAME | LOC |
++--------+------------+----------+
+| 10 | ACCOUNTING | NEW YORK |
+| 20 | RESEARCH | DALLAS |
+| 30 | SALES | CHICAGO |
+| 40 | OPERATIONS | BOSTON |
++--------+------------+----------+
+(4 rows)
+
+!ok
+
+EnumerableTableScan(table=[[scott, DEPT]])
+!plan
+
# End sub-query.iq
