You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by hy...@apache.org on 2019/04/19 18:29:20 UTC
[calcite] branch master updated: [CALCITE-3007] Type mismatch for
ANY subquery in project (Vineet Garg)
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 ceb3674 [CALCITE-3007] Type mismatch for ANY subquery in project (Vineet Garg)
ceb3674 is described below
commit ceb36747a6b2546f87cf52e65c4f622a08e2828e
Author: Vineet Garg <vg...@apache.org>
AuthorDate: Wed Apr 17 19:05:50 2019 -0700
[CALCITE-3007] Type mismatch for ANY subquery in project (Vineet Garg)
Close #1172
---
.../calcite/rel/rules/SubQueryRemoveRule.java | 41 ++++++-----
.../org/apache/calcite/test/RelOptRulesTest.java | 24 +++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 82 ++++++++++++++++++++--
3 files changed, 127 insertions(+), 20 deletions(-)
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 501e46f..04506ae 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
@@ -163,26 +163,35 @@ public abstract class SubQueryRemoveRule extends RelOptRule {
builder.push(e.rel)
.aggregate(builder.groupKey(),
op.comparisonKind == SqlKind.GREATER_THAN
- || op.comparisonKind == SqlKind.GREATER_THAN_OR_EQUAL
- ? builder.min("m", builder.field(0))
- : builder.max("m", builder.field(0)),
+ || op.comparisonKind == SqlKind.GREATER_THAN_OR_EQUAL
+ ? builder.min("m", builder.field(0))
+ : builder.max("m", builder.field(0)),
builder.count(false, "c"),
builder.count(false, "d", builder.field(0)))
.as("q")
.join(JoinRelType.INNER);
- return builder.call(SqlStdOperatorTable.CASE,
- builder.call(SqlStdOperatorTable.EQUALS,
- builder.field("q", "c"), builder.literal(0)),
- builder.literal(false),
- builder.call(SqlStdOperatorTable.IS_TRUE,
- builder.call(RelOptUtil.op(op.comparisonKind, null),
- e.operands.get(0), builder.field("q", "m"))),
- builder.literal(true),
- builder.call(SqlStdOperatorTable.GREATER_THAN,
- builder.field("q", "c"), builder.field("q", "d")),
- builder.literal(null),
- builder.call(RelOptUtil.op(op.comparisonKind, null),
- e.operands.get(0), builder.field("q", "m")));
+ final RexNode caseRexNode = builder.call(SqlStdOperatorTable.CASE,
+ builder.call(SqlStdOperatorTable.EQUALS,
+ builder.field("q", "c"), builder.literal(0)),
+ builder.literal(false),
+ builder.call(SqlStdOperatorTable.IS_TRUE,
+ builder.call(RelOptUtil.op(op.comparisonKind, null),
+ e.operands.get(0), builder.field("q", "m"))),
+ builder.literal(true),
+ builder.call(SqlStdOperatorTable.GREATER_THAN,
+ builder.field("q", "c"), builder.field("q", "d")),
+ builder.literal(null),
+ builder.call(RelOptUtil.op(op.comparisonKind, null),
+ e.operands.get(0), builder.field("q", "m")));
+ // CASE statement above is created with nullable boolean type, but it might
+ // not be correct. If the original subquery node's type is not nullable it
+ // is guranteed for case statement to not produce NULLs. Therefore to avoid
+ // planner complaining we need to add cast. Note that nullable type is
+ // created due to MIN aggcall, since there is no groupby.
+ if (!e.getType().isNullable()) {
+ return builder.cast(caseRexNode, e.getType().getSqlTypeName());
+ }
+ return caseRexNode;
}
/**
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 1885d9d..5a76557 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -4366,6 +4366,30 @@ public class RelOptRulesTest extends RelOptTestBase {
}
/** Test case for
+ * testing type created by SubQueryRemoveRule
+ * ANY subquery is non-nullable therefore plan should have cast
+ */
+ @Test public void testAnyInProjectNonNullable() {
+ final String sql = "select name, \n"
+ + " deptno > ANY (\n"
+ + " select deptno from emp) \n"
+ + " from dept";
+ checkSubQuery(sql).withLateDecorrelation(true).check();
+ }
+
+ /** Test case for
+ * testing type created by SubQueryRemoveRule
+ * ANY subquery is nullable therefore plan should not have cast
+ */
+ @Test public void testAnyInProjectNullable() {
+ final String sql = "select deptno, \n"
+ + " name = ANY (\n"
+ + " select mgr from emp) \n"
+ + " from dept";
+ checkSubQuery(sql).withLateDecorrelation(true).check();
+ }
+
+ /** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-1546">[CALCITE-1546]
* Sub-queries connected by OR</a>. */
@Test public void testWhereOrSubQuery() {
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 427883d..022137d 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -481,7 +481,7 @@ LogicalProject(DEPTNO=[$0])
<![CDATA[
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[OR(=($10, 0), AND(>($0, $9), <>($10, 0), IS NOT TRUE(<=($0, $9)), <=($10, $11)))])
+ LogicalFilter(condition=[NOT(CAST(OR(AND(IS TRUE(<=($0, $9)), <>($10, 0)), AND(>($10, $11), null, <>($10, 0), IS NOT TRUE(<=($0, $9))), AND(<=($0, $9), <>($10, 0), IS NOT TRUE(<=($0, $9)), <=($10, $11)))):BOOLEAN NOT NULL)])
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{}], m=[MAX($0)], c=[COUNT()], d=[COUNT($0)])
@@ -493,7 +493,7 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
<![CDATA[
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[OR(=($10, 0), AND(>($0, $9), <>($10, 0), IS NOT TRUE(<=($0, $9)), <=($10, $11)))])
+ LogicalFilter(condition=[NOT(CAST(OR(AND(IS TRUE(<=($0, $9)), <>($10, 0)), AND(>($10, $11), null, <>($10, 0), IS NOT TRUE(<=($0, $9))), AND(<=($0, $9), <>($10, 0), IS NOT TRUE(<=($0, $9)), <=($10, $11)))):BOOLEAN NOT NULL)])
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{}], m=[MAX($0)], c=[COUNT()], d=[COUNT($0)])
@@ -502,6 +502,80 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
]]>
</Resource>
</TestCase>
+ <TestCase name="testAnyInProjectNonNullable">
+ <Resource name="sql">
+ <![CDATA[select name,
+ deptno > ANY (
+ select deptno from emp)
+ from dept]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(NAME=[$1], EXPR$1=[> SOME($0, {
+LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(NAME=[$1], EXPR$1=[CAST(OR(AND(IS TRUE(>($0, $2)), <>($3, 0)), AND(>($3, $4), null, <>($3, 0), IS NOT TRUE(>($0, $2))), AND(>($0, $2), <>($3, 0), IS NOT TRUE(>($0, $2)), <=($3, $4)))):BOOLEAN NOT NULL])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{}], m=[MIN($0)], c=[COUNT()], d=[COUNT($0)])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(NAME=[$1], EXPR$1=[CAST(OR(AND(IS TRUE(>($0, $2)), <>($3, 0)), AND(>($3, $4), null, <>($3, 0), IS NOT TRUE(>($0, $2))), AND(>($0, $2), <>($3, 0), IS NOT TRUE(>($0, $2)), <=($3, $4)))):BOOLEAN NOT NULL])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{}], m=[MIN($0)], c=[COUNT()], d=[COUNT($0)])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testAnyInProjectNullable">
+ <Resource name="sql">
+ <![CDATA[select deptno,
+ name = ANY (
+ select mgr from emp)
+ from dept]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], EXPR$1=[= SOME($1, {
+LogicalProject(MGR=[$3])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], EXPR$1=[OR(AND(IS TRUE(=($1, $2)), <>($3, 0)), AND(>($3, $4), null, <>($3, 0), IS NOT TRUE(=($1, $2))), AND(=($1, $2), <>($3, 0), IS NOT TRUE(=($1, $2)), <=($3, $4)))])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{}], m=[MAX($0)], c=[COUNT()], d=[COUNT($0)])
+ LogicalProject(MGR=[$3])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], EXPR$1=[OR(AND(IS TRUE(=($1, $2)), <>($3, 0)), AND(>($3, $4), null, <>($3, 0), IS NOT TRUE(=($1, $2))), AND(=($1, $2), <>($3, 0), IS NOT TRUE(=($1, $2)), <=($3, $4)))])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{}], m=[MAX($0)], c=[COUNT()], d=[COUNT($0)])
+ LogicalProject(MGR=[$3])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testCasePushIsAlwaysWorking">
<Resource name="sql">
<![CDATA[select empno from emp where case when sal > 1000 then empno else sal end = 1]]>
@@ -7746,7 +7820,7 @@ LogicalProject(DEPTNO=[$0])
<![CDATA[
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[OR(AND(>($0, $9), <>($10, 0)), AND(>($0, $9), <>($10, 0), IS NOT TRUE(>($0, $9)), <=($10, $11)))])
+ LogicalFilter(condition=[OR(AND(IS TRUE(>($0, $9)), <>($10, 0)), AND(>($10, $11), null, <>($10, 0), IS NOT TRUE(>($0, $9))), AND(>($0, $9), <>($10, 0), IS NOT TRUE(>($0, $9)), <=($10, $11)))])
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{}], m=[MIN($0)], c=[COUNT()], d=[COUNT($0)])
@@ -7758,7 +7832,7 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
<![CDATA[
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[OR(AND(>($0, $9), <>($10, 0)), AND(>($0, $9), <>($10, 0), IS NOT TRUE(>($0, $9)), <=($10, $11)))])
+ LogicalFilter(condition=[OR(AND(IS TRUE(>($0, $9)), <>($10, 0)), AND(>($10, $11), null, <>($10, 0), IS NOT TRUE(>($0, $9))), AND(>($0, $9), <>($10, 0), IS NOT TRUE(>($0, $9)), <=($10, $11)))])
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{}], m=[MIN($0)], c=[COUNT()], d=[COUNT($0)])