ARRAY_AGG(DISTINCT expr) does not seem to work

1

When running the example from the ARRAY_AGG documentation, with the DISTINCT keyword, I receive an error.

CREATE TABLE $scratch.people (name) AS VALUES ('Bob'), ('Charlie'), ('Alice'), ('Bob');
SELECT ARRAY_AGG(DISTINCT name) FROM $scratch.people;

The error:

Failure while planning to query. Please remove ARRAY_AGG(DISTINCT) and try again.

Server version: 24.3.0-202312220021110714-f19d865e
(from the Docker image)

Am I doing something wrong ?

2

Hey @jnd77

I notice the same in a non-docker 24.3 env, we will get back to you on this.

Thanks, Bogdan

3

Hi @jnd77,

In 24.3 the DISTINCT keyword is available with ARRAY_AGG, but sometimes the planner can generate an invalid plan, which results in the error “Failure while planning to query. Please remove ARRAY_AGG(DISTINCT) and try again.”

The fix (DX-85492) will be in releases v24.3.2 and 25. Please let us know if any questions.

Thanks, Bogdan

4

Hi @bogdan.coman thanks for your reply. I will wait for the release then.