[CALCITE-4719] Add variants of RexSubQuery that collect sub-queries into MULTISET, ARRAY and MAP collections

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.28.0
• Assignee: Julian Hyde
• Reporter: Julian Hyde
• Created: 05/Aug/21 18:58
• Updated: 17/Dec/21 08:58
• Resolved: 15/Oct/21 00:49

Description

Add variants of RexSubQuery that collect sub-queries into MULTISET, ARRAY and MAP collections. We currently use RexSubQuery for scalar sub-query, EXISTS, IN and some others; this allows us to defer conversion: convert via rewrites (RelOptRule) rather than in SqlToRelConverter. The same benefits would apply to MULTISET, ARRAY and MAP sub-queries.

Examples:

SELECT deptno, MULTISET(SELECT * FROM Emp WHERE deptno = d.deptno)
FROM Dept AS d

SELECT deptno, ARRAY(SELECT * FROM Emp WHERE deptno = d.deptno)
FROM Dept AS d

SELECT deptno, MAP(SELECT empno, job FROM Emp WHERE deptno = d.deptno)
FROM Dept AS d

Issue Links

• is related to: CALCITE-4720 Obsolete the Collect relational operator, using Aggregate and ARRAY_AGG (and new aggregate functions MULTISET_AGG and MAP_AGG) instead
• relates to: CALCITE-4718 Erroneous correlate variables in case of using collect sub-queries into ARRAY, MAP and MULTISET.
• relates to: CALCITE-4946 Add method RelBuilder.size()
• relates to: CALCITE-3524 Add methods to RelBuilder to create RexSubQuery