Czy NAPRAWDĘ jest możliwe, że zamówienie nie będzie gwarantowane dla tej konkretnej zbędnej tabeli pochodnej?

Natknąłem się na to pytanie w rozmowie na Twitterze z Lukasem Ederem .

Chociaż poprawnym działaniem byłoby zastosowanie klauzuli ORDER BY w najbardziej zewnętrznym zapytaniu, ponieważ tutaj nie używamy DISTINCT, GROUP BY, JOIN ani żadnej innej klauzuli WHERE w najbardziej zewnętrznym zapytaniu, dlaczego RDBMS po prostu nie przekaże przychodzące dane, które zostały posortowane według wewnętrznego zapytania?

SELECT * 
FROM (
    SELECT * FROM table ORDER BY time DESC
) AS t

Uruchamiając ten przykład na PostgreSQL, otrzymujesz co najmniej ten sam plan wykonania zarówno dla wewnętrznego zapytania, jak i tego przykładu tabeli pochodnej, a także ten sam zestaw wyników.

Zakładam więc, że Planista po prostu odrzuci najbardziej zewnętrzne zapytanie, ponieważ jest ono zbędne lub po prostu przechodzi przez wyniki z wewnętrznej tabeli.

Czy ktoś uważa, że ​​tak nie jest?

Większość baz danych jest dość jasna na temat faktu, że ORDER BYw podzapytaniu jest albo:

• Niedozwolone: ​​np. SQL Server, Sybase SQL Anywhere (chyba że uzupełnione o TOPlub OFFSET .. FETCH)
• Bez znaczenia: np. PostgreSQL, DB2 (ponownie, chyba że uzupełnione o OFFSET .. FETCHlub LIMIT)

Oto przykład z podręcznika DB2 LUW (wyróżnienie moje)

Klauzula ORDER BY w podselekcji nie wpływa na kolejność wierszy zwracanych przez zapytanie. Klauzula ORDER BY wpływa tylko na kolejność zwracanych wierszy, jeśli jest określona w skrajnie pełnym wyborze.

Sformułowanie jest dość wyraźne, podobnie jak PostgreSQL :

Jeśli sortowanie nie zostanie wybrane, wiersze zostaną zwrócone w nieokreślonej kolejności. Rzeczywista kolejność w takim przypadku będzie zależeć od typu skanowania i planu dołączania oraz kolejności na dysku, ale nie można na nim polegać . Szczególne uporządkowanie wyjściowe może być zagwarantowane tylko wtedy, gdy zostanie wyraźnie wybrany krok sortowania.

Z tej specyfikacji można ORDER BYwywnioskować, że każde uporządkowanie wynikające z klauzuli w tabeli pochodnej jest jedynie przypadkowe i może przypadkowo pasować do oczekiwanej kolejności (co robi w większości baz danych w trywialnym przykładzie), ale nierozsądne byłoby polegać na to.

Uwaga dodatkowa na temat DB2:

W szczególności DB2 ma mniej znaną funkcję o nazwieORDER BY ORDER OF <table-designator> , której można użyć w następujący sposób:

SELECT C1 FROM
   (SELECT C1 FROM T1
      UNION
    SELECT C1 FROM T2
    ORDER BY C1 ) AS UTABLE
ORDER BY ORDER OF UTABLE

W tym konkretnym przypadku kolejność tabeli pochodnej może zostać ponownie wyraźnie użyta w najbardziej zewnętrznym SELECT

Uwaga dodatkowa na temat Oracle:

Od lat praktyką w Oracle jest stosowanie OFFSETpaginacji przy użyciu ROWNUM, którą można racjonalnie obliczyć dopiero po zamówieniu tabeli pochodnej:

SELECT *
FROM (
  SELECT rownum AS rn, t.* -- ROWNUM here depends on the derived table's ordering
  FROM (
    SELECT * FROM table ORDER BY time DESC
  ) t
) t
WHERE rn BETWEEN 10 AND 20

Można zasadnie oczekiwać, że przynajmniej w obecności ROWNUMzapytania przyszłe wersje Oracle nie złamią tego zachowania, aby nie złamać prawie całej dotychczasowej wersji Oracle SQL, która nie została jeszcze przeniesiona do znacznie bardziej pożądanej i czytelna standardowa OFFSET .. FETCHskładnia SQL :

SELECT * FROM table ORDER BY time DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

Tak. Bez ORDER BYklauzuli kolejność wyjściowa jest niezdefiniowana, a planista zapytań mieści się w zakresie jego kompetencji, aby założyć, że znasz to i rozumiesz.

Może zdecydować, że ponieważ zapytanie zewnętrzne nie określa kolejności, może porzucić porządek w zapytaniu wewnętrznym, aby uniknąć operacji sortowania, szczególnie jeśli nie ma indeksu klastrowego lub w ogóle indeksu do obsługi porządkowania. Jeśli nie teraz , może to zrobić w przyszłych wersjach.

Nigdy nie polegaj na nieokreślonym zachowaniu. Jeśli potrzebujesz konkretnego zamówienia, podaj ORDER BYklauzulę w odpowiednim miejscu.

To właśnie problem z nieokreślonym zachowaniem - działa dla ciebie, działa dla mnie, formatuje HDD w prod;)

Możemy cofnąć się o krok i powiedzieć, że w pewnym sensie masz rację - nie ma ziemskiego powodu, dla którego rozsądny system RDBMS zmieniłby rzędy w wewnętrznej selekcji. Ale nie jest to gwarantowane - co oznacza, że ​​w przyszłości może być jakiś powód, a sprzedawcy mogą to zrobić. Oznacza to, że każdy kod, który opiera się na tym zachowaniu, jest zdany na zmianę, którą może wprowadzić sprzedawca, którego nie byłby zobowiązany do opublikowania, ponieważ nie jest to przełomowa zmiana w stosunku do POV API.

Czy NAPRAWDĘ jest możliwe, że zamówienie nie będzie gwarantowane dla tej konkretnej zbędnej tabeli pochodnej?

Odpowiedź dla wszystkich obecnie istniejących wersji Postgres (które testowałeś) brzmi: Nie - dla tego konkretnego zapytania. Kolejność sortowania jest gwarantowana.

Ludzie korzystający z serwera SQL nie będą się z tym czuć komfortowo, ponieważ Microsoft nawet nie zezwala ORDER BYna podzapytania. Mimo to kolejność sortowania jest gwarantowana w przypadku tego prostego zapytania w Postgres. ORDER BYjest stosowane w podzapytaniu, a zapytanie zewnętrzne nie robi niczego, co mogłoby zmienić kolejność.

Podręcznik nawet wskazuje tyle samo w rozdziale Funkcje agregujące :

Alternatywnie, podanie wartości wejściowych z posortowanego podzapytania zwykle będzie działać.

Zauważ, że jest to prawdą tylko wtedy, gdy zewnętrzne poziomy zapytań nie dodają operacji, które mogłyby zmienić kolejność. Jest to więc „gwarantowane” tylko w prostym przypadku i nie jest ono wspierane przez standard SQL. Postgres może bezpłatnie zmieniać kolejność, jeśli jest to odpowiednie do dodatkowych operacji. W razie wątpliwości dodaj kolejną ORDER BYdo zewnętrznej SELECT. (W takim przypadku wewnętrzny ORDER BYdźwięk byłby nadmiarowy dla tego prostego zapytania).