Error in Dremio Community Edition 24.3.2: Issues with Grouping by Array and ARRAY_AGG Function

1

Hello Dremio Community,

I’ve encountered an issue while executing SQL queries that involve array functions in Dremio Community Edition version 24.3.2. Specifically, when I attempt to execute the following SQL query:

SELECT ARRAY_SIZE(SET_UNION(array_col, ARRAY['item1', 'item2'])) from table_name;

I receive the error message:

Cannot group by an array and aggregate with ARRAY_AGG at the same time.

This issue also occurs when using the ARRAY_INTERSECTION function in a similar context.

Interestingly, when I use the SET_UNION function as described in the Dremio documentation (e.g., SELECT SET_UNION(ARRAY[1, 2, 2, 3], ARRAY[1, 3, 5]);), the query executes successfully and returns the expected result. The issue arises specifically when I specify an array column from my table in the function.

Has anyone else encountered this issue, or does anyone have advice on how to workaround this limitation? Any guidance or recommendations for modifying the query to avoid this error would be greatly appreciated.

Thank you in advance for your help!

2

same error here, also FLATTEN fails if column is empty or null

3

@balaji.ramaswamy Any update on this?

4

@jithin.odattu

Looks like this works

SELECT ARRAY_SIZE(SET_UNION(ARRAY[1, 2, 2, 3], ARRAY[1, 3, 5]));

What version are you on? It could be possible there is a limitation when using it on a column. I assume you have 2 array columns in the dataset?

5

Yes @balaji.ramaswamy The SQL query you have given is working. But I want to use it on my table column which is an array. Similar to the below query…

SELECT ARRAY_SIZE(SET_UNION(my_array_column_name, ARRAY[1, 3, 5]));