Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-7452

Support comparison operator for Array

Attach filesAttach ScreenshotAdd voteVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Wish
    • Status: Open
    • Major
    • Resolution: Unresolved
    • 1.16.0
    • None
    • Functions - Drill
    • None

    Description

      It will be useful to have a comparison operator for nested types, at less for Array.
      sample file in attachment : example_array.parquet

      /* It's possible to do */
      apache drill(1.16)> SELECT id, tags FROM ....`example_array.parquet`;
      +--------+------------+
      |   id   |    tags    |
      +--------+------------+
      | 7b8808 | [1,2,3]    |
      | 7b8808 | [1,20,3]   |
      | 55a4be | [1,3,5,6]  |
      +--------+------------+
      
      /* But it's not possible to use DISTINCT or ORDER BY on the field Tags (ARRAY) */
      /* https://drill.apache.org/docs/nested-data-limitations/ */
      apache drill(1.16)> SELECT DISTINCT id, tags FROM ....`example_array_parquet` ORDER BY tags;
      Error: SYSTEM ERROR: UnsupportedOperationException: Map, Array, Union or repeated scalar type should not be used in group by, order by or in a comparison operator. Drill does not support compare between BIGINT:REPEATED and BIGINT:REPEATED.
      

      It's possible to do that in Postgres

      SELECT DISTINCT id, tags
      FROM
      (
      SELECT '7b8808' AS id, ARRAY[1,2,3] tags
      UNION SELECT '7b8808', ARRAY[1,20,3]
      UNION SELECT '55a4be', ARRAY[1,3,5,6]
      ) x
      ORDER BY tags
      7b8808;{1,2,3}
      55a4be;{1,3,5,6}
      7b8808;{1,20,3}
      

      Attachments

        Activity

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

          People

            Unassigned Unassigned
            benj641 benj

            Dates

              Created:
              Updated:

              Slack

                Issue deployment