[jira] [Commented] (CALCITE-5743) Query gives incorrect result when COUNT appears in the correlated subquery select list

["libopeng (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5743?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17729919#comment-17729919 ] 

libopeng commented on CALCITE-5743:
-----------------------------------

Rather than adding a join, could the existing join change from inner to left?
Q: Changing the inner join to a left join is not feasible. Taking the previous example into account, if we modify it to use a left join, the output result becomes 3, 10, 8. However, 3 should not be included in the output.

> 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)