[IGNITE-22092] Sql. Implement subquery decorrelation

• Type: Improvement
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Component/s: sql
• Labels: ignite-3
• Assignee: Unassigned
• Reporter: Maksim Zhuravkov
• Created: 23/Apr/24 12:15
• Updated: 29/Apr/24 11:30

Description

Rewriting correlated subqueries provides significant performance boost by removing redundant loops during execution and by allowing subsequent optimisations of flattened plans during planning.

P.S.

Apache calcite provides some means to decorrelate subqueries (via `RelDecorrelator`) but unfortunetly they are:

• produce incorrect results (see https://issues.apache.org/jira/browse/IGNITE-16493 where decorrelating subquery produces incorrect result because a resulting plan contains a join with `equality` instead of is `not distinct from` predicate (thus breaking queries for tables that contain NULLs).
• limited in scope.

Issue Links

• is related to: IGNITE-16493 Calcite engine. Decorrelation after subquery rewrite