This is an automated email from the ASF dual-hosted git repository.
hyuan 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 3f24710 [CALCITE-2936] Simplify EXISTS or NOT EXISTS sub-query that
has "GROUP BY ()"
3f24710 is described below
commit 3f24710db7e1ef91eb8fe934057456bffc2de780
Author: Haisheng Yuan <[email protected]>
AuthorDate: Wed Mar 20 16:43:31 2019 -0700
[CALCITE-2936] Simplify EXISTS or NOT EXISTS sub-query that has "GROUP BY
()"
---
.../apache/calcite/sql2rel/SqlToRelConverter.java | 19 +++++++-
.../apache/calcite/test/SqlToRelConverterTest.java | 40 ++++++++++++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 54 ++++++++++++++++++++++
3 files changed, 111 insertions(+), 2 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 6d24bf3..bb876ec 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -1166,8 +1166,23 @@ public class SqlToRelConverter {
if (!config.isExpand()) {
return;
}
- converted = convertExists(query, RelOptUtil.SubQueryType.EXISTS,
- subQuery.logic, true, null);
+ final SqlValidatorScope seekScope =
+ (query instanceof SqlSelect)
+ ? validator.getSelectScope((SqlSelect) query)
+ : null;
+ final Blackboard seekBb = createBlackboard(seekScope, null, false);
+ final RelNode seekRel = convertQueryOrInList(seekBb, query, null);
+ // An EXIST sub-query whose inner child has at least 1 tuple
+ // (e.g. an Aggregate with no grouping columns or non-empty Values
+ // node) should be simplified to a Boolean constant expression.
+ final RelMetadataQuery mq = seekRel.getCluster().getMetadataQuery();
+ final Double minRowCount = mq.getMinRowCount(seekRel);
+ if (minRowCount != null && minRowCount >= 1D) {
+ subQuery.expr = rexBuilder.makeLiteral(true);
+ return;
+ }
+ converted = RelOptUtil.createExistsPlan(seekRel,
+ RelOptUtil.SubQueryType.EXISTS, subQuery.logic, true, relBuilder);
assert !converted.indicator;
if (convertNonCorrelatedSubQuery(subQuery, bb, converted.r, true)) {
return;
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index b981201..3499657 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -2250,6 +2250,46 @@ public class SqlToRelConverterTest extends
SqlToRelTestBase {
/**
* Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-2936";>[CALCITE-2936]
+ * Existential sub-query that has aggregate without grouping key
+ * should be simplified to constant boolean expression</a>.
+ */
+ @Test public void testSimplifyExistsAggregateSubQuery() {
+ final String sql = "SELECT e1.empno\n"
+ + "FROM emp e1 where exists\n"
+ + "(select avg(sal) from emp e2 where e1.empno = e2.empno)";
+ sql(sql).decorrelate(true).ok();
+ }
+
+ @Test public void testSimplifyNotExistsAggregateSubQuery() {
+ final String sql = "SELECT e1.empno\n"
+ + "FROM emp e1 where not exists\n"
+ + "(select avg(sal) from emp e2 where e1.empno = e2.empno)";
+ sql(sql).decorrelate(true).ok();
+ }
+
+ /**
+ * Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-2936";>[CALCITE-2936]
+ * Existential sub-query that has Values with at least 1 tuple
+ * should be simplified to constant boolean expression</a>.
+ */
+ @Test public void testSimplifyExistsValuesSubQuery() {
+ final String sql = "select deptno\n"
+ + "from EMP\n"
+ + "where exists (values 10)";
+ sql(sql).decorrelate(true).ok();
+ }
+
+ @Test public void testSimplifyNotExistsValuesSubQuery() {
+ final String sql = "select deptno\n"
+ + "from EMP\n"
+ + "where not exists (values 10)";
+ sql(sql).decorrelate(true).ok();
+ }
+
+ /**
+ * Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-695";>[CALCITE-695]
* SqlSingleValueAggFunction is created when it may not be needed</a>.
*/
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 843c712..80f279b 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -3312,6 +3312,60 @@ LogicalAggregate(group=[{0}], EXPR$1=[SUM($1) FILTER
$2], EXPR$2=[COUNT()])
]]>
</Resource>
</TestCase>
+ <TestCase name="testSimplifyExistsAggregateSubQuery">
+ <Resource name="sql">
+ <![CDATA[SELECT e1.empno
+ FROM emp e1 where exists
+ (select avg(sal) from emp e2 where e1.empno = e2.empno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSimplifyNotExistsAggregateSubQuery">
+ <Resource name="sql">
+ <![CDATA[SELECT e1.empno
+ FROM emp e1 where not exists
+ (select avg(sal) from emp e2 where e1.empno = e2.empno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[NOT(true)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSimplifyExistsValuesSubQuery">
+ <Resource name="sql">
+ <![CDATA[select deptno
+ from EMP
+ where exists (values 10)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSimplifyNotExistsValuesSubQuery">
+ <Resource name="sql">
+ <![CDATA[select deptno
+ from EMP
+ where not exists (values 10)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7])
+ LogicalFilter(condition=[NOT(true)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testSubQueryAggregateFunctionFollowedBySimpleOperation">
<Resource name="sql">
<