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

Supports Presto style unnest with items alias

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • New Feature
    • Status: Closed
    • Minor
    • Resolution: Fixed
    • 1.21.0
    • 1.23.0
    • 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

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            my7ym Will Yu
            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

                Slack

                  Issue deployment