[
https://issues.apache.org/jira/browse/CALCITE-7074?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17986533#comment-17986533
]
xiong duan commented on CALCITE-7074:
-------------------------------------
Only after IN-LIST is converted to VALUES will it be processed by the
SubQueryRemoveRule.
Before this PR:
(empno, deptno) not in ((1, 2), (3, null));
if empno is not null and depth is not null, the result will be evaluated as
NULL.
After this PR: the result is False.
and expression:
{code:java}
(empno, deptno) not in ((1, 2), (3, null), (null, null));{code}
return NULL.
Do you have any other suggestions regarding the description of this issue?
> IN-list that includes NULL converted to Values return wrong result
> ------------------------------------------------------------------
>
> Key: CALCITE-7074
> URL: https://issues.apache.org/jira/browse/CALCITE-7074
> Project: Calcite
> Issue Type: Bug
> Reporter: xiong duan
> Assignee: xiong duan
> Priority: Major
>
> In Sub-query.iq：
> {code:java}
> select * from "scott".emp where (empno, deptno) not in ((1, 2), (3, null));
> +-------+-------+-----+-----+----------+-----+------+--------+
> | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
> +-------+-------+-----+-----+----------+-----+------+--------+
> +-------+-------+-----+-----+----------+-----+------+--------+
> (0 rows)
> !ok {code}
> should return all values.
> This is because In SubQueryRemoveRule :
> {code:java}
> builder.aggregate(builder.groupKey(),
> builder.count(false, "c"),
> builder.count(builder.fields()).as("ck"));{code}
> When builder.fields contains multiple columns, builder.count() cannot convey
> the intended meaning. Currently, only one of the columns has been retrieved.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
