[ https://issues.apache.org/jira/browse/CALCITE-5743?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17755782#comment-17755782 ]
libopeng commented on CALCITE-5743: ----------------------------------- [~shenlang] Unlike [CALCITE-5930], which records bugs caused by data types, the current issue records bugs caused by decorrelation. In addition, [CALCITE-5930] is a duplicate submission, please refer to [CALCITE-5930] for details. > 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)