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

Support comparison operator for Array

    XMLWordPrintableJSON

    Details

    • Type: Wish
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.16.0
    • Fix Version/s: None
    • Component/s: Functions - Drill
    • Labels:
      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

        1. example_array.parquet
          0.5 kB
          benj

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              benj641 benj
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: