Zaskakujące wyniki dla typów danych z modyfikatorem typu

Omawiając rekurencyjne rozwiązanie CTE dla tego pytania:

Uzyskaj ostatnie 5 różnych wartości dla każdego identyfikatora

@ypercube natknął się na zaskakujący wyjątek, który skłonił nas do zbadania sposobu obsługi modyfikatorów typów. Znaleźliśmy zaskakujące zachowanie.

1. Rzutowanie typu zachowuje modyfikator typu w niektórych kontekstach

Nawet jeśli nie polecono. Najbardziej podstawowy przykład:

SELECT 'vc8'::varchar(8)::varchar

Można się spodziewać varchar(bez modyfikatora), przynajmniej ja bym to zrobił. Ale wynik jest varchar(8)(z modyfikatorem). Wiele powiązanych przypadków w skrzypce poniżej.

2. W niektórych kontekstach konkatenacja macierzy traci modyfikator typu

Bez potrzeby, więc to błąd po przeciwnej stronie:

SELECT ARRAY['vc8']::varchar(8)[]
     , ARRAY['vc8']::varchar(8)[] || 'vc8'::varchar(8)

Pierwsze wyrażenie daje varchar(8)[]oczekiwane wyniki.
Ale drugi, po konkatenacji inny varchar(8)jest rozwodniony do samego poziomu varchar[](bez modyfikatora). Podobne zachowanie z array_append()przykładów w skrzypce poniżej.

Wszystko to nie ma znaczenia w większości kontekstów. Postgres nie traci danych, a po przypisaniu do kolumny wartość i tak jest wymuszana na odpowiedni typ. Jednak błąd w przeciwnych kierunkach kończy się zaskakującym wyjątkiem:

3. Rekurencyjne CTE wymaga, aby typy danych były dokładnie dopasowane

Biorąc pod uwagę tę uproszczoną tabelę:

CREATE TABLE a (
  vc8  varchar(8)  -- with modifier
, vc   varchar     -- without  
);
INSERT INTO a VALUES  ('a',  'a'), ('bb', 'bb');

Chociaż ten rCTE działa dla varcharkolumny vc, nie działa dla varchar(8)kolumny vc8:

WITH RECURSIVE cte AS (
   (
   SELECT ARRAY[vc8] AS arr  -- produces varchar(8)[]
   FROM   a
   ORDER  BY vc8
   LIMIT 1
   )

   UNION ALL
   (
   SELECT a.vc8 || c.arr  -- produces varchar[] !!
   FROM   cte c
   JOIN   a ON a.vc8 > c.arr[1]
   ORDER  BY vc8
   LIMIT 1
   )
   )
TABLE  cte;

BŁĄD: zapytanie rekurencyjne „cte” kolumna 1 ma zmienny znak typu (8) [] w nierekurencyjnym terminie, ale ogólnie znak typu różni się []  
Wskazówka: Rzuć dane wyjściowe terminu nierekurencyjnego na poprawny typ. Pozycja: 103

_{Jednym szybkim obejściem byłoby użycie text.}

Zwykłe UNIONzapytanie nie ma tego samego problemu: ustala się na typ bez modyfikatora, co gwarantuje zachowanie wszystkich informacji. Ale rCTE jest bardziej wybredny.

Ponadto, nie napotkasz problemów z częściej używanymi max(vc8)zamiast ORDER BY/ LIMIT 1, ponieważ max()przyjaciele textnatychmiast się zadowolą (lub odpowiedni typ podstawowy bez modyfikatora).

SQL Fiddle demonstrujący 3 rzeczy:

Szereg przykładowych wyrażeń, w tym zaskakujące wyniki.
Prosty rCTE, który działa z varchar(bez modyfikatora).
Ten sam rCTE zgłaszający wyjątek dla varchar(n)(z modyfikatorem).

Skrzypce dotyczą str. 9.3. Otrzymuję te same wyniki lokalnie dla str. 9.4.4.

Utworzyłem tabele z wyrażeń demonstracyjnych, aby móc pokazać dokładny typ danych, w tym modyfikator. Chociaż pgAdmin wyświetla te informacje po wyjęciu z pudełka, nie są one dostępne w sqlfiddle. Co ciekawe, nie jest również dostępny w psql(!). Jest to znane niedociągnięcie w psql i możliwe rozwiązanie zostało wcześniej omówione na temat hakerów pgsql - ale jeszcze nie wdrożone. Może to być jeden z powodów, dla których problem nie został jeszcze wykryty i rozwiązany.

Na poziomie SQL można użyć pg_typeof()do uzyskania typu (ale nie modyfikatora).

pytania

Razem 3 problemy powodują bałagan.
_{Mówiąc ściślej, problem 1. nie jest bezpośrednio zaangażowany, ale rujnuje pozornie oczywistą poprawkę za pomocą obsady w nierekurencyjnym określeniu: ARRAY[vc8]::varchar[]lub podobnym, co zwiększa zamieszanie.}
Który z tych elementów jest błędem, usterką lub po prostu, jak ma być?
Czy coś pomijam, czy powinniśmy zgłosić błąd?

— Erwin Brandstetter
źródło

To z pewnością wydaje się raczej podejrzane. Podejrzewam, że pewna rola może mieć wsteczna kompatybilność istniejących zapytań związkowych.

— Craig Ringer

@CraigRinger: Nie rozumiem, dlaczego konkatenacja macierzy upuszcza modyfikator bez potrzeby, a rzutowanie nie, nawet jeśli jest wymagane. Ani dlaczego rCTE musi być bardziej rygorystyczne (mniej inteligentne) niż proste UNIONzapytania. Czy to możliwe, że znaleźliśmy jednocześnie trzy niezależne małe błędy? (Po miesiącach i miesiącach braku takiego znaleziska.) Który z nich uważasz za błędny?

— Erwin Brandstetter

Wynika to z cech relacji (zdefiniowany w pg_classa pg_attribute, albo zdefiniowana dynamicznie z selectrachunku) wsparcie modyfikatory (przez pg_attribute.atttypmod), podczas gdy parametry funkcyjne nie. Modyfikatory są tracone podczas przetwarzania przez funkcje, a ponieważ wszyscy operatorzy są obsługiwani przez funkcje, modyfikatory są tracone, gdy są przetwarzane również przez operatorów.

Funkcje z wartościami wyjściowymi lub zwracające zestawy rekordów lub równoważne returns table(...)również nie mogą zachować żadnych modyfikatorów zawartych w definicji. Jednak tabele, return setof <type>które zachowają (a właściwie prawdopodobnie typecast) dowolne modyfikatory zdefiniowane typew pg_attribute.

— Ziggy Crueltyfree Zeitgeister
źródło