Dlaczego zapytania są analizowane w taki sposób, że w większości klauzul nie można używać aliasów kolumn?

Próbując napisać zapytanie, dowiedziałem się (na poważnie), że SQL Server analizuje GDZIE w zapytaniu na długo przed parsowaniem instrukcji SELECT podczas wykonywania zapytania.

Dokumenty MSDN mówią, że ogólna logiczna kolejność analizowania jest taka, że ​​SELECT jest analizowany prawie na końcu (co powoduje błędy „brak takiego obiektu [alias]” podczas próby użycia aliasu kolumny w innych klauzulach). Pojawiła się nawet sugestia, aby zezwolić na stosowanie aliasów w dowolnym miejscu, co zostało zestrzelone przez zespół Microsoft, powołując się na problemy ze zgodnością ze standardami ANSI (co sugeruje, że takie zachowanie jest częścią standardu ANSI).

Jako programista (nie DBA) uważam, że to zachowanie jest nieco mylące, ponieważ wydaje mi się, że w dużej mierze przeczy to celowi posiadania aliasów kolumn (a przynajmniej aliasów kolumn można by znacznie zwiększyć, gdyby były przeanalizowane wcześniej podczas wykonywania zapytania), ponieważ jedynym miejscem, w którym można faktycznie używać aliasów, jest ORDER BY. Jako programista wydaje się, że brakuje mu ogromnej okazji do uczynienia zapytań bardziej wydajnymi, wygodnymi i DRY.

Wygląda na to, że jest to tak rażący problem, że ma uzasadnienie, że istnieją inne powody, aby zdecydować, że aliasy kolumn nie powinny być dozwolone w niczym innym niż WYBIERZ i ZAMÓW, ale jakie są te powody?

streszczenie

Nie ma logicznego powodu, dla którego nie można tego zrobić, ale korzyść jest niewielka i istnieją pewne pułapki, które mogą nie być natychmiast widoczne.

Winiki wyszukiwania

Przeprowadziłem badania i znalazłem dobre informacje. Poniżej znajduje się bezpośredni cytat z wiarygodnego źródła pierwotnego (które chce pozostać anonimowy) o 08.08.2012 17:49 GMT:

Kiedy SQL został wymyślony po raz pierwszy, nie zawierał aliasów w klauzuli SELECT. Było to poważne niedociągnięcie, które zostało naprawione, gdy język został znormalizowany przez ANSI w około 1986 roku.

Język miał być „nieprocesowy” - innymi słowy, aby opisywać potrzebne dane bez określania, jak je znaleźć. O ile wiem, nie ma powodu, dla którego implementacja SQL nie mogła przeanalizować całego zapytania przed przetworzeniem go i pozwolić, aby aliasy były definiowane w dowolnym miejscu i używane wszędzie. Na przykład nie widzę żadnego powodu, dla którego poniższe zapytanie powinno być nieprawidłowe:

select name, salary + bonus as pay
from employee
where pay > 100000

Chociaż myślę, że jest to rozsądne zapytanie, niektóre systemy oparte na SQL mogą wprowadzać ograniczenia dotyczące używania aliasów z pewnych powodów związanych z implementacją. Nie jestem zaskoczony, że SQL Server to robi.

Interesują mnie dalsze badania nad standardem SQL-86 i dlaczego współczesne DBMS nie obsługują ponownego użycia aliasu, ale nie miałem jeszcze czasu, aby się z tym za daleko posunąć. Na początek nie wiem, skąd wziąć dokumentację ani jak dowiedzieć się, kto dokładnie utworzył komitet. Czy ktoś może pomóc? Chciałbym również dowiedzieć się więcej o oryginalnym produkcie Sybase, z którego pochodzi SQL Server.

