Kolejność w zapytaniu z DISTINCT ON

Na problem z nieustawioną kolejnością w zapytaniu zawierającym DISTINCT ON natknąłem się już jakiś czas temu, jednak dopiero teraz zebrałem się, aby go opisać. A warto, bo niby błahy, a jednak ciekawy i sam trochę ignorowałem jego znaczenie.

Otóż korzystając w zapytaniu z DISTINCT ON na zestawie kolumn powinniśmy ustawić kolejność prezentacji wierszy poprzez standardową klauzulę ORDER BY. DISTINCT ON pomija bowiem kolejne wiersze o wartościach, które wystąpiły już wcześniej:

DISTINCT ON ( expression [, …] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. […] Note that the “first row” of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. […] The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s).

http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-DISTINCT

Bez ustawionej kolejności może być przypadkowa (chodź technicznie to nie do końca prawda) i różna w zależności od sposobu użycia.

Przykład? Opiszę mój przypadek w nieco zmodyfikowanej formie. Miałem zapytanie korzystające z dwóch tabel: pierwsza to tabela z numerami telefonów klientów wraz z etykietami. Jeden klient może posiadać kilka telefonów, nawet z tymi samymi etykietami (ze względu na przypisywanie tych numerów do jego umów, których może być wiele):

Z tabeli pobierane były unikalne numery telefonów dla klienta wraz z pierwszą etykietą – ta bowiem nie była do końca ważna. Korzystałem więc z zapytania:

SELECT DISTINCT ON (id_customer, number)
    id_customer,
    number,
    label
FROM customers

Nakładając na zapytanie ograniczenie na ID żądanego klienta otrzymywałem wiersze z jego telefonami oraz etykietą. Jeżeli do jednego numeru klienta było kilka etykiet, mogła zostać zaprezentowana dowolna z nich, każda bowiem była prawidłowa i w tym zastosowaniu nie miało to znaczenia.

Trzeba było jednak rozszerzyć tabelę o dodatkowe dane (tagi), które nie występowały – jak etykiety – przy każdym wierszu klienta. Jeżeli już jednak istniały, należało je zaprezentować, a jeżeli było ich kilka – najważniejszą z nich według zadanego priorytetu. Pominę przy analizie priorytet, ponieważ nie jest on aż tak istotny. Tabela po zmianach wyglądała więc tak:

Do zapytania dodałem tylko kolumnę z interesującą mnie daną:

SELECT DISTINCT ON (id_customer, number)
    id_customer,
    number,
    label,
    tag
FROM customers

Przy testach wszystko było OK, zawsze zwracane były wiersze z wypełnioną kolumną tag. To nieco uśpiło moją czujność i nie przetestowałem takiej liczb przypadków, abym trafił na pustą wartość mimo istnienia tagu dla numeru.

Sytuacja zmieniła się, gdy zapytanie trafiło do SQL-owego widoku. To zmieniło sposób sortowania wyników, przez co pobierając dane z widoku miałem przypadki zwracania numerów bez tagów mimo, że te ewidentnie istniały. Żeby było zabawniej, wykonanie zapytania bezpośrednio (z pominięciem widoku) działało tak, jak bym chciał.

Wróciłem więc do dokumentacji i stackoverflow. To naprowadziło mnie na podany na początku wpisu fragment dokumentacji. Aby poprawić zapytanie, należało więc użyć ORDER BY:

SELECT DISTINCT ON (id_customer, number)
    id_customer,
    number,
    label,
    tag
FROM customers
ORDER BY tag NULLS LAST

Dzięki temu, wiersze z tagami były zwracane jako pierwsze i ewentualne późniejsze wiersze z pustymi wartościami w tej kolumnie dla tego samego klienta i numeru były pomijane, a nie odwrotnie.

Nie można więc zapominać o klauzuli ORDER BY po kolumnach niewystępujących w DISTINCT ON, bo prędzej czy później się to zemści.