This is an automated email from the ASF dual-hosted git repository.
xiong pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new c2282e5b29 [CALCITE-7074] IN-list that includes NULL converted to
Values return wrong result
c2282e5b29 is described below
commit c2282e5b296af953b01bedd90681aafdbc534385
Author: Xiong Duan <[email protected]>
AuthorDate: Sat Jun 28 08:52:09 2025 +0800
[CALCITE-7074] IN-list that includes NULL converted to Values return wrong
result
---
.../calcite/rel/rules/SubQueryRemoveRule.java | 21 ++-
.../org/apache/calcite/test/JdbcAdapterTest.java | 23 +++
.../org/apache/calcite/test/RelOptRulesTest.xml | 186 ++++++++++-----------
core/src/test/resources/sql/sub-query.iq | 62 +++++--
4 files changed, 184 insertions(+), 108 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 fd6e3d7d74..ca5b016ae6 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
@@ -722,9 +722,24 @@ private static RexNode rewriteIn(RexSubQuery e,
Set<CorrelationId> variablesSet,
case TRUE_FALSE_UNKNOWN:
case UNKNOWN_AS_TRUE:
// Builds the cross join
- builder.aggregate(builder.groupKey(),
- builder.count(false, "c"),
- builder.count(builder.fields()).as("ck"));
+ // Some databases don't support use FILTER clauses for aggregate
functions
+ // like {@code COUNT(*) FILTER (WHERE not(a is null))}
+ // So use count(*) when only one column
+ if (builder.fields().size() <= 1) {
+ builder.aggregate(builder.groupKey(),
+ builder.count(false, "c"),
+ builder.count(builder.fields()).as("ck"));
+ } else {
+ builder.aggregate(builder.groupKey(),
+ builder.count(false, "c"),
+ builder.count()
+ .filter(builder
+ .not(builder
+ .and(builder.fields().stream()
+ .map(builder::isNull)
+ .collect(Collectors.toList()))))
+ .as("ck"));
+ }
builder.as(ctAlias);
if (!variablesSet.isEmpty()) {
builder.join(JoinRelType.LEFT, trueLiteral, variablesSet);
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
index 4379ab2505..7749bc8831 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
@@ -266,6 +266,29 @@ class JdbcAdapterTest {
+ "WHERE \"t\".\"c\" = 0 OR \"t0\".\"i\" IS NULL AND \"t\".\"ck\"
>= \"t\".\"c\"");
}
+ @Test void testNotPushDownNotIn() {
+ CalciteAssert.model(JdbcTest.SCOTT_MODEL)
+ .query("select * from dept where (deptno, dname) not in (select
deptno, ename from emp)")
+ .explainContains("PLAN=EnumerableCalc(expr#0..7=[{inputs}],
expr#8=[0], "
+ + "expr#9=[=($t3, $t8)], expr#10=[IS NULL($t7)], expr#11=[>=($t4,
$t3)], "
+ + "expr#12=[IS NOT NULL($t1)], expr#13=[AND($t10, $t11, $t12)], "
+ + "expr#14=[OR($t9, $t13)], proj#0..2=[{exprs}],
$condition=[$t14])\n"
+ + " EnumerableMergeJoin(condition=[AND(=($0, $5), =($1, $6))],
joinType=[left])\n"
+ + " EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC],
dir1=[ASC])\n"
+ + " EnumerableNestedLoopJoin(condition=[true],
joinType=[inner])\n"
+ + " JdbcToEnumerableConverter\n"
+ + " JdbcTableScan(table=[[SCOTT, DEPT]])\n"
+ + " EnumerableAggregate(group=[{}], c=[COUNT()],
ck=[COUNT() FILTER $0])\n"
+ + " JdbcToEnumerableConverter\n"
+ + " JdbcProject($f2=[OR(IS NOT NULL($7), IS NOT
NULL($1))])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
+ + " JdbcToEnumerableConverter\n"
+ + " JdbcSort(sort0=[$0], sort1=[$1], dir0=[ASC],
dir1=[ASC])\n"
+ + " JdbcAggregate(group=[{0, 1}], i=[LITERAL_AGG(true)])\n"
+ + " JdbcProject(DEPTNO=[$7],
ENAME=[CAST($1):VARCHAR(14)])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n\n");
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-6401";>[CALCITE-6401]
* JDBC adapter cannot push down JOIN with condition
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 5ff9ee3f65..cf7fba849d 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -5714,6 +5714,37 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$
LogicalFilter(condition=[=($cor0.DEPTNO, $9)])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[+(*(2, $7),
30)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testFullLeftSemiJoinTypeForDphyp">
+ <Resource name="sql">
+ <![CDATA[select emp.empno from emp_address full join emp on
emp_address.empno = emp.empno left join dept on emp.deptno = dept.deptno where
exists(select * from dept_nested where dept.deptno = dept_nested.deptno)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$9])
+ HyperGraph(edges=[{0, 1, 2}——[SEMI, =(node(2)_field(0),
node(3)_field(0))]——{3},{0, 1}——[LEFT, =(node(1)_field(7),
node(2)_field(0))]——{2},{0}——[FULL, =(node(0)_field(0),
node(1)_field(0))]——{1}])
+ LogicalProject(EMPNO=[$0], STREET=[$1.STREET], CITY=[$1.CITY],
ZIP=[$1.ZIP], STATE=[$1.STATE], STREET5=[$2.STREET], CITY6=[$2.CITY],
ZIP7=[$2.ZIP], STATE8=[$2.STATE])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_ADDRESS]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalProject(DEPTNO=[$0], $f0=[true])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalJoin(condition=[=($18, $20)], joinType=[semi])
+ LogicalJoin(condition=[=($7, $18)], joinType=[left])
+ LogicalJoin(condition=[=($9, $0)], joinType=[full])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(EMPNO=[$0], STREET=[$1.STREET], CITY=[$1.CITY],
ZIP=[$1.ZIP], STATE=[$1.STATE], STREET5=[$2.STREET], CITY6=[$2.CITY],
ZIP7=[$2.ZIP], STATE8=[$2.STATE])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_ADDRESS]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalProject(DEPTNO=[$0], $f0=[true])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
]]>
</Resource>
</TestCase>
@@ -6079,6 +6110,37 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], T=[$10])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[7934],
$f10=[CURRENT_TIMESTAMP])
LogicalFilter(condition=[=($0, 7934)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testInnerLeftSemiJoinTypeForDphyp">
+ <Resource name="sql">
+ <![CDATA[select emp.empno from emp_address inner join emp on
emp_address.empno = emp.empno left join dept on emp.deptno = dept.deptno where
exists(select * from dept_nested where dept.deptno = dept_nested.deptno)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$9])
+ HyperGraph(edges=[{1, 2}——[SEMI, =(node(2)_field(0),
node(3)_field(0))]——{3},{1}——[LEFT, =(node(1)_field(7),
node(2)_field(0))]——{2},{0}——[INNER, =(node(0)_field(0),
node(1)_field(0))]——{1}])
+ LogicalProject(EMPNO=[$0], STREET=[$1.STREET], CITY=[$1.CITY],
ZIP=[$1.ZIP], STATE=[$1.STATE], STREET5=[$2.STREET], CITY6=[$2.CITY],
ZIP7=[$2.ZIP], STATE8=[$2.STATE])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_ADDRESS]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalProject(DEPTNO=[$0], $f0=[true])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalJoin(condition=[=($11, $0)], joinType=[inner])
+ LogicalJoin(condition=[=($9, $11)], joinType=[semi])
+ LogicalJoin(condition=[=($7, $9)], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalProject(DEPTNO=[$0], $f0=[true])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
+ LogicalProject(EMPNO=[$0], STREET=[$1.STREET], CITY=[$1.CITY],
ZIP=[$1.ZIP], STATE=[$1.STATE], STREET5=[$2.STREET], CITY6=[$2.CITY],
ZIP7=[$2.ZIP], STATE8=[$2.STATE])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_ADDRESS]])
]]>
</Resource>
</TestCase>
@@ -8163,6 +8225,37 @@ LogicalProject(EMPNO=[$0])
<![CDATA[
LogicalProject(EMPNO=[$0])
LogicalValues(tuples=[[]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testLeftInnerSemiJoinTypeForDphyp">
+ <Resource name="sql">
+ <![CDATA[select emp.empno from emp_address left join emp on
emp_address.empno = emp.empno inner join dept on emp.deptno = dept.deptno where
exists(select * from dept_nested where dept.deptno = dept_nested.deptno)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$9])
+ HyperGraph(edges=[{2}——[SEMI, =(node(2)_field(0),
node(3)_field(0))]——{3},{0, 1}——[INNER, =(node(1)_field(7),
node(2)_field(0))]——{2},{0}——[LEFT, =(node(0)_field(0),
node(1)_field(0))]——{1}])
+ LogicalProject(EMPNO=[$0], STREET=[$1.STREET], CITY=[$1.CITY],
ZIP=[$1.ZIP], STATE=[$1.STATE], STREET5=[$2.STREET], CITY6=[$2.CITY],
ZIP7=[$2.ZIP], STATE8=[$2.STATE])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_ADDRESS]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalProject(DEPTNO=[$0], $f0=[true])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$11])
+ LogicalJoin(condition=[=($18, $0)], joinType=[inner])
+ LogicalJoin(condition=[=($0, $2)], joinType=[semi])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalProject(DEPTNO=[$0], $f0=[true])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
+ LogicalJoin(condition=[=($0, $9)], joinType=[left])
+ LogicalProject(EMPNO=[$0], STREET=[$1.STREET], CITY=[$1.CITY],
ZIP=[$1.ZIP], STATE=[$1.STATE], STREET5=[$2.STREET], CITY6=[$2.CITY],
ZIP7=[$2.ZIP], STATE8=[$2.STATE])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_ADDRESS]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -19491,99 +19584,6 @@ LogicalProject(DEPTNO=[$7])
<![CDATA[
LogicalProject(DEPTNO=[$0], NAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testInnerLeftSemiJoinTypeForDphyp">
- <Resource name="sql">
- <![CDATA[select emp.empno from emp_address inner join emp on
emp_address.empno = emp.empno left join dept on emp.deptno = dept.deptno where
exists(select * from dept_nested where dept.deptno = dept_nested.deptno)]]>
- </Resource>
- <Resource name="planBefore">
- <![CDATA[
-LogicalProject(EMPNO=[$9])
- HyperGraph(edges=[{1, 2}——[SEMI, =(node(2)_field(0),
node(3)_field(0))]——{3},{1}——[LEFT, =(node(1)_field(7),
node(2)_field(0))]——{2},{0}——[INNER, =(node(0)_field(0),
node(1)_field(0))]——{1}])
- LogicalProject(EMPNO=[$0], STREET=[$1.STREET], CITY=[$1.CITY],
ZIP=[$1.ZIP], STATE=[$1.STATE], STREET5=[$2.STREET], CITY6=[$2.CITY],
ZIP7=[$2.ZIP], STATE8=[$2.STATE])
- LogicalTableScan(table=[[CATALOG, SALES, EMP_ADDRESS]])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalProject(DEPTNO=[$0], $f0=[true])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
-]]>
- </Resource>
- <Resource name="planAfter">
- <![CDATA[
-LogicalProject(EMPNO=[$0])
- LogicalJoin(condition=[=($11, $0)], joinType=[inner])
- LogicalJoin(condition=[=($9, $11)], joinType=[semi])
- LogicalJoin(condition=[=($7, $9)], joinType=[left])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalProject(DEPTNO=[$0], $f0=[true])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
- LogicalProject(EMPNO=[$0], STREET=[$1.STREET], CITY=[$1.CITY],
ZIP=[$1.ZIP], STATE=[$1.STATE], STREET5=[$2.STREET], CITY6=[$2.CITY],
ZIP7=[$2.ZIP], STATE8=[$2.STATE])
- LogicalTableScan(table=[[CATALOG, SALES, EMP_ADDRESS]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testLeftInnerSemiJoinTypeForDphyp">
- <Resource name="sql">
- <![CDATA[select emp.empno from emp_address left join emp on
emp_address.empno = emp.empno inner join dept on emp.deptno = dept.deptno where
exists(select * from dept_nested where dept.deptno = dept_nested.deptno)]]>
- </Resource>
- <Resource name="planBefore">
- <![CDATA[
-LogicalProject(EMPNO=[$9])
- HyperGraph(edges=[{2}——[SEMI, =(node(2)_field(0),
node(3)_field(0))]——{3},{0, 1}——[INNER, =(node(1)_field(7),
node(2)_field(0))]——{2},{0}——[LEFT, =(node(0)_field(0),
node(1)_field(0))]——{1}])
- LogicalProject(EMPNO=[$0], STREET=[$1.STREET], CITY=[$1.CITY],
ZIP=[$1.ZIP], STATE=[$1.STATE], STREET5=[$2.STREET], CITY6=[$2.CITY],
ZIP7=[$2.ZIP], STATE8=[$2.STATE])
- LogicalTableScan(table=[[CATALOG, SALES, EMP_ADDRESS]])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalProject(DEPTNO=[$0], $f0=[true])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
-]]>
- </Resource>
- <Resource name="planAfter">
- <![CDATA[
-LogicalProject(EMPNO=[$11])
- LogicalJoin(condition=[=($18, $0)], joinType=[inner])
- LogicalJoin(condition=[=($0, $2)], joinType=[semi])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalProject(DEPTNO=[$0], $f0=[true])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
- LogicalJoin(condition=[=($0, $9)], joinType=[left])
- LogicalProject(EMPNO=[$0], STREET=[$1.STREET], CITY=[$1.CITY],
ZIP=[$1.ZIP], STATE=[$1.STATE], STREET5=[$2.STREET], CITY6=[$2.CITY],
ZIP7=[$2.ZIP], STATE8=[$2.STATE])
- LogicalTableScan(table=[[CATALOG, SALES, EMP_ADDRESS]])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testFullLeftSemiJoinTypeForDphyp">
- <Resource name="sql">
- <![CDATA[select emp.empno from emp_address full join emp on
emp_address.empno = emp.empno left join dept on emp.deptno = dept.deptno where
exists(select * from dept_nested where dept.deptno = dept_nested.deptno)]]>
- </Resource>
- <Resource name="planBefore">
- <![CDATA[
-LogicalProject(EMPNO=[$9])
- HyperGraph(edges=[{0, 1, 2}——[SEMI, =(node(2)_field(0),
node(3)_field(0))]——{3},{0, 1}——[LEFT, =(node(1)_field(7),
node(2)_field(0))]——{2},{0}——[FULL, =(node(0)_field(0),
node(1)_field(0))]——{1}])
- LogicalProject(EMPNO=[$0], STREET=[$1.STREET], CITY=[$1.CITY],
ZIP=[$1.ZIP], STATE=[$1.STATE], STREET5=[$2.STREET], CITY6=[$2.CITY],
ZIP7=[$2.ZIP], STATE8=[$2.STATE])
- LogicalTableScan(table=[[CATALOG, SALES, EMP_ADDRESS]])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalProject(DEPTNO=[$0], $f0=[true])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
-]]>
- </Resource>
- <Resource name="planAfter">
- <![CDATA[
-LogicalProject(EMPNO=[$0])
- LogicalJoin(condition=[=($18, $20)], joinType=[semi])
- LogicalJoin(condition=[=($7, $18)], joinType=[left])
- LogicalJoin(condition=[=($9, $0)], joinType=[full])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalProject(EMPNO=[$0], STREET=[$1.STREET], CITY=[$1.CITY],
ZIP=[$1.ZIP], STATE=[$1.STATE], STREET5=[$2.STREET], CITY6=[$2.CITY],
ZIP7=[$2.ZIP], STATE8=[$2.STATE])
- LogicalTableScan(table=[[CATALOG, SALES, EMP_ADDRESS]])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalProject(DEPTNO=[$0], $f0=[true])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index aab9e286c2..62426d0547 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -3937,6 +3937,43 @@ EnumerableCalc(expr#0..12=[{inputs}], expr#13=[0],
expr#14=[=($t8, $t13)], expr#
!plan
select * from "scott".emp where (empno, deptno) not in ((1, 2), (3, null));
++-------+--------+-----------+------+------------+---------+---------+--------+
+| 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
+
+EnumerableCalc(expr#0..14=[{inputs}], expr#15=[0], expr#16=[=($t8, $t15)],
expr#17=[IS NULL($t14)], expr#18=[>=($t9, $t8)], expr#19=[IS NOT NULL($t7)],
expr#20=[AND($t17, $t18, $t19)], expr#21=[OR($t16, $t20)], proj#0..7=[{exprs}],
$condition=[$t21])
+ EnumerableMergeJoin(condition=[AND(=($10, $12), =($11, $13))],
joinType=[left])
+ EnumerableSort(sort0=[$10], sort1=[$11], dir0=[ASC], dir1=[ASC])
+ EnumerableCalc(expr#0..9=[{inputs}], expr#10=[CAST($t0):INTEGER NOT
NULL], expr#11=[CAST($t7):INTEGER], proj#0..11=[{exprs}])
+ EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableAggregate(group=[{}], c=[COUNT()], ck=[COUNT() FILTER $0])
+ EnumerableValues(tuples=[[{ true }, { true }]])
+ EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
+ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
+ EnumerableValues(tuples=[[{ 3, null }, { 1, 2 }]])
+!plan
+
+# As above, but the IN-list includes all null value
+select * from "scott".emp where (mgr, deptno) not in ((1, 2), (3, null),
(cast(null as integer), cast(null as integer)));
+-------+-------+-----+-----+----------+-----+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----+-----+----------+-----+------+--------+
@@ -3945,17 +3982,18 @@ select * from "scott".emp where (empno, deptno) not in
((1, 2), (3, null));
!ok
-EnumerableCalc(expr#0..14=[{inputs}], expr#15=[0], expr#16=[=($t8, $t15)],
expr#17=[IS NULL($t14)], expr#18=[>=($t9, $t8)], expr#19=[IS NOT NULL($t7)],
expr#20=[AND($t17, $t18, $t19)], expr#21=[OR($t16, $t20)], proj#0..7=[{exprs}],
$condition=[$t21])
+EnumerableCalc(expr#0..14=[{inputs}], expr#15=[0], expr#16=[=($t8, $t15)],
expr#17=[IS NULL($t14)], expr#18=[>=($t9, $t8)], expr#19=[IS NOT NULL($t3)],
expr#20=[IS NOT NULL($t7)], expr#21=[AND($t17, $t18, $t19, $t20)],
expr#22=[OR($t16, $t21)], proj#0..7=[{exprs}], $condition=[$t22])
EnumerableMergeJoin(condition=[AND(=($10, $12), =($11, $13))],
joinType=[left])
EnumerableSort(sort0=[$10], sort1=[$11], dir0=[ASC], dir1=[ASC])
- EnumerableCalc(expr#0..9=[{inputs}], expr#10=[CAST($t0):INTEGER NOT
NULL], expr#11=[CAST($t7):INTEGER], proj#0..11=[{exprs}])
+ EnumerableCalc(expr#0..9=[{inputs}], expr#10=[CAST($t3):INTEGER],
expr#11=[CAST($t7):INTEGER], proj#0..11=[{exprs}])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
- EnumerableValues(tuples=[[{ null }, { 2 }]])
+ EnumerableAggregate(group=[{}], c=[COUNT()], ck=[COUNT() FILTER $0])
+ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NOT NULL($t0)],
expr#3=[IS NOT NULL($t1)], expr#4=[OR($t2, $t3)], $f2=[$t4])
+ EnumerableValues(tuples=[[{ 3, null }, { null, null }, { 1, 2
}]])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
- EnumerableValues(tuples=[[{ 3, null }, { 1, 2 }]])
+ EnumerableValues(tuples=[[{ 3, null }, { null, null }, { 1, 2 }]])
!plan
select * from "scott".emp where (empno, deptno) not in ((1, 2), (3, null),
(cast(null as integer), cast(null as integer)));
@@ -3994,8 +4032,8 @@ EnumerableCalc(expr#0..14=[{inputs}], expr#15=[0],
expr#16=[=($t8, $t15)], expr#
EnumerableCalc(expr#0..9=[{inputs}], expr#10=[CAST($t0):INTEGER NOT
NULL], expr#11=[CAST($t7):INTEGER], proj#0..11=[{exprs}])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0=[{inputs}], c=[$t0], ck=[$t0])
- EnumerableAggregate(group=[{}], c=[COUNT()])
+ EnumerableAggregate(group=[{}], c=[COUNT()], ck=[COUNT() FILTER $0])
+ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], $f2=[$t2])
EnumerableValues(tuples=[[{ 7369, 20 }, { 7499, 30 }]])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
@@ -4712,9 +4750,8 @@ EnumerableCalc(expr#0..8=[{inputs}], expr#9=[0],
expr#10=[=($t2, $t9)], expr#11=
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], COMM=[$t6])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0=[{inputs}], c=[$t0], ck=[$t0])
- EnumerableAggregate(group=[{}], c=[COUNT()])
- EnumerableValues(tuples=[[{ 500.00 }, { 300.00 }, { 0.00 }]])
+ EnumerableAggregate(group=[{}], c=[COUNT()], ck=[COUNT() FILTER $0])
+ EnumerableValues(tuples=[[{ true }, { true }, { true }]])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
EnumerableValues(tuples=[[{ 500.00, 500.00 }, { 300.00, 300.00 }, {
0.00, 0.00 }]])
@@ -4752,8 +4789,9 @@ EnumerableCalc(expr#0..8=[{inputs}], expr#9=[0],
expr#10=[=($t2, $t9)], expr#11=
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], COMM=[$t6])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0, $1)])
- EnumerableValues(tuples=[[{ 500.00, 500.00 }, { 300.00, 300.00 },
{ 0.00, 0.00 }, { null, null }]])
+ EnumerableAggregate(group=[{}], c=[COUNT()], ck=[COUNT() FILTER $0])
+ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NOT NULL($t0)],
expr#3=[IS NOT NULL($t1)], expr#4=[OR($t2, $t3)], $f2=[$t4])
+ EnumerableValues(tuples=[[{ 500.00, 500.00 }, { 300.00, 300.00
}, { 0.00, 0.00 }, { null, null }]])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
EnumerableValues(tuples=[[{ 500.00, 500.00 }, { 300.00, 300.00 }, {
0.00, 0.00 }, { null, null }]])
