ORDER BY 1 uvnitř agregátu řadí podle konstanty, ne podle sloupce

import { Aside } from ‘@astrojs/starlight/components’;

V agregátním ORDER BY nikdy nepoužívej ordinály — vytáhni výraz do poddotazu s pojmenovaným sloupcem a řaď podle jména.

Symptom

Kanonizační dotaz (deterministický otisk stavu DB) používal string_agg(CASE … END, ',' ORDER BY 1) s úmyslem řadit podle výsledku CASE. Dotaz prošel bez chyby a na datech s jednou položkou na skupinu vracel „správné” výsledky — takže testy zelené. Na vícepoložkových skupinách by ale pořadí bylo nedeterministické a otisk nestabilní.

Root cause

Na rozdíl od ORDER BY na úrovni SELECTu agregátní ORDER BY čísla výstupních sloupců nepodporuje — položky jsou vždy výrazy. ORDER BY 1 je tedy řazení podle konstanty 1, což neřadí vůbec. PostgreSQL to nehlásí jako chybu.

Fix

-- PŘED (neřadí):
string_agg(CASE WHEN rr = 0 THEN 'PUBLIC' ELSE pr.rolname END, ',' ORDER BY 1)

-- PO (řadí): mezikrok s pojmenovaným sloupcem
(SELECT string_agg(rn.role_name, ',' ORDER BY rn.role_name)
   FROM (SELECT CASE WHEN rr = 0 THEN 'PUBLIC' ELSE pr.rolname END AS role_name
           FROM unnest(polroles) rr LEFT JOIN pg_roles pr ON pr.oid = rr) rn)

Jak se tomu vyvarovat v jiných systémech

• Detection: grep -rn "ORDER BY [0-9])" *.sql uvnitř volání agregátů.
• Anti-pattern: ordinál v agregátním ORDER BY; „testy prošly” na skupinách o jedné položce.
• Lepší přístup: poddotaz s aliasem + řazení podle jména; test s vícepoložkovou skupinou.

Sister bugs / související

Determinismus otisků obecně: výrazová funkce pg_get_expr je závislá na search_path — otisky počítej ve funkci se SET search_path = pg_catalog.