[jira] [Created] (CALCITE-3787) Revisit Sql To Rel for a chained UNNEST

["Will Yu (Jira)" <ji...@apache.org>]

Will Yu created CALCITE-3787:
--------------------------------

             Summary: 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


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)