[
https://issues.apache.org/jira/browse/CALCITE-5743?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17752779#comment-17752779
]
LakeShen commented on CALCITE-5743:
-----------------------------------
Hi [~leepb] ,could you give me a sql to reproduce this case using EMP and DEPT
tables,I would reproduce it in my local environment.
> 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)
