[CALCITE-5117] Optimize the EXISTS sub-query by Metadata RowCount

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.30.0
• Fix Version/s: 1.31.0
• Component/s: core
• Labels: pull-request-available
• Assignee: xiong duan
• Reporter: xiong duan
• Created: 27/Apr/22 01:17
• Updated: 30/Oct/24 12:10
• Resolved: 28/Apr/22 02:41

Description

As same as the UNIQUE sub-query, before we convert the sub-query to RelNode. we use the Metadata to optimize it.

EXISTS sub-query, 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.

For example:

select *
from dept as d
where EXISTS (select count(*) from emp e where d.deptno = e.deptno)

We can optimize it to:

EnumerableTableScan(table=[[scott, DEPT]])

Issue Links

• is related to: CALCITE-1583 Wrong results for query with correlated subqueries with aggregate subquery expression
• relates to: CALCITE-6650 Optimize the IN sub-query and SOME sub-query by Metadata RowCount
• links to: GitHub Pull Request #2781