Následující ukázka prezentuje problém, který vznikl při vyhodnocování dotazu, který spojuje data z několika tabulek a současně provádí agregované výpočty, viz obr. 1. Hlavním zdrojem dat je tabulka "Akce", k ní jsou vázány záznamy z tabulek "Akce podklady" a "Čerpání", obě vazbou 1:N a v obou se často skutečně vyskytuje několik záznamů pro jednu akci. Ostatní tabulky obsahují vždy jediný záznam odpovídající akci a problémy nezpůsobují.
Při spojení záznamů je provedena volba "Unique Records", aby se zamezilo opakování záznamů na vstupu. Spojení zdrojových záznamů komplikuje spojování více záznamů z tabulek "Akce podklady" a "Čerpání". Po spojení záznamů je proveden výpočet součtu čerpání (položka z tabulky "Čerpání"). Výsledkem je následující hodnota:
23 | Objednávky | 3520007/97 | 10 004,00 Kč | 85 400,00 Kč |
Jedině rozborem zdrojových záznamů zjistíme, že vypočtená částka
je rovna čtyřnásobku skutečné částky (21 350,- Kč). To koresponduje
se skutečností, že k této akci náležely 4 záznamy v tabulce
"Akce podklady"
SELECT DISTINCTROW Akce.ID, Akce.Ucel, IIf([Akce].[Ucel]="Vyslání" Or [Akce].[Ucel]="Přijetí",[Akce].[Podminky],[Akce].[Cislo]) AS Cislo, Akce.Datum, IIf([Akce].[Ucel]="Vyslání" Or [Akce].[Ucel]="Přijetí", [Akce].[Stat],[Dodavatele].[Nazev]) AS Nazev, Dodavatele.Specifikace, Akce.Podminky, Uhrady.Uhrada, Uhrady.Cislo AS UhradaCislo, Akce.DruhProst, Akce.Vyrizuje, Akce.PlanCena AS Planovano, Sum(Cerpani.Castka) AS Cerpano FROM (Uhrady RIGHT JOIN ((Dodavatele RIGHT JOIN Akce ON Dodavatele.ID = Akce.Dodavatel) LEFT JOIN Cerpani ON Akce.ID = Cerpani.Akce) ON Uhrady.ID = Akce.Uhrada) LEFT JOIN AkcePodklady ON Akce.ID = AkcePodklady.Akce GROUP BY Akce.ID, Akce.Ucel, IIf([Akce].[Ucel]="Vyslání" Or [Akce].[Ucel]="Přijetí",[Akce].[Podminky],[Akce].[Cislo]), Akce.Datum, IIf([Akce].[Ucel]="Vyslání" Or [Akce].[Ucel]="Přijetí", [Akce].[Stat],[Dodavatele].[Nazev]), Dodavatele.Specifikace, Akce.Podminky, Uhrady.Uhrada, Uhrady.Cislo, Akce.DruhProst, Akce.Vyrizuje, Akce.PlanCena, Akce.Stav HAVING ((Akce.Stav=1));
Problém vyřešíme rozdělením dotazu na dvě části. Na obr. 2 je zobrazen přípravný dotaz, který pouze spojuje zdrojová data. Jeho výsledkem je však poněkud překvapivě pouze 6 záznamů, odpovídajících 6 záznamům v tabulce "Čerpání".
23 | Objednávky | 3520007/97 | 10 004,00 Kč | 4 064,00 Kč |
23 | Objednávky | 3520007/97 | 10 004,00 Kč | 1 256,00 Kč |
23 | Objednávky | 3520007/97 | 10 004,00 Kč | 2 824,00 Kč |
23 | Objednávky | 3520007/97 | 10 004,00 Kč | 7 278,00 Kč |
23 | Objednávky | 3520007/97 | 10 004,00 Kč | 3 766,00 Kč |
23 | Objednávky | 3520007/97 | 10 004,00 Kč | 2 162,00 Kč |
Výsledek vstoupí do dotazu, který provede výpočet součtu, viz obr. 3. Součet je pak samozřejmě správný:
23 | Objednávky | 3520007/97 | 10 004,00 Kč | 21 350,00 Kč |
Dosažení správného výsledku je sice potěšující, nicméně důvod
nesprávného výpočtu dotazu z obr. 1 je tímto výsledkem značně
zpochybněn. Pokud byl nesprávný globální dotaz, mělo by být nesprávné
i druhé řešení. Vysvětlením je pouze průběh realizace dotazu a
jeho optimalizace, které způsobují nesprávný výsledek.
SELECT DISTINCTROW Akce.ID, Akce.Ucel, IIf([Akce].[Ucel]="Vyslání" Or [Akce].[Ucel]="Přijetí",[Akce].[Podminky],[Akce].[Cislo]) AS Cislo, Akce.Datum, IIf([Akce].[Ucel]="Vyslání" Or [Akce].[Ucel]="Přijetí", [Akce].[Stat],[Dodavatele].[Nazev]) AS Nazev, Dodavatele.Specifikace, Akce.Podminky, Uhrady.Uhrada, Uhrady.Cislo AS UhradaCislo, Akce.DruhProst, Akce.Vyrizuje, Akce.PlanCena AS Planovano, Cerpani.Castka AS Cerpano FROM (Uhrady RIGHT JOIN ((Dodavatele RIGHT JOIN Akce ON Dodavatele.ID = Akce.Dodavatel) LEFT JOIN Cerpani ON Akce.ID = Cerpani.Akce) ON Uhrady.ID = Akce.Uhrada) LEFT JOIN AkcePodklady ON Akce.ID = AkcePodklady.Akce WHERE ((Akce.Stav=1));
SELECT DISTINCTROW QAkceVyrizovanoPom.ID, QAkceVyrizovanoPom.Ucel, QAkceVyrizovanoPom.Cislo, QAkceVyrizovanoPom.Datum, QAkceVyrizovanoPom.Nazev, QAkceVyrizovanoPom.Specifikace, QAkceVyrizovanoPom.Podminky, QAkceVyrizovanoPom.Uhrada, QAkceVyrizovanoPom.UhradaCislo, QAkceVyrizovanoPom.DruhProst, QAkceVyrizovanoPom.Vyrizuje, QAkceVyrizovanoPom.Planovano, Sum(QAkceVyrizovanoPom.Cerpano) AS Cerpano FROM QAkceVyrizovanoPom GROUP BY QAkceVyrizovanoPom.ID, QAkceVyrizovanoPom.Ucel, QAkceVyrizovanoPom.Cislo, QAkceVyrizovanoPom.Datum, QAkceVyrizovanoPom.Nazev, QAkceVyrizovanoPom.Specifikace, QAkceVyrizovanoPom.Podminky, QAkceVyrizovanoPom.Uhrada, QAkceVyrizovanoPom.UhradaCislo, QAkceVyrizovanoPom.DruhProst, QAkceVyrizovanoPom.Vyrizuje, QAkceVyrizovanoPom.Planovano;
V některých okamžicích může být uživatel značně zaskočen chováním systému při vyhodnocování dotazů. Normální postup je následující:
Otázkou je, kdy se provádí agregované výpočty a kdy výpočty virtuálních položek. K výpočtům by mělo dojít až po výběru požadovaných záznamů, tedy nejdříve po provedení bodu 3. To vysvětluje také chování dotazu vypočítávajícího průměrný věk osob v tabulce Adesar. Problém je komplikován skutečností, že k dispozici je pouze datum narození osoby v položce Spocti_Vek. Výpočet věku provádí funkce:
Function Spocti_vek(Dat As Variant) As Variant ' ************************************************************ ' Vrátí aktuální věk spočtený ze zadaného datumu ' ************************************************************ If IsNull(Dat) Then Spocti_vek = Null Else Dim V As Integer V = Year(Date) - Year(Dat) If Month(Dat) > Month(Date) Then V = V - 1 Else If Month(Dat) = Month(Date) Then If Day(Dat) > Day(Date) Then V = V - 1 End If End If End If Select Case V Case 1 Spocti_vek = Str(V) & " rok" Case 2, 3, 4 Spocti_vek = Str(V) & " roky" Case Else Spocti_vek = Str(V) & " let" End Select End If End Function
Funkce Spocti_Vek ale vrací věk včetně jednotky, např. "26 let". Pro výpočet průměrného věku převedeme výsledek funkce na číslo funkcí Val. Větší problém je v tom, že argument funkce je typu String (řetězec) a pokud bude položka DatumNarozeni prázdná (Null), bude hlášena chyba při předávání parametru funkci. Problém vyřešíme výběrem záznamů které nemají prázdné datum narození. Celý dotaz pak zní:
SELECT Avg(Val(Spocti_Vek([DatumNarozeni]))) FROM Adresy WHERE (((Adresy.DatumNarozeni) Is Not Null)))
Výsledkem dotazu bude skutečně průměrný věk všech osob v tabulce, kteří mají zadané datum narození.
Nyní vložíme výsledek do podmínky pro výběr osob, které mají vyšší věk než průměrný:
SELECT Adresy.*, Val(Spocti_Vek([DatumNarozeni])) AS Vek FROM Adresy WHERE (((Val(Spocti_Vek([DatumNarozeni])))> (SELECT Avg(Val(Spocti_Vek([DatumNarozeni]))) FROM Adresy WHERE (((Adresy.DatumNarozeni) Is Not Null)))) AND ((Adresy.DatumNarozeni) Is Not Null));
Opět zde musíme počítat věk osob, proto také v hlavním dotazu vybíráme pouze osoby, které mají zadáno datum narození. Jinak by opět vznikala chyba při volání funkce Spocti_Vek, neboť by jí byly předávány ke zpracování všechny záznamy. Problém by v tom případě bylo možno řešit jedině úpravou položky DatumNarozeni funkcí Nz(), která převádí hodnotu Null na prázdný řetězec nebo hodnotu 0:
SELECT Adresy.*, Val(Spocti_Vek(Nz([DatumNarozeni]))) AS Vek FROM Adresy WHERE (((Val(Spocti_Vek(Nz([DatumNarozeni]))))> (SELECT Avg(Val(Spocti_Vek([DatumNarozeni]))) FROM Adresy WHERE (((Adresy.DatumNarozeni) Is Not Null))));
Poslední otázkou, která se v souvislosti s řešeným problémem může objevit je proč jsme nepoužili agregovanou funkci DAvg("Val(Spocti_Vek([DatumNarozeni]))", "Adresy", "Not IsNull(DatumNarozeni)"), která poskytuje stejný výsledek. Důvodem je nižší rychlost provedení dotazu při jejím použití. Funkce ve své podstatě představuje dotaz, plán jeho provedení se ale musí vytvářet vždy znovu při použití funkce. Pokud pro určení průměrného věku použijeme dotaz, vytvoří se plán jeho realizace při uložení dotazu a současně s dotazem se uloží. Při dalším použití dotazu se tedy tento plán nemusí znovu vytvářet.
Již několikrát se stalo, že odladěný systém po nějaké době provozu náhle vypověděl poslušnost při vyhodnocení dotazu s hlášením viz obr. 5.
Důvod vzniku problému plně vysvětluje text nápovědy:
Can't perform join, group, or sort. Combined fields are too long. |
The maximum combined length of fields used to join, group, or sort is 255 bytes. Reduce the number of fields in your join, group, or sort, or use shorter data types and field sizes for the fields. |
Dotaz provádí agregovaný výpočet a součet velikostí obsahu položek, podle kterých se provádí seskupování záznamů je větší než 255 Byte. Je třeba buď ubrat počet položek pro seskupování nebo omezit jejich velikost např. funkcí Left([Jmeno], 50), protože problém způsobují především textové položky.
Bohužel se tato chyba projevuje obvykle až při provozu systému, protože v testovací fázi se používají jen cvičná data, která mají malý objem.