citusdata / citusdata/citus

Some aggregates that takes anyelement type may fail when the input is record

Open
#5,001 5 comments 0 reactions 0 assignees View on GitHub
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

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.