Skip to content

opt: group-by instead of scalar-group-by in subquery #68290

@RaduBerinde

Description

@RaduBerinde

See the example below; the query is a scalar group by (because of json_agg) but for some reason it is being built as a group by on the outer column.

build
SELECT
  (
    SELECT
      CASE
      WHEN COALESCE(array_length(t.arr, 1), 0) = 0 THEN '[]'::JSONB
      ELSE json_agg(elem)
      END
    FROM
      unnest(t.arr) AS elem
  )
FROM
  (SELECT ARRAY[]::STRING[] AS arr) AS t
----
project
 ├── columns: json_agg:6
 ├── cardinality: [1 - 1]
 ├── immutable
 ├── key: ()
 ├── fd: ()-->(6)
 ├── project
 │    ├── columns: arr:1!null
 │    ├── cardinality: [1 - 1]
 │    ├── key: ()
 │    ├── fd: ()-->(1)
 │    ├── values
 │    │    ├── cardinality: [1 - 1]
 │    │    ├── key: ()
 │    │    └── ()
 │    └── projections
 │         └── ARRAY[] [as=arr:1]
 └── projections
      └── subquery [as=json_agg:6, outer=(1), immutable, correlated-subquery]
           └── max1-row
                ├── columns: json_agg:5
                ├── error: "more than one row returned by a subquery used as an expression"
                ├── outer: (1)
                ├── cardinality: [0 - 1]
                ├── immutable
                ├── key: ()
                ├── fd: ()-->(5)
                └── project
                     ├── columns: json_agg:5
                     ├── outer: (1)
                     ├── cardinality: [0 - 1]
                     ├── immutable
                     ├── key: ()
                     ├── fd: ()-->(5)
                     ├── group-by
                     │    ├── columns: json_agg:3 arr:4
                     │    ├── grouping columns: arr:4
                     │    ├── outer: (1)
                     │    ├── cardinality: [0 - 1]
                     │    ├── immutable
                     │    ├── key: ()
                     │    ├── fd: ()-->(3,4)
                     │    ├── project
                     │    │    ├── columns: arr:4 unnest:2
                     │    │    ├── outer: (1)
                     │    │    ├── immutable
                     │    │    ├── fd: ()-->(4)
                     │    │    ├── project-set
                     │    │    │    ├── columns: unnest:2
                     │    │    │    ├── outer: (1)
                     │    │    │    ├── immutable
                     │    │    │    ├── values
                     │    │    │    │    ├── cardinality: [1 - 1]
                     │    │    │    │    ├── key: ()
                     │    │    │    │    └── ()
                     │    │    │    └── zip
                     │    │    │         └── unnest(arr:1) [outer=(1), immutable]
                     │    │    └── projections
                     │    │         └── arr:1 [as=arr:4, outer=(1)]
                     │    └── aggregations
                     │         └── json-agg [as=json_agg:3, outer=(2)]
                     │              └── unnest:2
                     └── projections
                          └── CASE WHEN COALESCE(array_length(arr:1, 1), 0) = 0 THEN '[]' ELSE json_agg:3 END [as=json_agg:5, outer=(1,3), immutable]

Metadata

Metadata

Assignees

Labels

C-bugCode not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.T-sql-queriesSQL Queries Team

Type

No type

Projects

Status

Done

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions