[DRILL-7452] Support comparison operator for Array

• Type: Wish
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.16.0
• Component/s: Functions - Drill
• Assignee: Unassigned
• Reporter: benj
• Created: 21/Nov/19 09:56
• Updated: 21/Nov/19 09:56

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}