[jira] [Created] (DRILL-7452) Support comparison operator for Array

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

benj created DRILL-7452:
---------------------------

             Summary: Support comparison operator for Array
                 Key: DRILL-7452
                 URL: https://issues.apache.org/jira/browse/DRILL-7452
             Project: Apache Drill
          Issue Type: Wish
          Components: Functions - Drill
    Affects Versions: 1.16.0
            Reporter: benj
         Attachments: example_array.parquet

It will be useful to have a comparison operator for nested types,  at less for Array.
sample file in attachment : example_array.parquet
{code:sql}
/* 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.
{code}

It's possible to do that in Postgres
{code:sql}
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}
{code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)