[
https://issues.apache.org/jira/browse/CALCITE-6650?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17894190#comment-17894190
]
xiong duan commented on CALCITE-6650:
-------------------------------------
Hi [~julianhyde] . The NULL value works as expected, not a special case and I
will cover it in PR.
Here is an SQL example in PG:
{code:java}
table1:
+-----------+
|column_name|
+-----------+
|1 |
|2 |
|3 |
|null |
+-----------+
select column_name not in (select column_name from table2 where false) from
table1;
+--------+
|?column?|
+--------+
|true |
|true |
|true |
|true |
+--------+
select column_name > some (select column_name from table2 where false) from
table1;
+--------+
|?column?|
+--------+
|false |
|false |
|false |
|false |
+--------+
{code}
> Optimize the IN sub-query and SOME sub-query by Metadata RowCount
> -----------------------------------------------------------------
>
> Key: CALCITE-6650
> URL: https://issues.apache.org/jira/browse/CALCITE-6650
> Project: Calcite
> Issue Type: Improvement
> Reporter: xiong duan
> Priority: Major
> Labels: pull-request-available
>
> As same as the UNIQUE sub-query, before we convert the sub-query to RelNode.
> we use the Metadata to optimize it.
> * IN sub-query, If the sub-query is guaranteed to produce no row, just
> return FALSE.
> * SOME sub-query, If the sub-query is guaranteed to produce no row, just
> return FALSE.
> SQL Example:
> {code:java}
> select * from dept as d where deptno not in (select count(*) from emp e
> having false)
> Before this PR:
> LogicalProject(DEPTNO=[$0], NAME=[$1])
> LogicalProject(DEPTNO=[$0], NAME=[$1])
> LogicalFilter(condition=[OR(=($2, 0), AND(IS NULL($5), >=($3, $2)))])
> LogicalJoin(condition=[=(CAST($0):BIGINT NOT NULL, $4)],
> joinType=[left])
> LogicalJoin(condition=[true], joinType=[inner])\n
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(c=[$0], ck=[$0])
> LogicalAggregate(group=[{}], c=[COUNT()])
> LogicalValues(tuples=[[]])
> LogicalProject(EXPR$0=[$0], i=[true])
> LogicalValues(tuples=[[]])\n
> After this PR:
> LogicalProject(DEPTNO=[$0], NAME=[$1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
