[CALCITE-1045] Decorrelate sub-queries in Project and Join

• Type: Bug
• Status: Reopened
• Priority: Major
• Resolution: Unresolved
• Labels: pull-request-available sub-query
• Assignee: Benchao Li
• Reporter: Julian Hyde
• Created: 06/Jan/16 23:00
• Updated: 04/Apr/23 05:53

Description

In CALCITE-816 we created a representation of sub-query in the relational algebra, RexSubQuery, so that we could defer expanding and de-correlating sub-queries until after SqlNode tree has been converted to RelNode tree. But the RelDecorrelator can only reliably handle sub-queries that occur in a Filter (such as WHERE EXISTS or WHERE IN).

This task would except RelDecorrelator to handle sub-queries in Project (which occur with scalar sub-queries in the SELECT clause) and sub-queries in Join (which occur with scalar sub-queries in the ON clause of a JOIN).

Issue Links

• blocks: PHOENIX-2595 Upgrade Calcite-Phoenix to Calcite 1.6
• is blocked by: CALCITE-4808 RelOptUtil.getVariablesUsed should assert that correlated variables are used in scope
• is depended upon by: CALCITE-863 AssertionError while translating query with UNNEST and having two correlation conditions
• is depended upon by: CALCITE-709 LIMIT inside scalar sub-query
• is depended upon by: CALCITE-551 Sub-query inside aggregate function
• is depended upon by: CALCITE-559 Correlated scalar subquery in WHERE gives error
• is part of: CALCITE-4210 Sub-query in ON clause is not correctly expanded by SqlToRelConverter
• is part of: CALCITE-4340 Correlated Queries in ON clause do not work when referring to left side
• is related to: CALCITE-5213 PROJECT_TO_SUBQUERY producing Incorrect tree from nested correlated subqueries in projections with correlations in filters.
• is related to: CALCITE-4812 RelBuilder.join should error multiple correlate ids
• relates to: CALCITE-5588 ArrayIndexOutOfBoundsException on "on not exists" or "on exists"
• relates to: CALCITE-5127 Support correlation variables in Project
• relates to: CALCITE-3870 Change the default value of SqlToRelConverter.Config.expand from true to false
• split to: CALCITE-4686 SubQueryRemoveRule.matchJoin should correctly rewrite all sub-queries
• links to: GitHub Pull Request #2385