Na podstawie tych badań i dalszych przemyśleń zacząłem podejrzewać, że stosowanie aliasów w innych klauzulach, choć całkiem możliwe, po prostu nigdy nie było tak wysokim priorytetem dla producentów DBMS w porównaniu z innymi funkcjami językowymi. Ponieważ nie jest to zbyt duża przeszkoda, ponieważ autor łatwego do obejścia problemu łatwo go obejść, włożenie w to wysiłku nad innymi osiągnięciami nie jest optymalne. Dodatkowo byłby zastrzeżony, ponieważ oczywiście nie jest częścią standardu SQL (choć na pewno czekam, aby dowiedzieć się więcej na ten temat), a zatem byłby drobnym ulepszeniem, łamiącym zgodność SQL między DBMS. W stosunku,CROSS APPLY (która tak naprawdę nie jest niczym więcej niż tabelą pochodną, ​​która pozwala na zewnętrzne odniesienia), jest ogromną zmianą, która, choć własna, oferuje niesamowitą moc ekspresji, której nie można łatwo wykonać w inny sposób.

Problemy z używaniem aliasów w dowolnym miejscu

Jeśli zezwolisz na umieszczanie elementów SELECT w klauzuli WHERE, możesz nie tylko rozwikłać złożoność zapytania (a tym samym złożoność znalezienia dobrego planu wykonania), ale możesz wymyślić zupełnie nielogiczne rzeczy. Próbować:

SELECT X + 5 Y FROM MyTable WHERE Y = X

Co jeśli MyTable ma już kolumnę Y, do której odnosi się klauzula WHERE? Rozwiązaniem jest użycie CTE lub tabeli pochodnej, która w większości przypadków nie powinna kosztować więcej, ale osiąga ten sam końcowy wynik. CTE i tabele pochodne przynajmniej wymuszają rozwiązanie niejednoznaczności, umożliwiając użycie aliasu tylko raz.

Również nieużywanie aliasów w klauzuli FROM ma sens. Nie możesz tego zrobić:

SELECT
   T3.ID + (SELECT Min(Interval) FROM Intervals WHERE IntName = 'T') CalcID
FROM
   Table1 T
   INNER JOIN Table2 T2
      ON T2.ID = CalcID
   INNER JOIN Table3 T3
      ON T2.ID = T3.ID

Jest to cykliczne odniesienie (w tym sensie, że T2 potajemnie odnosi się do wartości z T3, zanim ta tabela została przedstawiona na liście JOIN) i jest cholernie trudne do zobaczenia. Co powiesz na ten:

INSERT dbo.FinalTransaction
SELECT
   newid() FinalTransactionGUID,
   'GUID is: ' + Convert(varchar(50), FinalTransactionGUID) TextGUID,
   T.*
FROM
   dbo.MyTable T

Ile chcesz się założyć, że funkcja newid () zostanie dwukrotnie umieszczona w planie wykonania, zupełnie nieoczekiwanie powodując, że dwie kolumny będą miały różne wartości? Co powiesz na to, kiedy powyższe zapytanie zostanie użyte N poziomy głęboko w CTE lub tabelach pochodnych. Gwarantuję, że problem jest gorszy, niż możesz sobie wyobrazić. Istnieją już poważne problemy z niespójnością dotyczące tego, kiedy rzeczy są oceniane tylko raz lub w jakim momencie w planie zapytań, a Microsoft powiedział, że nie naprawiniektóre z nich, ponieważ poprawnie wyrażają algebrę zapytań - jeśli otrzymamy nieoczekiwane wyniki, podziel zapytanie na części. Zezwolenie na łańcuchowe odniesienia, wykrywanie cyklicznych odniesień poprzez potencjalnie bardzo długie takie łańcuchy - to dość skomplikowane problemy. Po wprowadzeniu paralelizmu powstaje koszmar.

Uwaga: użycie aliasu w GDZIE lub GROUP BY nie zmieni problemów z funkcjami takimi jak newid () lub rand ().

Sposób SQL Server do tworzenia wyrażeń wielokrotnego użytku

ZASTOSOWANIE KRZYŻOWE / ZASTOSOWANIE ZEWNĘTRZNE jest jednym ze sposobów SQL Server do tworzenia wyrażeń, których można użyć w dowolnym miejscu zapytania (tylko nie wcześniej w klauzuli FROM):

