[
https://issues.apache.org/jira/browse/CALCITE-5743?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
libopeng updated CALCITE-5743:
------------------------------
Description:
{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]
was:
{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]
> 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)
