Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-3789

Supports Presto style unnest with items alias

    XMLWordPrintableJSON

    Details

    • Type: New Feature
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 1.21.0
    • Fix Version/s: 1.23.0
    • Component/s: core

      Description

      In Presto, users are able to UNNEST multiple array columns and CROSS JOIN with the original table . As shown in the Presto doc:

      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)
      

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

      It seems Calcite does not have such a feature to support this semantics. In Calcite and for above SQL, n and a will be identified as alias of subfields of numbers.

      The plan will be to introduce a new Presto conformance and enable validation of such SQLs

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                my7ym Will Yu
                Reporter:
                my7ym Will Yu
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 15h 10m
                  15h 10m