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 2021/09/17 21:46:42 UTC
[calcite] 01/01: [CALCITE-4742] Implement "SOME <>" sub-query
(NobiGo)
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 <no...@gmail.com>
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