[ https://issues.apache.org/jira/browse/CALCITE-3787?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17036435#comment-17036435 ]
Julian Hyde commented on CALCITE-3787: -------------------------------------- I believe that the {{Uncollect}} relational operator unnests multiple fields (if given an input with multiple collection fields). So perhaps we should exploit that. (Or perhaps not. The {{Join}} operator can perform cartesian products. So why do we need {{Uncollect}} to do them also?) In the above example, there are 3 relations in the {{FROM}} clause and therefore SqlToRel is doing the right thing in creating one table scan and two uncollects. If any code is going to combine multiple {{Uncollect}} operators into one, it should be a planner rule, not SqlToRel. > Revisit Sql To Rel for a chained UNNEST > --------------------------------------- > > Key: CALCITE-3787 > URL: https://issues.apache.org/jira/browse/CALCITE-3787 > Project: Calcite > Issue Type: Bug > Components: core > Reporter: Will Yu > Priority: Trivial > > Need to revisit the behavior of a chained UNNEST to unnest two array columns > at the same time. > Currently it seems to yield a *cartesian product*: > {code:java} > @Test public void testUnnestArrayPlan() { > final String sql = "select d.deptno, e2.empno, e3.detail\n" > + "from dept_nested as d,\n" > + " UNNEST(d.employees) e2, UNNEST(d.employees) e3"; > sql(sql).with(getExtendedTester()).ok(); > } > {code} > which yield: > {code:java} > LogicalProject(DEPTNO=[$0], EMPNO=[$7], DETAIL=[ROW($12)]) > LogicalCorrelate(correlation=[$cor1], joinType=[inner], > requiredColumns=[{6}]) > LogicalCorrelate(correlation=[$cor0], joinType=[inner], > requiredColumns=[{6}]) > LogicalProject(DEPTNO=[$0], NAME=[$1], TYPE=[$2.TYPE], DESC=[$2.DESC], > A=[$2.OTHERS.A], B=[$2.OTHERS.B], EMPLOYEES=[$3]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]]) > Uncollect > LogicalProject(EMPLOYEES=[$cor0.EMPLOYEES_6]) > LogicalValues(tuples=[[{ 0 }]]) > Uncollect > LogicalProject(EMPLOYEES=[$cor1.EMPLOYEES_6]) > LogicalValues(tuples=[[{ 0 }]]) > {code} > Another option is to achieve something similar to Presto UNNEST(array_1, > array_2) as t(a1, a2) > {code:sql} > SELECT numbers, animals, n, a > FROM ( > VALUES > (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']), > (ARRAY[7, 8, 9], ARRAY['cow', 'pig']) > ) AS x (numbers, animals) > CROSS JOIN UNNEST(numbers, animals) AS t (n, a){code} > {code} > numbers | animals | n | a > -----------++------------------------+------ > [2, 5] | [dog, cat, bird] | 2 | dog > [2, 5] | [dog, cat, bird] | 5 | cat > [2, 5] | [dog, cat, bird] | NULL | bird > [7, 8, 9] | [cow, pig] | 7 | cow > [7, 8, 9] | [cow, pig] | 8 | pig > [7, 8, 9] | [cow, pig] | 9 | NULL > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)