ERROR: column "surface.*" must appear in the GROUP BY clause or be used in an aggregate function at character 426
While working on !354 (merged) I stumbled upon this error message which can be triggered only by calling add_border_split_surfaces with unexpected parameters:
ERROR: column "surface.*" must appear in the GROUP BY clause or be used in an aggregate function at character 426
This ticket is to improve the error message to help user fix the input.
Full log:
+psql:add_border_split_surface.sql:2126: DEBUG: topo_update._most_adjacent_surface: SQL
+WITH adjacentFaces AS (
+ -- Find adjacent faces
+ SELECT face_id, sum(len) len
+ FROM (
+ SELECT
+ CASE WHEN left_face = ANY($1) THEN right_face
+ ELSE left_face END face_id,
+ ST_Length(geom) len
+ FROM topo.edge
+ WHERE ( left_face = ANY($1) ) <> ( right_face = ANY($1) )
+ AND left_face > 0
+ AND right_face > 0
+ ) foo
+ GROUP BY face_id
+), adjacentSurfaces AS (
+ SELECT
+ surface.k surface_id,
+ sum(adjFace.len) len,
+ surface surface_record
+ FROM
+ topo.surface2 surface,
+ adjacentFaces adjFace,
+ topo.relation
+ WHERE
+ -- TopoGeometry contains any of the
+ -- adjacentFaces
+ relation.topogeo_id = id(g2)
+ AND relation.layer_id = layer_id(g2)
+ AND relation.element_id = adjFace.face_id
+ GROUP BY surface_id
+)
+SELECT surface_id, surface_record, len border_len
+FROM adjacentSurfaces
+ORDER BY
+ len DESC,
+ -- in case of same shared-edges len, pick Surface with highest id
+ surface_id DESC
+LIMIT 1
+ERROR: column "surface.*" must appear in the GROUP BY clause or be used in an aggregate function at character 426
Seen here: https://gitlab.com/nibioopensource/pgtopo_update_sql/-/jobs/12138078380#L1345
Edited by Sandro Santilli