SELECT
   X.CalcID
FROM
   Table1 T
   INNER JOIN Table3 T3
      ON T.ID = T3.ID
   CROSS APPLY (
      SELECT
         T3.ID + (SELECT Min(Interval) FROM Intervals WHERE IntName = 'T') CalcID
   ) X
   INNER JOIN Table2 T2
      ON T2.ID = X.CalcID

To robi dwie rzeczy:

1. Sprawia, że ​​wszystkie wyrażenia w aplikacji CROSS APPLY uzyskują „przestrzeń nazw” (alias tabeli, tutaj, X) i są unikalne w obrębie tej przestrzeni nazw.
2. Sprawia, że ​​wszędzie widać nie tylko, że CalcID pochodzi z X, ale także wyjaśnia, dlaczego nie można używać niczego z X podczas dołączania do tabel T1 i T3, ponieważ X nie został jeszcze wprowadzony.

Właściwie bardzo lubię CROSS APPLY. Stał się moim wiernym przyjacielem i używam go cały czas. Potrzebujesz częściowego UNPIVOT (który wymagałby PIVOT / UNPIVOT lub UNPIVOT / PIVOT przy użyciu natywnej składni)? Zrobione z APLIKACJĄ KRZYŻOWĄ. Potrzebujesz obliczonej wartości, która będzie wielokrotnie używana? Gotowy. Potrzebujesz sztywno egzekwować polecenie wykonania dla połączeń przez połączony serwer? Zrobione z krzyczącą poprawą prędkości. Potrzebujesz tylko jednego rodzaju podziału wiersza na 2 wiersze lub z dodatkowymi warunkami? Gotowy.

Przynajmniej w DBMS SQL Server 2005 i nowszych nie masz już żadnych powodów do narzekań: CROSS APPLY to sposób SUSZENIA w sposób, w jaki chcesz.

Nie potrafię podać dokładnych powodów, ale powiem ci, że istnieją sposoby obejścia powtarzania wyrażeń, na przykład użycie CTE, podzapytań, tabel pochodnych itp., Aby uniknąć powtórzeń.

Jeśli wyświetlisz zapytanie z powtarzającym się wyrażeniem, prawdopodobnie będziemy w stanie pokazać Ci, jak je ponownie napisać, aby wyrażenie było wymienione tylko raz. Jednak to po prostu zmniejsza złożoność pisania / czytania zapytania, mało prawdopodobne jest, aby zmieniło się znacznie w zakresie wydajności. SQL Server ogólnie dobrze radzi sobie z rozpoznawaniem powtarzających się wyrażeń i nie wykonuje tej pracy dwa razy. Są wyjątki, które idą w drugą stronę, ale powinieneś martwić się o efektywność tylko wtedy, gdy faktycznie to obserwujesz. Podejrzewam, że większość powtarzanych wyrażeń, które piszesz, są naprawdę sprowadzone do jednej operacji w planie.

To powiedziawszy, powtórzę również część mojej odpowiedzi z tego pytania:

/dba/19762/why-is-the-select-clause-listed-first

Oto wyjaśnienie Joe Celko, w jaki sposób zapytanie jest przetwarzane zgodnie ze standardem (ukradłem to z własnego artykułu aspfaq.com , który ukradł cytat prawdopodobnie z postu grupy dyskusyjnej Celko):

Oto jak SELECT działa w SQL ... przynajmniej w teorii. Prawdziwe produkty zoptymalizują rzeczy, kiedy będą w stanie.

Zacznij od klauzuli FROM i zbuduj tabelę roboczą ze wszystkich złączeń, związków, skrzyżowań i innych konstruktorów tabel. Opcja AS pozwala nazwać tę tabelę roboczą, której następnie należy użyć do reszty zawierającego zapytanie.

Przejdź do klauzuli WHERE i usuń wiersze, które nie spełniają kryteriów; to znaczy, że nie testują na PRAWDA (odrzucają NIEZNANE i FAŁSZ). Klauzula WHERE jest stosowana do pracy w klauzuli FROM.

