cockroachdb / cockroachdb/cockroach

opt: incorrect nested tuple within array_agg

Open
#115,150 0 comments 0 reactions 0 assignees View on GitHub
A-sql-pgcompat C-enhancement P-3 T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

Postgres wraps a table star-select expression within an `array_agg` in a tuple, but doesn't add an extra tuple if explicit `ROW()` syntax is added:
```
postgres=# select array_agg(xy.*) from xy;
array_agg
-----------------------
{"(1,2)","(100,100)"}
(1 row)

postgres=# select array_agg(ROW(xy.*)) from xy;
array_agg
-----------------------
{"(1,2)","(100,100)"}
(1 row)

postgres=# select array_agg(ROW(ROW(xy.*))) from xy;
array_agg
-----------------------------------
{"(\"(1,2)\")","(\"(100,100)\")"}
(1 row)
```
CRDB instead produces an array with nested-tuple elements for the query with a single `ROW()`:
```
root@localhost:26257/defaultdb> select array_agg(xy.*) from xy;
array_agg
-------------------------
{"(1,2)","(100,100)"}
(1 row)

Time: 3ms total (execution 2ms / network 0ms)

root@localhost:26257/defaultdb> select array_agg(ROW(xy.*)) from xy;
array_agg
-------------------------------------
{"(\"(1,2)\")","(\"(100,100)\")"}
(1 row)

Time: 2ms total (execution 2ms / network 0ms)

root@localhost:26257/defaultdb> select array_agg(ROW(ROW(xy.*))) from xy;
array_agg
---------------------------------------------------------
{"(\"(\"\"(1,2)\"\")\")","(\"(\"\"(100,100)\"\")\")"}
(1 row)

Time: 3ms total (execution 3ms / network 0ms)
```

Jira issue: CRDB-33913

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.