[jira] [Created] (FLINK-33485) Optimize the EXISTS sub-query by Metadata RowCount

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

Sergey Nuyanzin created FLINK-33485:
---------------------------------------

             Summary: Optimize the EXISTS sub-query by Metadata RowCount
                 Key: FLINK-33485
                 URL: https://issues.apache.org/jira/browse/FLINK-33485
             Project: Flink
          Issue Type: Improvement
          Components: Table SQL / API
    Affects Versions: 1.18.0
            Reporter: Sergey Nuyanzin


If the sub-query is guaranteed to produce at least one row, just return TRUE. If the sub-query is guaranteed to produce no row, just return FALSE.

inspired by CALCITE-5117 however since there is {{FlinkSubQueryRemoveRule}} then it shold be adopted accordingly

examples
{code:sql}
SELECT * FROM T2 WHERE EXISTS (SELECT SUM(a1), COUNT(*) FROM T1 WHERE 1=2)
{code}

aggregation functions always return 1 row even if there is an empty table then we could just replace this query with 
{code:sql}
SELECT * FROM T2 
{code}

another example
{code:sql}
SELECT * FROM MyTable WHERE NOT EXISTS (SELECT a FROM MyTable LIMIT 0)
{code}

{{LIMIT 0}} means no rows so it cold be optimized to

{code:sql}
SELECT * FROM MyTable
{code}





--
This message was sent by Atlassian Jira
(v8.20.10#820010)