-
Notifications
You must be signed in to change notification settings - Fork 4.1k
opt: group-by instead of scalar-group-by in subquery #68290
Copy link
Copy link
Closed
Labels
C-bugCode not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.T-sql-queriesSQL Queries TeamSQL Queries Team
Description
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]
Reactions are currently unavailable
Metadata
Metadata
Assignees
Labels
C-bugCode not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.T-sql-queriesSQL Queries TeamSQL Queries Team
Type
Projects
Status
Done