sql: cannot apply ARRAY() over correlated subquery

[knz]

Consider the following SQL:

CREATE TABLE u (x INT);
insert into u values(7),(8);
CREATE TABLE v (y INT[]);
insert into v values(array[1,2,3]), (array[4,5,6]);
SELECT ARRAY(SELECT (y, 2) FROM u ORDER BY x) FROM v;

This currently fails in CockroachDB with "can't execute a correlated ARRAY.."

PostgreSQL produces this:

                 array
---------------------------------------
 {"(\"{1,2,3}\",2)","(\"{1,2,3}\",2)"}
 {"(\"{1,2,3}\",2)","(\"{1,2,3}\",2)"}
 {"(\"{4,5,6}\",2)","(\"{4,5,6}\",2)"}
(3 rows)

with the following logical plan:

                                QUERY PLAN
---------------------------------------------------------------------------
 Seq Scan on public.v  (cost=0.00..253197.92 rows=1360 width=32)
   Output: (SubPlan 1)
   SubPlan 1
     ->  Sort  (cost=179.78..186.16 rows=2550 width=36)
           Output: (ROW(v.y, 2)), u.x
           Sort Key: u.x
           ->  Seq Scan on public.u  (cost=0.00..35.50 rows=2550 width=36)
                 Output: ROW(v.y, 2), u.x

``

Jira issue: CRDB-4562

[Emill]

Supporting this would be a good feature when we want to query one or multiple levels of related data, which are usually done using joins. But joins have the disadvantage that a lot of duplicated data is returned. Joins with three or more tables can also be problematic from a performance point of view.

Take this as a simple example:

select *, array(select row(tbl2.*) from tbl2 where tbl1.id = tbl2.tbl1id) from tbl1

This fetches all rows in tbl1, together with the related rows in tbl2, without duplicate data.

I'm working on implementing this in EF Core, see npgsql/efcore.pg#1691.

I also see that array_agg is also not working currently together with the row constructor, so that approach can't be used either.

[Emill]

Seems like the query engine supports it, since casting the row to text first works:

select *, array(select (tbl2.*)::text from tbl2 where tbl1.id = tbl2.tbl1id) from tbl1

so I guess it's just adding support for propagating the plain value (instead of a row materialized as text) that's currently not working.

[knz]

cc @RaduBerinde for re-triage

[RaduBerinde]

We are missing array_agg overloads for tuples (array_agg is used internally when decorrelating such queries).

[jordanlewis]

No longer repros, and we now support array_agg for tuples as well.