Some aggregates that takes anyelement type may fail when the input is record
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
```SQL
CREATE TABLE t1 (id bigserial, geom geometry(Polygon, 3857), catetitle varchar(32));
SELECT create_distributed_table('t1', 'id');
INSERT INTO t1 (catetitle, geom) SELECT i::text, 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))' FROM generate_series(0,1000)i;
CREATE OR REPLACE VIEW layer_view_test AS
SELECT id, COALESCE(NULLIF('catetitle'::text,''), 'NULL') AS catetitle, 1000001 AS layer_id, geom FROM t1
UNION ALL
SELECT id, COALESCE(NULLIF('catetitle'::text,''), 'null') AS catetitle, 1000002 AS layer_id, geom FROM t1
UNION ALL
SELECT id, COALESCE(NULLIF('catetitle'::text,''), 'NULL') AS catetitle, 1000003 AS layer_id, geom FROM t1
UNION ALL
SELECT id, COALESCE(NULLIF('catetitle'::text,''), 'null') AS catetitle, 1000004 AS layer_id, geom FROM t1
UNION ALL
SELECT id, 'NULL' AS catetitle, 1000810 AS layer_id, geom FROM t1;
SELECT
catetitle ,
count(*) ,
public.ST_AsGeoBuf(
feature
)
FROM
(
SELECT
catetitle,
geom
FROM layer_view_test
)feature group by catetitle LIMIT 1;
ERROR: input of anonymous composite types is not implemented
```
The current workaround is to materialize results via `OFFSET 0` trick:
```SQL
SELECT
catetitle ,
count(*) ,
public.ST_AsGeoBuf(
feature
)
FROM
(
SELECT
catetitle,
geom
FROM layer_view_test OFFSET 0
)feature group by catetitle LIMIT 1;
```
OR with pg12+, MATERIALIZED CTEs are nicer
```SQL
WITH feature AS MATERIALIZED (
SELECT
catetitle,
geom
FROM layer_view_test
)
SELECT
catetitle ,
count(*) ,
public.ST_AsGeoBuf(
feature
)
FROM feature
group by catetitle
LIMIT 1;
```
As the worker query contains something like `, feature.*::record AS worker_column_2`:
related to #3191 as we need to call the same function
Contributor guide
Assessment
This issue has not been assessed yet.