[ https://issues.apache.org/jira/browse/CALCITE-5743?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17728792#comment-17728792 ]
Julian Hyde commented on CALCITE-5743:
--------------------------------------
I believe that decorrelation is converting a correlated “GROUP BY ()” subquery to an uncorrelated “GROUP BY a” subquery. The former will produce a row even if no rows match.
Going between empty key and singleton key Aggregate is an issue that has cropped up many times in different guises. E.g. when removing group keys that are constant.
> An error occurs 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)