You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2015/05/01 22:42:02 UTC
[5/5] incubator-calcite git commit: [CALCITE-695] Do not add
SINGLE_VALUE aggregate function to a sub-query that will never return more
than one row (Hsuan-Yi Chu)
[CALCITE-695] Do not add SINGLE_VALUE aggregate function to a sub-query that will never return more than one row (Hsuan-Yi Chu)
Close apache/incubator-calcite#81
Project: http://git-wip-us.apache.org/repos/asf/incubator-calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/f076a9ba
Tree: http://git-wip-us.apache.org/repos/asf/incubator-calcite/tree/f076a9ba
Diff: http://git-wip-us.apache.org/repos/asf/incubator-calcite/diff/f076a9ba
Branch: refs/heads/master
Commit: f076a9bacc25dc5671b5557e48d9a654a6aa6854
Parents: f107218
Author: Hsuan-Yi Chu <hs...@usc.edu>
Authored: Thu Apr 30 11:16:03 2015 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Fri May 1 11:25:21 2015 -0700
----------------------------------------------------------------------
.../calcite/sql2rel/SqlToRelConverter.java | 25 ++++++++-
.../main/java/org/apache/calcite/util/Util.java | 47 +++++++++++++++++
.../calcite/test/SqlToRelConverterTest.java | 37 ++++++++++++++
.../calcite/test/SqlToRelConverterTest.xml | 54 ++++++++++++++++++++
4 files changed, 161 insertions(+), 2 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f076a9ba/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
----------------------------------------------------------------------
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 7bc58be..905578d 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -94,6 +94,7 @@ import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlMerge;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
+import org.apache.calcite.sql.SqlNumericLiteral;
import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlOperatorTable;
import org.apache.calcite.sql.SqlSampleSpec;
@@ -102,6 +103,7 @@ import org.apache.calcite.sql.SqlSelectKeyword;
import org.apache.calcite.sql.SqlSetOperator;
import org.apache.calcite.sql.SqlUpdate;
import org.apache.calcite.sql.SqlUtil;
+import org.apache.calcite.sql.SqlValuesOperator;
import org.apache.calcite.sql.SqlWindow;
import org.apache.calcite.sql.SqlWith;
import org.apache.calcite.sql.SqlWithItem;
@@ -1282,11 +1284,30 @@ public class SqlToRelConverter {
SqlNode selectExpr = selectList.get(0);
if (selectExpr instanceof SqlCall) {
SqlCall selectExprCall = (SqlCall) selectExpr;
- if (selectExprCall.getOperator()
- instanceof SqlAggFunction) {
+ if (Util.isSingleValue(selectExprCall)) {
return plan;
}
}
+
+ // If there is a limit with 0 or 1,
+ // it is ensured to produce a single value
+ if (select.getFetch() != null
+ && select.getFetch() instanceof SqlNumericLiteral) {
+ SqlNumericLiteral limitNum = (SqlNumericLiteral) select.getFetch();
+ if (((BigDecimal) limitNum.getValue()).intValue() < 2) {
+ return plan;
+ }
+ }
+ }
+ } else if (query instanceof SqlCall) {
+ // If the query is (values ...),
+ // it is necessary to look into the operands to determine
+ // whether SingleValueAgg is necessary
+ SqlCall exprCall = (SqlCall) query;
+ if (exprCall.getOperator()
+ instanceof SqlValuesOperator
+ && Util.isSingleValue(exprCall)) {
+ return plan;
}
}
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f076a9ba/core/src/main/java/org/apache/calcite/util/Util.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/util/Util.java b/core/src/main/java/org/apache/calcite/util/Util.java
index 5aa8246..f739fbd 100644
--- a/core/src/main/java/org/apache/calcite/util/Util.java
+++ b/core/src/main/java/org/apache/calcite/util/Util.java
@@ -19,6 +19,12 @@ package org.apache.calcite.util;
import org.apache.calcite.avatica.util.Spaces;
import org.apache.calcite.linq4j.Ord;
import org.apache.calcite.runtime.CalciteException;
+import org.apache.calcite.sql.SqlAggFunction;
+import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlLiteral;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlValuesOperator;
+import org.apache.calcite.sql.fun.SqlRowOperator;
import com.google.common.base.Function;
import com.google.common.base.Objects;
@@ -133,6 +139,47 @@ public class Util {
//~ Methods ----------------------------------------------------------------
/**
+ * Does nothing with its argument. Returns whether it is ensured that
+ * the call produces a single value
+ *
+ * @param call the expression to evaluate
+ * @return Whether it is ensured that the call produces a single value
+ */
+ public static boolean isSingleValue(SqlCall call) {
+ if (call.getOperator() instanceof SqlAggFunction) {
+ return true;
+ } else if (call.getOperator() instanceof SqlValuesOperator
+ || call.getOperator() instanceof SqlRowOperator) {
+ List<SqlNode> operands = call.getOperandList();
+ if (operands.size() == 1) {
+ SqlNode operand = operands.get(0);
+ if (operand instanceof SqlLiteral) {
+ return true;
+ } else if (operand instanceof SqlCall) {
+ return isSingleValue((SqlCall) operand);
+ }
+ }
+
+ return false;
+ } else {
+ boolean isScalar = true;
+ for (SqlNode operand : call.getOperandList()) {
+ if (operand instanceof SqlLiteral) {
+ continue;
+ }
+
+ if (!(operand instanceof SqlCall)
+ || !Util.isSingleValue((SqlCall) operand)) {
+ isScalar = false;
+ break;
+ }
+ }
+
+ return isScalar;
+ }
+ }
+
+ /**
* Does nothing with its argument. Call this method when you have a value
* you are not interested in, but you don't want the compiler to warn that
* you are not using it.
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f076a9ba/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
----------------------------------------------------------------------
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 c3a1f40..fd09a21 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -1143,6 +1143,43 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
}
/**
+ * 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>.
+ */
+ @Test public void testSubqueryAggreFunctionFollowedBySimpleOperation() {
+ sql("select deptno\n"
+ + "from EMP\n"
+ + "where deptno > (select min(deptno) * 2 + 10 from EMP)")
+ .convertsTo("${plan}");
+ }
+
+ /**
+ * 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>.
+ */
+ @Test public void testSubqueryValues() {
+ sql("select deptno\n"
+ + "from EMP\n"
+ + "where deptno > (values 10)")
+ .convertsTo("${plan}");
+ }
+
+ /**
+ * 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>.
+ */
+ @Test public void testSubqueryLimitOne() {
+ sql("select deptno\n"
+ + "from EMP\n"
+ + "where deptno > (select deptno \n"
+ + "from EMP order by deptno limit 1)")
+ .convertsTo("${plan}");
+ }
+
+ /**
* Visitor that checks that every {@link RelNode} in a tree is valid.
*
* @see RelNode#isValid(boolean)
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/f076a9ba/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
----------------------------------------------------------------------
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 7ad5b41..76070dc 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -2421,4 +2421,58 @@ LogicalAggregate(group=[{0}], EXPR$1=[SUM($1) FILTER $2], EXPR$2=[COUNT()])
]]>
</Resource>
</TestCase>
+ <TestCase name="testSubqueryAggreFunctionFollowedBySimpleOperation">
+ <Resource name="sql">
+ <![CDATA[select deptno
+from EMP
+where deptno > (select min(deptno) * 2 + 10 from EMP]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7])
+ LogicalFilter(condition=[>($7, $9)])
+ LogicalJoin(condition=[true], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(EXPR$0=[+(*($0, 2), 10)])
+ LogicalAggregate(group=[{}], agg#0=[MIN($0)])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSubqueryValues">
+ <Resource name="sql">
+ <![CDATA[select deptno
+from EMP
+where deptno > (values 10)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7])
+ LogicalFilter(condition=[>($7, $9)])
+ LogicalJoin(condition=[true], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalValues(tuples=[[{ 10 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSubqueryLimitOne">
+ <Resource name="sql">
+ <![CDATA[select deptno
+from EMP
+where deptno > (select deptno
+from EMP order by deptno limit 1)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7])
+ LogicalFilter(condition=[>($7, $9)])
+ LogicalJoin(condition=[true], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalSort(sort0=[$0], dir0=[ASC], fetch=[1])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
</Root>