opt: bug in aggregate scoping rules

[rytaft]

Consider the following example:

CREATE TABLE testing (name text);
insert into testing values ('a'), ('b'), ('c');

We can create an array from these values successfully, but then when we try to unnest the array, the optimizer returns a cryptic error:

demo@127.0.0.1:26257/movr> SELECT string_to_array(string_agg(name, ' '), ' ') FROM testing;
         string_to_array
---------------------------------
  {a,b,c}
(1 row)

demo@127.0.0.1:26257/movr> SELECT unnest(string_to_array(string_agg(name, ' '), ' ')) FROM testing;
ERROR: unnest(): string_to_array(): string_agg(): aggregate functions are not allowed in SELECT
SQLSTATE: 42803

This works fine in Postgres, however:

postgres=# SELECT unnest(string_to_array(string_agg(name, ' '), ' ')) FROM testing;
 unnest 
--------
 a
 b
 c
(3 rows)

While we fix this bug, users can do the following as a workaround:

SELECT unnest(x.name_agg) FROM (select string_to_array(string_agg(name, ' '), ' ') AS name_agg FROM testing) x;

Jira issue: CRDB-3335

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!