[
https://issues.apache.org/jira/browse/CALCITE-5743?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17754333#comment-17754333
]
Julian Hyde edited comment on CALCITE-5743 at 8/14/23 10:56 PM:
----------------------------------------------------------------
[~shenlang], Here is a query on the built-in {{EMP}} table. The correct answer
(shown) is one row. Calcite currently returns zero rows:
{code}
select *
from dept
where deptno in (
select count(*)
from emp
where comm is null);
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
+--------+------------+----------+
{code}
({{EMP}} has 14 rows, 4 of which have a not-null value for {{comm}}.)
was (Author: julianhyde):
[~shenlang], Here is a query on the built-in EMP table. The correct answer
(shown) is one row. Calcite currently returns zero rows:
{code}
select *
from dept
where deptno in (
select count(*)
from emp
where comm is null);
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
+--------+------------+----------+
{code}
> Query gives incorrect result when COUNT appears in the correlated subquery
> select list
> --------------------------------------------------------------------------------------
>
> Key: CALCITE-5743
> URL: https://issues.apache.org/jira/browse/CALCITE-5743
> Project: Calcite
> Issue Type: Bug
> Reporter: libopeng
> Priority: Major
>
> {code:java}
> SELECT a
> FROM t1 t1
> WHERE b IN (SELECT COUNT (*) FROM t2 WHERE t1.a=t2.a); {code}
> {code:java}
> t1 | t2
> +----------+ | +-----+
> | a | b | | | a |
> +----------+ | +-----+
> | 3 | 6 | | | 3 |
> | 10 | 1 | | | 3 |
> | 8 | 0 | | | 10 |
> |
> {code}
> correct result
> {code:java}
> +------+
> | a |
> +------+
> | 10 |
> | 8 |{code}
> after decorrelate
> {code:java}
> LogicalProject(A=[$0])
> LogicalJoin(condition=[AND(=($0, $3), =($1, $2))], joinType=[inner])
> LogicalTableScan(table=[[t1]])
> LogicalFilter(condition=[=($0, $0)])
> LogicalProject(EXPR$0=[$1], a=[$0])
> LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
> LogicalProject(a=[$0])
> LogicalFilter(condition=[=($0, $0)])
> LogicalTableScan(table=[[t2]]) {code}
> error result
> {code:java}
> +------+
> | a |
> +------+
> | 10 | {code}
> Data with count=0 will be lost
> This issue was discovered in [this
> issue|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5568]
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