Przejdź do opcjonalnej klauzuli GROUP BY, utwórz grupy i zredukuj każdą grupę do jednego wiersza, zastępując oryginalny stół roboczy nowym stołem zgrupowanym. Wiersze zgrupowanej tabeli muszą być cechami grupy: (1) kolumna grupująca (2) statystyka dotycząca grupy (tj. Funkcje agregujące) (3) funkcja lub (4) wyrażenie złożone z tych trzech elementów.

Przejdź do opcjonalnej klauzuli HAVING i zastosuj ją względem zgrupowanego stołu roboczego; jeśli nie było klauzuli GROUP BY, potraktuj całą tabelę jako jedną grupę.

Przejdź do klauzuli SELECT i konstruuj wyrażenia na liście. Oznacza to, że podkwerendy skalarne, wywołania funkcji i wyrażenia w instrukcji SELECT są wykonywane po wykonaniu wszystkich pozostałych klauzul. Operator AS może również nadać nazwę wyrażeniom z listy SELECT. Te nowe nazwy powstają od razu, ale po wykonaniu klauzuli WHERE; z tego powodu nie można ich używać na liście WYBIERZ lub cluase GDZIE.

Zagnieżdżone wyrażenia zapytań są zgodne ze zwykłymi zasadami określania zakresu, których można oczekiwać od języka o strukturze blokowej, takiego jak C, Pascal, Algol itp. Mianowicie, najbardziej wewnętrzne zapytania mogą odwoływać się do kolumn i tabel w zapytaniach, w których są zawarte.

Oznacza to, że WYBÓR nie może mieć więcej kolumn niż GROUP BY; ale z pewnością może mieć mniej kolumn.

Teraz Celko była jednym z głównych autorów wcześniejszych wersji standardów. Nie wiem, czy kiedykolwiek uzyskasz ostateczną odpowiedź na WHY?pytanie, z wyjątkiem spekulacji. Domyślam się, że najpierw wyświetlenie rzeczywistej operacji bardzo ułatwia analizatorowi składni dokładne określenie, jaki będzie typ operacji. Wyobraź sobie złączenie na 20 tabel, które może ostatecznie być SELECTlub UPDATElub DELETE, i pamiętaj, że kod dla tych silników został pierwotnie zapisany w czasach, gdy parsowanie łańcuchów było dość kosztowne.

Należy pamiętać, że jeśli standard SQL podyktowany FROMjako pierwszy, dostawcy mogą niezależnie zdecydować się na parsowanie gramatyki w innej kolejności, więc nadal nie ma sensu oczekiwać kolejności zapisanych klauzul, aby całkowicie przestrzegać kolejności przetwarzania 100% czas.

To samo dotyczy rzeczy takich jak CASE. Widzieliśmy tutaj scenariusze na tej stronie , na przykład, w których mit, który CASEzawsze przetwarza w porządku i zwarciach, jest fałszywy. Dotyczy to również innych powszechnych przekonań, takich jak SQL Server oceniający złączenia w kolejności, w jakiej zostały napisane, klauzule zwarciowe WHEREod lewej do prawej lub przetwarzanie CTE raz lub w określonej kolejności, nawet jeśli odwołuje się do nich wiele razy. Produkty mogą dowolnie optymalizować sposób, w jaki uznają za odpowiedni, nawet jeśli nie odzwierciedla to dokładnie tego, jak podałeś, że zapytanie powinno działać deklaratywnie.

W Entity SQL możesz MOŻE używać aliasów z wyrażeń w innych miejscach zapytania w niektórych sytuacjach:

select k1, count(t.a), sum(t.a)
from T as t
group by t.b + t.c as k1

Zauważ, że tutaj MUSISZ zdefiniować wyrażenie w GROUP BYklauzuli, aby użyć go w SELECTklauzuli.

Oczywiście możliwe jest zezwolenie na tego rodzaju wyrażenia alias-as-wielokrotnego użytku w zapytaniach SQL.