Skip to content

opt: incorrect nested tuple within array_agg #115150

Open
#174773
Open
opt: incorrect nested tuple within array_agg#115150
#174773
Labels
A-sql-pgcompatSemantic compatibility with PostgreSQLC-enhancementSolution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)P-3Issues/test failures with no fix SLAT-sql-queriesSQL Queries Team

Description

@DrewKimball
DrewKimball
opened on Nov 28, 2023

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

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    A-sql-pgcompatSemantic compatibility with PostgreSQLC-enhancementSolution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)P-3Issues/test failures with no fix SLAT-sql-queriesSQL Queries Team

    Type

    No type

    Projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions