This is an automated email from the ASF dual-hosted git repository. jhyde pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 053d9b7e25ded230e8449b51d26a6d3cacac8fbe Author: NobiGo <[email protected]> AuthorDate: Fri Sep 17 12:05:29 2021 -0700 [CALCITE-4742] Implement "SOME <>" sub-query (NobiGo) Close apache/calcite#2512 --- .../calcite/rel/rules/SubQueryRemoveRule.java | 300 +++++++++++++++------ .../org/apache/calcite/test/RelOptRulesTest.java | 6 + .../apache/calcite/test/SqlToRelConverterTest.java | 6 + .../org/apache/calcite/test/RelOptRulesTest.xml | 42 +++ .../apache/calcite/test/SqlToRelConverterTest.xml | 16 ++ core/src/test/resources/sql/some.iq | 270 +++++++++++++++++++ core/src/test/resources/sql/sub-query.iq | 254 +++++++++++++++++ 7 files changed, 809 insertions(+), 85 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 1a8add9..e63999d 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 @@ -157,105 +157,235 @@ public class SubQueryRemoveRule // from emp) as q // final SqlQuantifyOperator op = (SqlQuantifyOperator) e.op; + switch (op.comparisonKind) { + case GREATER_THAN_OR_EQUAL: + case LESS_THAN_OR_EQUAL: + case LESS_THAN: + case GREATER_THAN: + case NOT_EQUALS: + break; - // SOME_EQ & SOME_NE should have been rewritten into IN/ NOT IN - assert op == SqlStdOperatorTable.SOME_GE || op == SqlStdOperatorTable.SOME_LE - || op == SqlStdOperatorTable.SOME_LT || op == SqlStdOperatorTable.SOME_GT; + default: + // "SOME =" should have been rewritten into IN. + throw new AssertionError("unexpected " + op); + } final RexNode caseRexNode; final RexNode literalFalse = builder.literal(false); final RexNode literalTrue = builder.literal(true); final RexLiteral literalUnknown = builder.getRexBuilder().makeNullLiteral(literalFalse.getType()); + final SqlAggFunction minMax = op.comparisonKind == SqlKind.GREATER_THAN || op.comparisonKind == SqlKind.GREATER_THAN_OR_EQUAL ? SqlStdOperatorTable.MIN : SqlStdOperatorTable.MAX; if (variablesSet.isEmpty()) { - // for non-correlated case queries such as - // select e.deptno, e.deptno < some (select deptno from emp) as v - // from emp as e - // - // becomes - // - // select e.deptno, - // case - // when q.c = 0 then false // sub-query is empty - // when (e.deptno < q.m) is true then true - // when q.c > q.d then unknown // sub-query has at least one null - // else e.deptno < q.m - // end as v - // from emp as e - // cross join ( - // select max(deptno) as m, count(*) as c, count(deptno) as d - // from emp) as q - builder.push(e.rel) - .aggregate(builder.groupKey(), - builder.aggregateCall(minMax, builder.field(0)).as("m"), - builder.count(false, "c"), - builder.count(false, "d", builder.field(0))) - .as("q") - .join(JoinRelType.INNER); - caseRexNode = builder.call(SqlStdOperatorTable.CASE, - builder.equals(builder.field("q", "c"), builder.literal(0)), - literalFalse, - builder.call(SqlStdOperatorTable.IS_TRUE, - builder.call(RexUtil.op(op.comparisonKind), - e.operands.get(0), builder.field("q", "m"))), - literalTrue, - builder.greaterThan(builder.field("q", "c"), - builder.field("q", "d")), - literalUnknown, - builder.call(RexUtil.op(op.comparisonKind), - e.operands.get(0), builder.field("q", "m"))); + switch (op.comparisonKind) { + case GREATER_THAN_OR_EQUAL: + case LESS_THAN_OR_EQUAL: + case LESS_THAN: + case GREATER_THAN: + // for non-correlated case queries such as + // select e.deptno, e.deptno < some (select deptno from emp) as v + // from emp as e + // + // becomes + // + // select e.deptno, + // case + // when q.c = 0 then false // sub-query is empty + // when (e.deptno < q.m) is true then true + // when q.c > q.d then unknown // sub-query has at least one null + // else e.deptno < q.m + // end as v + // from emp as e + // cross join ( + // select max(deptno) as m, count(*) as c, count(deptno) as d + // from emp) as q + builder.push(e.rel) + .aggregate(builder.groupKey(), + builder.aggregateCall(minMax, builder.field(0)).as("m"), + builder.count(false, "c"), + builder.count(false, "d", builder.field(0))) + .as("q") + .join(JoinRelType.INNER); + caseRexNode = builder.call(SqlStdOperatorTable.CASE, + builder.equals(builder.field("q", "c"), builder.literal(0)), + literalFalse, + builder.call(SqlStdOperatorTable.IS_TRUE, + builder.call(RexUtil.op(op.comparisonKind), + e.operands.get(0), builder.field("q", "m"))), + literalTrue, + builder.greaterThan(builder.field("q", "c"), + builder.field("q", "d")), + literalUnknown, + builder.call(RexUtil.op(op.comparisonKind), + e.operands.get(0), builder.field("q", "m"))); + break; + + case NOT_EQUALS: + // for non-correlated case queries such as + // select e.deptno, e.deptno <> some (select deptno from emp) as v + // from emp as e + // + // becomes + // + // select e.deptno, + // case + // when q.c = 0 then false // sub-query is empty + // when e.deptno is null then unknown + // when q.c <> q.d && q.d <= 1 then e.deptno != m || unknown + // when q.d = 1 + // then e.deptno != m // sub-query has the distinct result + // else true + // end as v + // from emp as e + // cross join ( + // select count(*) as c, count(deptno) as d, max(deptno) as m + // from (select distinct deptno from emp)) as q + builder.push(e.rel); + builder.distinct() + .aggregate(builder.groupKey(), + builder.count(false, "c"), + builder.count(false, "d", builder.field(0)), + builder.max(builder.field(0)).as("m")) + .as("q") + .join(JoinRelType.INNER); + caseRexNode = builder.call(SqlStdOperatorTable.CASE, + builder.equals(builder.field("c"), builder.literal(0)), + literalFalse, + builder.isNull(e.getOperands().get(0)), + literalUnknown, + builder.and( + builder.notEquals(builder.field("d"), builder.field("c")), + builder.lessThanOrEqual(builder.field("d"), + builder.literal(1))), + builder.or( + builder.notEquals(e.operands.get(0), builder.field("q", "m")), + literalUnknown), + builder.equals(builder.field("d"), builder.literal(1)), + builder.notEquals(e.operands.get(0), builder.field("q", "m")), + literalTrue); + break; + + default: + throw new AssertionError("not possible - per above check"); + } } else { - // for correlated case queries such as - // - // select e.deptno, e.deptno < some ( - // select deptno from emp where emp.name = e.name) as v - // from emp as e - // - // becomes - // - // select e.deptno, - // case - // when indicator is null then false // sub-query is empty for corresponding corr value - // when q.c = 0 then false // sub-query is empty - // when (e.deptno < q.m) is true then true - // when q.c > q.d then unknown // sub-query has at least one null - // else e.deptno < q.m - // end as v - // from emp as e - // left outer join ( - // select name, max(deptno) as m, count(*) as c, count(deptno) as d, - // "alwaysTrue" as indicator - // from emp group by name) as q on e.name = q.name - builder.push(e.rel) - .aggregate(builder.groupKey(), - builder.aggregateCall(minMax, builder.field(0)).as("m"), - builder.count(false, "c"), - builder.count(false, "d", builder.field(0))); - - final List<RexNode> parentQueryFields = new ArrayList<>(builder.fields()); - String indicator = "trueLiteral"; - parentQueryFields.add(builder.alias(literalTrue, indicator)); - builder.project(parentQueryFields).as("q"); - builder.join(JoinRelType.LEFT, literalTrue, variablesSet); - caseRexNode = builder.call(SqlStdOperatorTable.CASE, - builder.isNull(builder.field("q", indicator)), - literalFalse, - builder.equals(builder.field("q", "c"), builder.literal(0)), - literalFalse, - builder.call(SqlStdOperatorTable.IS_TRUE, - builder.call(RexUtil.op(op.comparisonKind), - e.operands.get(0), builder.field("q", "m"))), - literalTrue, - builder.greaterThan(builder.field("q", "c"), - builder.field("q", "d")), - literalUnknown, - builder.call(RexUtil.op(op.comparisonKind), - e.operands.get(0), builder.field("q", "m"))); + final String indicator = "trueLiteral"; + final List<RexNode> parentQueryFields = new ArrayList<>(); + switch (op.comparisonKind) { + case GREATER_THAN_OR_EQUAL: + case LESS_THAN_OR_EQUAL: + case LESS_THAN: + case GREATER_THAN: + // for correlated case queries such as + // + // select e.deptno, e.deptno < some ( + // select deptno from emp where emp.name = e.name) as v + // from emp as e + // + // becomes + // + // select e.deptno, + // case + // when indicator is null then false // sub-query is empty for corresponding corr value + // when q.c = 0 then false // sub-query is empty + // when (e.deptno < q.m) is true then true + // when q.c > q.d then unknown // sub-query has at least one null + // else e.deptno < q.m + // end as v + // from emp as e + // left outer join ( + // select name, max(deptno) as m, count(*) as c, count(deptno) as d, + // "alwaysTrue" as indicator + // from emp group by name) as q on e.name = q.name + builder.push(e.rel) + .aggregate(builder.groupKey(), + builder.aggregateCall(minMax, builder.field(0)).as("m"), + builder.count(false, "c"), + builder.count(false, "d", builder.field(0))); + + parentQueryFields.addAll(builder.fields()); + parentQueryFields.add(builder.alias(literalTrue, indicator)); + builder.project(parentQueryFields).as("q"); + builder.join(JoinRelType.LEFT, literalTrue, variablesSet); + caseRexNode = builder.call(SqlStdOperatorTable.CASE, + builder.isNull(builder.field("q", indicator)), + literalFalse, + builder.equals(builder.field("q", "c"), builder.literal(0)), + literalFalse, + builder.call(SqlStdOperatorTable.IS_TRUE, + builder.call(RexUtil.op(op.comparisonKind), + e.operands.get(0), builder.field("q", "m"))), + literalTrue, + builder.greaterThan(builder.field("q", "c"), + builder.field("q", "d")), + literalUnknown, + builder.call(RexUtil.op(op.comparisonKind), + e.operands.get(0), builder.field("q", "m"))); + break; + + case NOT_EQUALS: + // for correlated case queries such as + // + // select e.deptno, e.deptno <> some ( + // select deptno from emp where emp.name = e.name) as v + // from emp as e + // + // becomes + // + // select e.deptno, + // case + // when indicator is null + // then false // sub-query is empty for corresponding corr value + // when q.c = 0 then false // sub-query is empty + // when e.deptno is null then unknown + // when q.c <> q.d && q.d <= 1 + // then e.deptno != m || unknown + // when q.d = 1 + // then e.deptno != m // sub-query has the distinct result + // else true + // end as v + // from emp as e + // left outer join ( + // select name, count(distinct *) as c, count(distinct deptno) as d, + // max(deptno) as m, "alwaysTrue" as indicator + // from emp group by name) as q on e.name = q.name + builder.push(e.rel) + .aggregate(builder.groupKey(), + builder.count(true, "c"), + builder.count(true, "d", builder.field(0)), + builder.max(builder.field(0)).as("m")); + + parentQueryFields.addAll(builder.fields()); + parentQueryFields.add(builder.alias(literalTrue, indicator)); + builder.project(parentQueryFields).as("q"); // TODO use projectPlus + builder.join(JoinRelType.LEFT, literalTrue, variablesSet); + caseRexNode = builder.call(SqlStdOperatorTable.CASE, + builder.isNull(builder.field("q", indicator)), + literalFalse, + builder.equals(builder.field("c"), builder.literal(0)), + literalFalse, + builder.isNull(e.getOperands().get(0)), + literalUnknown, + builder.and( + builder.notEquals(builder.field("d"), builder.field("c")), + builder.lessThanOrEqual(builder.field("d"), + builder.literal(1))), + builder.or( + builder.notEquals(e.operands.get(0), builder.field("q", "m")), + literalUnknown), + builder.equals(builder.field("d"), builder.literal(1)), + builder.notEquals(e.operands.get(0), builder.field("q", "m")), + literalTrue); + break; + + default: + throw new AssertionError("not possible - per above check"); + } } // CASE statement above is created with nullable boolean type, but it might 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 f54bbf9..c86ccd9 100644 --- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java @@ -5548,6 +5548,12 @@ class RelOptRulesTest extends RelOptTestBase { checkSubQuery(sql).withLateDecorrelation(true).check(); } + @Test void testSomeWithNotEquality() { + final String sql = "select * from emp e1\n" + + " where e1.deptno <> SOME (select deptno 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 5fee9f3..5a1392d 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java @@ -1697,6 +1697,12 @@ class SqlToRelConverterTest extends SqlToRelTestBase { sql(sql).expand(false).ok(); } + @Test void testSomeWithNotEquality() { + final String sql = "select empno from emp where deptno <> some (\n" + + " select deptno from dept)"; + sql(sql).expand(false).ok(); + } + @Test 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 a99fa46..987802e 100644 --- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml @@ -11542,6 +11542,48 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ ]]> </Resource> </TestCase> + <TestCase name="testSomeWithNotEquality"> + <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=[<> SOME($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]) + LogicalFilter(condition=[OR(AND(<>($10, $9), <=($10, 1), OR(<>($7, $11), null), <>($9, 0)), AND(=($10, 1), <>($7, $11), <>($9, 0), OR(=($10, $9), >($10, 1))), AND(<>($9, 0), OR(=($10, $9), >($10, 1)), <>($10, 1)))]) + LogicalJoin(condition=[true], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(c=[$0], d=[$0], m=[$1]) + LogicalAggregate(group=[{}], c=[COUNT()], m=[MAX($0)]) + 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]) + LogicalFilter(condition=[OR(AND(<>($10, $9), <=($10, 1), OR(<>($7, $11), null), <>($9, 0)), AND(=($10, 1), <>($7, $11), <>($9, 0), OR(=($10, $9), >($10, 1))), AND(<>($9, 0), OR(=($10, $9), >($10, 1)), <>($10, 1)))]) + LogicalJoin(condition=[true], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(c=[$0], d=[$0], m=[$1]) + LogicalAggregate(group=[{}], c=[COUNT()], m=[MAX($0)]) + LogicalProject(DEPTNO=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> <TestCase name="testSortJoinCopyInnerJoinOrderBy"> <Resource name="sql"> <![CDATA[select * from sales.emp join sales.dept on 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 39b92d2..3bd6541 100644 --- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml @@ -5912,6 +5912,22 @@ LogicalProject(DEPTNO=[$0]) ]]> </Resource> </TestCase> + <TestCase name="testSomeWithNotEquality"> + <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=[<> SOME($7, { +LogicalProject(DEPTNO=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +})]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> <TestCase name="testSortInSubQuery"> <Resource name="sql"> <![CDATA[select * from (select empno from emp order by empno)]]> diff --git a/core/src/test/resources/sql/some.iq b/core/src/test/resources/sql/some.iq index 083256f..e0f14bb 100644 --- a/core/src/test/resources/sql/some.iq +++ b/core/src/test/resources/sql/some.iq @@ -178,6 +178,276 @@ from "scott".emp; !ok +# Some sub-query with not equality. +# Both sides Not NUll. +select * +from "scott".emp +where empno <> some (values (100), (200)); + ++-------+--------+-----------+------+------------+---------+---------+--------+ +| 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 + +# Previous, as scalar sub-query. +select *, empno <> some (values (100), (200)) as x +from "scott".emp; + ++-------+--------+-----------+------+------------+---------+---------+--------+------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | X | ++-------+--------+-----------+------+------------+---------+---------+--------+------+ +| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | true | +| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | true | +| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | true | +| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | true | +| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | true | +| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | true | +| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | true | +| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | true | +| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | true | +| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | true | +| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | true | +| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | true | +| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | true | +| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | true | ++-------+--------+-----------+------+------------+---------+---------+--------+------+ +(14 rows) + +!ok + +# left side NOT NULL, right side nullable. +select * +from "scott".emp +where empno <> some (values (7499),(NULL)); + ++-------+--------+-----------+------+------------+---------+---------+--------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | ++-------+--------+-----------+------+------------+---------+---------+--------+ +| 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 | +| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | ++-------+--------+-----------+------+------------+---------+---------+--------+ +(13 rows) + +!ok + +# Previous, as scalar sub-query. +select *, empno <> some (values (7499), (NULL)) as x +from "scott".emp; + ++-------+--------+-----------+------+------------+---------+---------+--------+------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | X | ++-------+--------+-----------+------+------------+---------+---------+--------+------+ +| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | true | +| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | +| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | true | +| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | true | +| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | true | +| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | true | +| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | true | +| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | true | +| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | true | +| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | true | +| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | true | +| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | true | +| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | true | +| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | true | ++-------+--------+-----------+------+------------+---------+---------+--------+------+ +(14 rows) + +!ok + +# left side NOT NULL, right side empty. +select * +from "scott".emp +where empno <> some (select empno from "scott".emp where empno = 8000); + ++-------+-------+-----+-----+----------+-----+------+--------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | ++-------+-------+-----+-----+----------+-----+------+--------+ ++-------+-------+-----+-----+----------+-----+------+--------+ +(0 rows) + +!ok + +# Previous, as scalar sub-query. +select *, empno <> some (select empno from "scott".emp where empno = 8000) as x +from "scott".emp; + ++-------+--------+-----------+------+------------+---------+---------+--------+-------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | X | ++-------+--------+-----------+------+------------+---------+---------+--------+-------+ +| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | false | +| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | false | +| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | false | +| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | false | +| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | false | +| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | false | +| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | false | +| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | false | +| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | false | +| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | false | +| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | false | +| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | false | +| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | false | +| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | false | ++-------+--------+-----------+------+------------+---------+---------+--------+-------+ +(14 rows) + +!ok + +# left side nullable, right side NOT NULL. +select * +from "scott".emp +where emp.comm <> some (values (300), (500)); + ++-------+--------+----------+------+------------+---------+---------+--------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | ++-------+--------+----------+------+------------+---------+---------+--------+ +| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | +| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | +| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | +| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | ++-------+--------+----------+------+------------+---------+---------+--------+ +(4 rows) + +!ok + +# Previous, as scalar sub-query. +select *, emp.comm <> some (values (300), (500)) as x +from "scott".emp; + ++-------+--------+-----------+------+------------+---------+---------+--------+------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | X | ++-------+--------+-----------+------+------------+---------+---------+--------+------+ +| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | | +| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | true | +| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | true | +| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | | +| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | true | +| 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 | true | +| 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 + +# left side nullable, right side nullable. +select * +from "scott".emp +where emp.comm <> some (select comm from "scott".emp); + ++-------+--------+----------+------+------------+---------+---------+--------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | ++-------+--------+----------+------+------------+---------+---------+--------+ +| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | +| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | +| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | +| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | ++-------+--------+----------+------+------------+---------+---------+--------+ +(4 rows) + +!ok + +# Previous, as scalar sub-query. +select *, emp.comm <> some (select comm from "scott".emp) as x +from "scott".emp; + ++-------+--------+-----------+------+------------+---------+---------+--------+------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | X | ++-------+--------+-----------+------+------------+---------+---------+--------+------+ +| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | | +| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | true | +| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | true | +| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | | +| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | true | +| 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 | true | +| 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 + +# left side nullable, right side empty. +select * +from "scott".emp +where emp.comm <> some (select comm from "scott".emp where comm = 800); + ++-------+-------+-----+-----+----------+-----+------+--------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | ++-------+-------+-----+-----+----------+-----+------+--------+ ++-------+-------+-----+-----+----------+-----+------+--------+ +(0 rows) + +!ok + +# Previous, as scalar sub-query. +select *, emp.comm <> some (select comm from "scott".emp where comm = 800) as x +from "scott".emp; + ++-------+--------+-----------+------+------------+---------+---------+--------+-------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | X | ++-------+--------+-----------+------+------------+---------+---------+--------+-------+ +| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | false | +| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | false | +| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | false | +| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | false | +| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | false | +| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | false | +| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | false | +| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | false | +| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | false | +| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | false | +| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | false | +| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | false | +| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | false | +| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | false | ++-------+--------+-----------+------+------------+---------+---------+--------+-------+ +(14 rows) + +!ok + # Sub-query is empty, so "< all" is trivially true. Even for null comm. select * from "scott".emp where comm < all (select comm from "scott".emp where 1 = 0) diff --git a/core/src/test/resources/sql/sub-query.iq b/core/src/test/resources/sql/sub-query.iq index 172dbe9..4200c47 100644 --- a/core/src/test/resources/sql/sub-query.iq +++ b/core/src/test/resources/sql/sub-query.iq @@ -2161,4 +2161,258 @@ EnumerableCalc(expr#0..2=[{inputs}], ENAME=[$t1]) !ok +!set outputformat mysql +# Correlated SOME sub-query with not equality +# Both sides Not NUll. +select empno +from "scott".emp emp1 +where empno <> some (select emp2.empno from "scott".emp emp2 where emp2.empno = emp1.empno); +EnumerableCalc(expr#0..5=[{inputs}], expr#6=[<>($t2, $t1)], expr#7=[1], expr#8=[<=($t2, $t7)], expr#9=[<>($t0, $t3)], expr#10=[IS NULL($t4)], expr#11=[0], expr#12=[=($t1, $t11)], expr#13=[OR($t10, $t12)], expr#14=[IS NOT TRUE($t13)], expr#15=[AND($t6, $t8, $t9, $t14)], expr#16=[=($t2, $t7)], expr#17=[IS NOT NULL($t2)], expr#18=[AND($t6, $t17)], expr#19=[IS NOT TRUE($t18)], expr#20=[AND($t16, $t9, $t14, $t19)], expr#21=[AND($t6, $t8)], expr#22=[IS NOT TRUE($t21)], expr#23=[IS NOT TRUE($t1 [...] + EnumerableMergeJoin(condition=[=($0, $5)], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0]) + EnumerableTableScan(table=[[scott, EMP]]) + EnumerableSort(sort0=[$4], dir0=[ASC]) + EnumerableCalc(expr#0..7=[{inputs}], expr#8=[1:BIGINT], expr#9=[true], c=[$t8], d=[$t8], m=[$t0], trueLiteral=[$t9], EMPNO1=[$t0]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan ++-------+ +| EMPNO | ++-------+ ++-------+ +(0 rows) + +!ok + +# Previous, as scalar sub-query. +select empno, empno <> some (select emp2.empno from "scott".emp emp2 where emp2.empno = emp1.empno) as x +from "scott".emp emp1; + ++-------+-------+ +| EMPNO | X | ++-------+-------+ +| 7369 | false | +| 7499 | false | +| 7521 | false | +| 7566 | false | +| 7654 | false | +| 7698 | false | +| 7782 | false | +| 7788 | false | +| 7839 | false | +| 7844 | false | +| 7876 | false | +| 7900 | false | +| 7902 | false | +| 7934 | false | ++-------+-------+ +(14 rows) + +!ok + +# left side NOT NULL, correlated sub-query nullable. +select * +from "scott".emp emp1 +where empno <> some (select comm from "scott".emp where deptno = emp1.deptno); +EnumerableCalc(expr#0..12=[{inputs}], expr#13=[<>($t9, $t8)], expr#14=[1], expr#15=[<=($t9, $t14)], expr#16=[AND($t13, $t15)], expr#17=[=($t9, $t14)], expr#18=[OR($t16, $t17)], expr#19=[<>($t0, $t10)], expr#20=[IS NULL($t11)], expr#21=[0], expr#22=[=($t8, $t21)], expr#23=[OR($t20, $t22)], expr#24=[IS NOT TRUE($t23)], expr#25=[AND($t18, $t19, $t24)], expr#26=[IS NOT TRUE($t18)], expr#27=[AND($t24, $t26)], expr#28=[OR($t25, $t27)], proj#0..7=[{exprs}], $condition=[$t28]) + EnumerableMergeJoin(condition=[=($7, $12)], joinType=[left]) + EnumerableSort(sort0=[$7], dir0=[ASC]) + EnumerableTableScan(table=[[scott, EMP]]) + EnumerableSort(sort0=[$4], dir0=[ASC]) + EnumerableCalc(expr#0..3=[{inputs}], expr#4=[true], c=[$t1], d=[$t2], m=[$t3], trueLiteral=[$t4], DEPTNO=[$t0]) + EnumerableAggregate(group=[{0}], c=[COUNT() FILTER $4], d=[COUNT($1) FILTER $3], m=[MIN($2) FILTER $4]) + EnumerableCalc(expr#0..3=[{inputs}], expr#4=[0], expr#5=[=($t3, $t4)], expr#6=[1], expr#7=[=($t3, $t6)], DEPTNO=[$t1], COMM=[$t0], m=[$t2], $g_0=[$t5], $g_1=[$t7]) + EnumerableAggregate(group=[{6, 7}], groups=[[{6, 7}, {7}]], m=[MAX($6)], $g=[GROUPING($7, $6)]) + EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)], proj#0..7=[{exprs}], $condition=[$t8]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan ++-------+--------+----------+------+------------+---------+---------+--------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | ++-------+--------+----------+------+------------+---------+---------+--------+ +| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | +| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | +| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | +| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | +| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | +| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | ++-------+--------+----------+------+------------+---------+---------+--------+ +(6 rows) + +!ok + +# Previous, as scalar sub-query. +select *, empno <> some (select comm from "scott".emp where deptno = emp1.deptno) as x +from "scott".emp as emp1; + ++-------+--------+-----------+------+------------+---------+---------+--------+------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | X | ++-------+--------+-----------+------+------------+---------+---------+--------+------+ +| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | | +| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | true | +| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | true | +| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | | +| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | true | +| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | true | +| 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 | true | +| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | | +| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | true | +| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | | +| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | | ++-------+--------+-----------+------+------------+---------+---------+--------+------+ +(14 rows) + +!ok + +# left side NOT NULL, correlated sub-query empty. +select * +from "scott".emp as emp1 +where empno <> some (select 2 from "scott".dept dept1 where dept1.deptno = emp1.empno); +EnumerableCalc(expr#0..12=[{inputs}], expr#13=[<>($t9, $t8)], expr#14=[1], expr#15=[<=($t9, $t14)], expr#16=[<>($t0, $t10)], expr#17=[IS NULL($t11)], expr#18=[0], expr#19=[=($t8, $t18)], expr#20=[OR($t17, $t19)], expr#21=[IS NOT TRUE($t20)], expr#22=[AND($t13, $t15, $t16, $t21)], expr#23=[=($t9, $t14)], expr#24=[IS NOT NULL($t9)], expr#25=[AND($t13, $t24)], expr#26=[IS NOT TRUE($t25)], expr#27=[AND($t23, $t16, $t21, $t26)], expr#28=[AND($t13, $t15)], expr#29=[IS NOT TRUE($t28)], expr#30= [...] + EnumerableMergeJoin(condition=[=($0, $12)], joinType=[left]) + EnumerableTableScan(table=[[scott, EMP]]) + EnumerableSort(sort0=[$4], dir0=[ASC]) + EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t3):INTEGER NOT NULL], expr#5=[true], c=[$t1], d=[$t2], m=[$t4], trueLiteral=[$t5], DEPTNO0=[$t0]) + EnumerableAggregate(group=[{0}], c=[COUNT() FILTER $4], d=[COUNT($1) FILTER $3], m=[MIN($2) FILTER $4]) + EnumerableCalc(expr#0..3=[{inputs}], expr#4=[0], expr#5=[=($t3, $t4)], expr#6=[1], expr#7=[=($t3, $t6)], proj#0..2=[{exprs}], $g_0=[$t5], $g_1=[$t7]) + EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], m=[MAX($1)], $g=[GROUPING($0, $1)]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):SMALLINT NOT NULL], expr#4=[2], DEPTNO0=[$t3], EXPR$0=[$t4]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan ++-------+-------+-----+-----+----------+-----+------+--------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | ++-------+-------+-----+-----+----------+-----+------+--------+ ++-------+-------+-----+-----+----------+-----+------+--------+ +(0 rows) + +!ok + +# Previous, as scalar sub-query. +select *, empno <> some (select 2 from "scott".dept dept1 where dept1.deptno = emp1.empno) as x +from "scott".emp as emp1; + ++-------+--------+-----------+------+------------+---------+---------+--------+-------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | X | ++-------+--------+-----------+------+------------+---------+---------+--------+-------+ +| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | false | +| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | false | +| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | false | +| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | false | +| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | false | +| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | false | +| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | false | +| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | false | +| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | false | +| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | false | +| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | false | +| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | false | +| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | false | +| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | false | ++-------+--------+-----------+------+------------+---------+---------+--------+-------+ +(14 rows) + +!ok + +# left side nullable, correlated sub-query empty. +select * +from "scott".emp as emp1 +where comm <> some (select 2 from "scott".dept dept1 where dept1.deptno = emp1.empno); +EnumerableCalc(expr#0..12=[{inputs}], expr#13=[<>($t9, $t8)], expr#14=[1], expr#15=[<=($t9, $t14)], expr#16=[AND($t13, $t15)], expr#17=[=($t9, $t14)], expr#18=[OR($t16, $t17)], expr#19=[<>($t6, $t10)], expr#20=[IS NULL($t11)], expr#21=[IS NULL($t6)], expr#22=[0], expr#23=[=($t8, $t22)], expr#24=[OR($t20, $t21, $t23)], expr#25=[IS NOT TRUE($t24)], expr#26=[AND($t18, $t19, $t25)], expr#27=[IS NOT TRUE($t18)], expr#28=[AND($t25, $t27)], expr#29=[OR($t26, $t28)], proj#0..7=[{exprs}], $condit [...] + EnumerableMergeJoin(condition=[=($0, $12)], joinType=[left]) + EnumerableTableScan(table=[[scott, EMP]]) + EnumerableSort(sort0=[$4], dir0=[ASC]) + EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t3):INTEGER NOT NULL], expr#5=[true], c=[$t1], d=[$t2], m=[$t4], trueLiteral=[$t5], DEPTNO0=[$t0]) + EnumerableAggregate(group=[{0}], c=[COUNT() FILTER $4], d=[COUNT($1) FILTER $3], m=[MIN($2) FILTER $4]) + EnumerableCalc(expr#0..3=[{inputs}], expr#4=[0], expr#5=[=($t3, $t4)], expr#6=[1], expr#7=[=($t3, $t6)], proj#0..2=[{exprs}], $g_0=[$t5], $g_1=[$t7]) + EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], m=[MAX($1)], $g=[GROUPING($0, $1)]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):SMALLINT NOT NULL], expr#4=[2], DEPTNO0=[$t3], EXPR$0=[$t4]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan ++-------+-------+-----+-----+----------+-----+------+--------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | ++-------+-------+-----+-----+----------+-----+------+--------+ ++-------+-------+-----+-----+----------+-----+------+--------+ +(0 rows) + +!ok + +# Previous, as scalar sub-query. +select *, comm <> some (select 2 from "scott".dept dept1 where dept1.deptno = emp1.empno) as x +from "scott".emp as emp1; + ++-------+--------+-----------+------+------------+---------+---------+--------+-------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | X | ++-------+--------+-----------+------+------------+---------+---------+--------+-------+ +| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | false | +| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | false | +| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | false | +| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | false | +| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | false | +| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | false | +| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | false | +| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | false | +| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | false | +| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | false | +| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | false | +| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | false | +| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | false | +| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | false | ++-------+--------+-----------+------+------------+---------+---------+--------+-------+ +(14 rows) + +!ok + +# left side nullable, correlated sub-query nullable. +select * +from "scott".emp emp1 +where emp1.comm <> some (select comm from "scott".emp emp2 where emp2.sal = emp1.sal); +EnumerableCalc(expr#0..12=[{inputs}], expr#13=[<>($t9, $t8)], expr#14=[1], expr#15=[<=($t9, $t14)], expr#16=[AND($t13, $t15)], expr#17=[=($t9, $t14)], expr#18=[OR($t16, $t17)], expr#19=[<>($t6, $t10)], expr#20=[IS NULL($t11)], expr#21=[IS NULL($t6)], expr#22=[0], expr#23=[=($t8, $t22)], expr#24=[OR($t20, $t21, $t23)], expr#25=[IS NOT TRUE($t24)], expr#26=[AND($t18, $t19, $t25)], expr#27=[IS NOT TRUE($t18)], expr#28=[AND($t25, $t27)], expr#29=[OR($t26, $t28)], proj#0..7=[{exprs}], $condit [...] + EnumerableMergeJoin(condition=[=($5, $12)], joinType=[left]) + EnumerableSort(sort0=[$5], dir0=[ASC]) + EnumerableTableScan(table=[[scott, EMP]]) + EnumerableSort(sort0=[$4], dir0=[ASC]) + EnumerableCalc(expr#0..3=[{inputs}], expr#4=[true], c=[$t1], d=[$t2], m=[$t3], trueLiteral=[$t4], SAL=[$t0]) + EnumerableAggregate(group=[{0}], c=[COUNT() FILTER $4], d=[COUNT($1) FILTER $3], m=[MIN($2) FILTER $4]) + EnumerableCalc(expr#0..3=[{inputs}], expr#4=[0], expr#5=[=($t3, $t4)], expr#6=[1], expr#7=[=($t3, $t6)], proj#0..2=[{exprs}], $g_0=[$t5], $g_1=[$t7]) + EnumerableAggregate(group=[{5, 6}], groups=[[{5, 6}, {5}]], m=[MAX($6)], $g=[GROUPING($5, $6)]) + EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t5)], proj#0..7=[{exprs}], $condition=[$t8]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan ++-------+--------+----------+------+------------+---------+---------+--------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | ++-------+--------+----------+------+------------+---------+---------+--------+ +| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | +| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | ++-------+--------+----------+------+------------+---------+---------+--------+ +(2 rows) + +!ok + +# Previous, as scalar sub-query. +select *, emp1.comm <> some (select comm from "scott".emp where sal = emp1.sal) as x +from "scott".emp emp1; + ++-------+--------+-----------+------+------------+---------+---------+--------+-------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | X | ++-------+--------+-----------+------+------------+---------+---------+--------+-------+ +| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | | +| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | false | +| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | true | +| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | | +| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | true | +| 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 | false | +| 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 + # End sub-query.iq
