[jira] [Created] (CALCITE-5743) An error occurs when count appears in the correlated subquery select list

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

libopeng created CALCITE-5743:
---------------------------------

             Summary: 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


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