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/20 16:20:07 UTC
[calcite] branch master updated: [CALCITE-2986] Wrong results with
=ANY subquery (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 cd96973 [CALCITE-2986] Wrong results with =ANY subquery (Vineet Garg)
cd96973 is described below
commit cd9697398959bf518b255d1c59e7686c45f39646
Author: Vineet Garg <vg...@apache.org>
AuthorDate: Mon Apr 8 14:19:34 2019 -0700
[CALCITE-2986] Wrong results with =ANY subquery (Vineet Garg)
---
.../calcite/rel/rules/SubQueryRemoveRule.java | 3 +
.../java/org/apache/calcite/rex/RexSubQuery.java | 8 +-
.../org/apache/calcite/test/RelOptRulesTest.java | 12 +++
.../apache/calcite/test/SqlToRelConverterTest.java | 6 ++
.../org/apache/calcite/test/RelOptRulesTest.xml | 104 ++++++++++++++++++---
.../apache/calcite/test/SqlToRelConverterTest.xml | 16 ++++
core/src/test/resources/sql/some.iq | 25 +++++
7 files changed, 162 insertions(+), 12 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 04506ae..2279644 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
@@ -160,6 +160,9 @@ public abstract class SubQueryRemoveRule extends RelOptRule {
// from emp) as q
//
final SqlQuantifyOperator op = (SqlQuantifyOperator) e.op;
+
+ // SOME_EQ (=SOME) should have been rewritten into IN
+ assert op != SqlStdOperatorTable.SOME_EQ;
builder.push(e.rel)
.aggregate(builder.groupKey(),
op.comparisonKind == SqlKind.GREATER_THAN
diff --git a/core/src/main/java/org/apache/calcite/rex/RexSubQuery.java b/core/src/main/java/org/apache/calcite/rex/RexSubQuery.java
index fceb58e..9bf5ecc 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexSubQuery.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexSubQuery.java
@@ -56,10 +56,16 @@ public class RexSubQuery extends RexCall {
* <p>There is no ALL. For {@code x comparison ALL (sub-query)} use instead
* {@code NOT (x inverse-comparison SOME (sub-query))}.
* If {@code comparison} is {@code >}
- * then {@code negated-comparison} is {@code <=}, and so forth. */
+ * then {@code negated-comparison} is {@code <=}, and so forth.
+ *
+ * <p>Also =SOME is rewritten into IN</p> */
public static RexSubQuery some(RelNode rel, ImmutableList<RexNode> nodes,
SqlQuantifyOperator op) {
assert op.kind == SqlKind.SOME;
+
+ if (op == SqlStdOperatorTable.SOME_EQ) {
+ return RexSubQuery.in(rel, nodes);
+ }
final RelDataType type = type(rel, nodes);
return new RexSubQuery(type, op, nodes, rel);
}
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 5a76557..8c2c8ed 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -4389,6 +4389,18 @@ public class RelOptRulesTest extends RelOptTestBase {
checkSubQuery(sql).withLateDecorrelation(true).check();
}
+ @Test public void testSomeWithEquality() {
+ final String sql = "select * from emp e1\n"
+ + " where e1.deptno = SOME (select deptno from dept)";
+ checkSubQuery(sql).withLateDecorrelation(true).check();
+ }
+
+ @Test public void testSomeWithEquality2() {
+ final String sql = "select * from emp e1\n"
+ + " where e1.ename= SOME (select name 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>. */
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 a0df5ce..e1a5ef1 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -1389,6 +1389,12 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
sql(sql).expand(false).ok();
}
+ @Test public void testSomeWithEquality() {
+ final String sql = "select empno from emp where deptno = some (\n"
+ + " select deptno from dept)";
+ sql(sql).expand(false).ok();
+ }
+
@Test public void testNotInUncorrelatedSubQueryRex() {
final String sql = "select empno from emp where deptno not in"
+ " (select deptno from dept)";
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 022137d..6520204 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -548,7 +548,7 @@ LogicalProject(NAME=[$1], EXPR$1=[CAST(OR(AND(IS TRUE(>($0, $2)), <>($3, 0)), AN
</Resource>
<Resource name="planBefore">
<![CDATA[
-LogicalProject(DEPTNO=[$0], EXPR$1=[= SOME($1, {
+LogicalProject(DEPTNO=[$0], EXPR$1=[IN($1, {
LogicalProject(MGR=[$3])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
})])
@@ -557,21 +557,29 @@ LogicalProject(MGR=[$3])
</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])
+LogicalProject(DEPTNO=[$0], EXPR$1=[OR(AND(IS NOT NULL($5), <>($2, 0)), AND(<($3, $2), null, <>($2, 0), IS NULL($5)))])
+ LogicalJoin(condition=[=($1, $4)], joinType=[left])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
+ LogicalProject(MGR=[$3])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalProject(MGR=[$3], i=[true])
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])
+LogicalProject(DEPTNO=[$0], EXPR$1=[OR(AND(IS NOT NULL($5), <>($2, 0)), AND(<($3, $2), null, <>($2, 0), IS NULL($5)))])
+ LogicalJoin(condition=[=($1, $4)], joinType=[left])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
+ LogicalProject(MGR=[$3])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalProject(MGR=[$3], i=[true])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
@@ -8498,6 +8506,80 @@ LogicalProject(EMPNO=[10], ENAME=[$1], JOB=[$2], MGR=[null:INTEGER], HIREDATE=[$
]]>
</Resource>
</TestCase>
+ <TestCase name="testSomeWithEquality">
+ <Resource name="sql">
+ <![CDATA[select * from emp e1
+ where e1.deptno = SOME (select deptno from dept)]]>
+ </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=[IN($7, {
+LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+})])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![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])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![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])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSomeWithEquality2">
+ <Resource name="sql">
+ <![CDATA[select * from emp e1
+ where e1.ename= SOME (select name from dept)]]>
+ </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=[IN($1, {
+LogicalProject(NAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+})])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![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])
+ LogicalJoin(condition=[=($1, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(NAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![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])
+ LogicalJoin(condition=[=($1, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(NAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testSortRemovalAllKeysConstant">
<Resource name="sql">
<![CDATA[select count(*) as c
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 619a766..d5c6fcd 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -4532,6 +4532,22 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
]]>
</Resource>
</TestCase>
+ <TestCase name="testSomeWithEquality">
+ <Resource name="sql">
+ <![CDATA[select empno from emp where deptno = some (
+ select deptno from dept)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[IN($7, {
+LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+})])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testWithInsideScalarSubQueryRex">
<Resource name="sql">
<![CDATA[select (
diff --git a/core/src/test/resources/sql/some.iq b/core/src/test/resources/sql/some.iq
index fbceee7..83cecd0 100644
--- a/core/src/test/resources/sql/some.iq
+++ b/core/src/test/resources/sql/some.iq
@@ -19,6 +19,31 @@
!set expand false
!set outputformat mysql
+# =ANY
+select * from "scott".emp
+where empno = any (select empno from "scott".emp);
++-------+--------+-----------+------+------------+---------+---------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+--------+-----------+------+------------+---------+---------+--------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
+| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
+| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
+| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 |
+| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
+| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 |
+| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 |
+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 |
+| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
+| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
+| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 |
+| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 |
+| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 |
+| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 |
++-------+--------+-----------+------+------------+---------+---------+--------+
+(14 rows)
+
+!ok
+
# Both sides NOT NULL
select * from "scott".emp
where empno > any (select deptno from "scott".dept);