Sql Hardcore: Union in Unterabfragen - sortiert

[Jürgen Auer]

Beim Microsoft-SqlServer 2005, auf dem server-daten basiert, gibt es eine tückische Geschichte mit UNION in Unterabfragen.

Aufgabenstellung: Eine Abfrage soll bsp. zu einem Nutzer genau eine Zeile zurückliefern. Wenn bsp. kein Nutzer angemeldet ist, soll 0 zurückgegeben werden. Notwendig ist dies bsp., falls im Rahmen einer Update-Anweisung der neue Wert nach einem komplizierten Verfahren ermittelt werden muß und zu jeder Zeile deterministisch genau ein neuer Wert benötigt wird.


Übliches Verfahren:

CODE Select Top 1 A.TabellenId, 1 As pos
From Tabelle As A
Where A._Owner = @USER_ID
Union
Select 0, 2
Order By pos, A.TabellenId


Wenn in der Tabelle eine Zeile drin ist, die @USER_ID als Owner gehört, dann wird die erste ID ausgegeben. Gibt es keinen Datensatz, der @USER_ID gehört, wird eine 0 geliefert. Analog kann nach 'Union' eine komplexe Abfrage notiert sein. Aufgrund der Where-Bedingungen ist sichergestellt, daß entweder nur die obere oder nur die untere Abfrage Zeilen liefert.


Nun gibt es Fälle, bei denen jede der Einzelabfragen mehrere Zeilen zurückliefern, bei denen weitere Verknüpfungen gewünscht sind usw. Auch für die oben erwähnte UPDATE-Aufgabe muß diese Abfrage als Unterabfrage notiert werden, so daß sie im JOIN-Abschnitt als Tabelle verfügbar ist. Also den obigen Code in eine Unterabfrage, mit Top 1 eine Zeile ausliefern. Zwei Alternativen:


CODE Select Top 1 B.TabellenId
From
(Select Top 1 A.TabellenId, 1 As pos
From Tabelle As A
Where A._Owner = @USER_ID
Union
Select 0, 2
Order By pos, A.TabellenId) As B


oder


CODE Select Top 1 B.TabellenId
From
(Select A.TabellenId, 1 As pos
From Tabelle As A
Where A._Owner = @USER_ID
Union
Select 0, 2) As B
Order By B.pos, B.TabellenId


Einer der beiden Codes führt unter bestimmten Umständen (bsp. mehrere Unionabschnitte, dort weitere verknüpfte Tabellen) zu beliebig falschen Ergebnissen.

Welcher? Warum ist das so? Wo steckt der Denkfehler?


PS: Das Problem wurde in einem Codeteil sichtbar, der nach dem Parsen einer vom Benutzer definierbaren Abfrage (mit Unterabfragen und Union) und nach deren Abbilden auf einen binären Baum versuchte, zu jedem Baumknoten die Schachtelungsebene zu bestimmen. Dieser - rückblickend fehlerhafte - Code läuft seit zwei Jahren und wurde schon für hunderte von Abfragen genutzt, ohne daß der Fehler sichtbar wurde (das knallt dann sofort). Das nichtdeterministische Verhalten der obigen - falschen - Variante wird erst sichtbar, wenn Abfragen mit mehreren tausend Zeichen verarbeitet werden oder wenn eine Abfrage 'sich wiederholende Unterabfragen' enthält.

 

[Irene]

Ich kann nicht das Ganze nachvollziehen, weil ich nicht dieselben Daten und vor allem Unter-Unions habe. Ausserdem bin ich nicht sicher ob ich alles richtig verstanden habe - mein Kopf ist nicht mehr am arbeiten ;-)

Nur zum sicherstellen, die Abfrage

CODE Select Top 1 A.TabellenId, 1 As pos
From Tabelle As A
Where A._Owner = @USER_ID
Union
Select 0, 2
Order By pos, A.TabellenId

liefert prinzipiell zwei Records (sofern A._Owner unique ist). Das Top bezieht sich nicht auf die Union, sondern nur auf das erste Select und ist in diesem Fall überflüssig, da die Begrenzung auf einen Record schon durch die Where-Klausel durchgesetzt wird.

Das da


CODE Select Top 1 B.TabellenId
From
(Select Top 1 A.TabellenId, 1 As pos
From Tabelle As A
Where A._Owner = @USER_ID
Union
Select 0, 2
Order By pos, A.TabellenId) As B

macht für mich keinen Sinn, respektive funktioniert nicht zuverlässig. Die Union ist zwar als Unterabfrage mit einem Top drumherum definiert. Aber der Top-Klausel fehlt das ORDER BY; so "weiss" das Top nicht, welchen Record es bevorzugen soll. Das Resultat ist dann einfach "der erste Record in der aktuellen Reihenfolge", und diese Reihenfolge ist nicht zwingend vorhersehbar.

Ich meine mich zu erinnern, dass ich sogar mal für ein Top 5 mehr als fünf Records bekommen habe. Nämlich dann, wenn über fünf gleiche in der Order By-Reihenfolge vorhanden waren. Könnte aber auch in Access gewesen sein.

Das


CODE Select Top 1 B.TabellenId
From
(Select A.TabellenId, 1 As pos
From Tabelle As A
Where A._Owner = @USER_ID
Union
Select 0, 2) As B
Order By B.pos, B.TabellenId

müsste aus meiner Sicht stimmen. Es liefert jedenfalls immer genau einen Record (es sei denn evtl. es gäbe mehrere mit der gleichen Pos, siehe oben).

Hoffe das hilft mal weiter, ansonsten bräucht ich mehr Details...

Griessli
Irene

 

[Jürgen Auer]

Genau, dies

QUOTE (Irene @ So 23.12.2007, 18:48)Das da


CODE Select Top 1 B.TabellenId
From
(Select Top 1 A.TabellenId, 1 As pos
From Tabelle As A
Where A._Owner = @USER_ID
Union
Select 0, 2
Order By pos, A.TabellenId) As B

macht für mich keinen Sinn, respektive funktioniert nicht zuverlässig. Die Union ist zwar als Unterabfrage mit einem Top drumherum definiert. Aber der Top-Klausel fehlt das ORDER BY; so "weiss" das Top nicht, welchen Record es bevorzugen soll. Das Resultat ist dann einfach "der erste Record in der aktuellen Reihenfolge", und diese Reihenfolge ist nicht zwingend vorhersehbar.


ist falsch. Bloß: Ich dachte, daß das korrekt sei. Und zwar deshalb, weil ich von einer impliziten Klammerung ausgegangen war:


QUOTE Select Top 1 B.TabellenId
From
((Select Top 1 A.TabellenId, 1 As pos
From Tabelle As A
Where A._Owner = @USER_ID
Union
Select 0, 2)
Order By pos, A.TabellenId) As B

Bei einer Select-Abfrage mit UNION auf der obersten Ebene darf nur eine OrderBy-Klausel ganz zum Schluß notiert werden. Diese sortiert alle Zeilen.

Das ist jedoch bei Unterabfragen falsch (und das war mir neulich neu). Dort ist der folgende Code zulässig (bei dem sofort gemeckert wird, wenn man diesen herauszieht und getrennt ausführt):


QUOTE Select Top 1 B.TabellenId
From
(Select Top 1 A.TabellenId, 1 As pos
From Tabelle As A
Where A._Owner = @USER_ID
Order By A.TabellenId
Union
Select 0, 2
Order By pos, A.TabellenId) As B

Die zweite Unterabfrage sah in Wirklichkeit ähnlich aus wie die erste, da war hier das Beispiel ungeschickt vereinfacht. Die tatsächliche Sortierung ging über die Differenz zweier Primärschlüssel (gib ein Element mit minimaler Differenz heraus), es war sogar egal, ob dieses Element aus dem oberen oder dem unteren Zweig stammte, wenn nur die Differenz minimal war.

Das Ergebnis war, daß - bei sehr vielen Zeilen im binären Baum (Nutzer hat eine Abfrage mit mehreren Unterabfragen, die selbst Unterabfragen enthalten) - der obere Select-Abschnitt auch etwas anderes als das Element mit 'minimaler Differenz' herausgeben konnte, weil dieser UNION-Abschnitt nicht sortiert war. Bei Abfragen mit 'einfachen' oder 'verschieden langen' Unterabfragen sorgte der Code drumherum dafür, daß das Problem nicht sichtbar wurde.

Deshalb blieb der Fehler auch etwa zwei Jahre lang unbemerkt und störte nicht einmal. Erst als ich neulich für einen Kunden so eine Monsterabfrage benötigte und dann noch etwas ergänzen wollte (die Varianten davor waren längst durchgelaufen), wurde sichtbar, daß sich der Code nicht strikt deterministisch verhielt.

 

[Michael Bieri]

Ist dein Ansatz nicht etwas kompliziert? Soweit ich dich verstehe willst du ja entweder eine TabellenID, oder den Wert 0 zurückgeben, limitiert auf 1 Datensatz. Dafür bietet sich doch eher ein Case oder ISNULL-Ansatz an.

Zum Bleistift:
QUOTE Select ISNULL((SELECT TOP 1 TabellenId FROM Tabelle WHERE _Owner = @USER_ID),0) as TabellenId

 

[Jürgen Auer]

QUOTE (Alonso @ Mo 24.12.2007, 10:01)Ist dein Ansatz nicht etwas kompliziert?

Das obige Beispiel ist auch viel zu sehr vereinfacht.

Beim realen Problem geht es um einen in Transact-Sql geschriebenen Parser für Sql.

Nutzer können eigene Abfragen erstellen - das ist ein wesentliches Feature von server-daten. Also muß sichergestellt sein, daß

• ein Nutzer nicht auf MS-Sql-Systemtabellen oder auf sd-Systemtabellen kommt
  
• er nicht auf andere Datenbanken anderer Kunden zugreifen kann
  
• bestimmte Systemfunktionen nutzbar (Stringbearbeitung), andere verboten sind
  
• möglichst viele Fehler (falsche Tabellen- und Spalten) im Vorfeld erkannt werden
  

Dazu wird der eingegebene Sql-Code in Token zerlegt und aus denen ein binärer Baum konstruiert (Start mit Declare / Select, dann pro Element zwei Children), solange, bis man bei den Blättern angekommen ist. Dabei müssen Union, geschachtelte Unterabfragen und all dies natürlich möglich sein. Und gegen Ende dieses Codes ist es notwendig, daß zu jedem Blatt das zugehörige 'erste Select' bzw. dessen ID zugeordnet wird.


PS: Ich habe den Codeausschnitt mal unten angehängt.


QUOTE Set @i = 1
While (@i > 0)
Begin
Update @TV
Set i_SubQuery_Source = D.A_Id
From @TV As A Inner Join
(Select Top 1 E.A_Id, E.C_Id
From (
Select A.Id As A_Id, C.Id As C_Id, C.Id - A.Id As Diff, 1 As flag
From @TV As A Inner Join @TV As B
On A.Id = B.i_parent_Id Inner Join @TV As C
On B.Id = C.i_parent_Id
Where A.i_tbl_SyntaxElementsId = @c_i_SubQuery_Symbol And
B.i_tbl_SyntaxElementsId = @c_i_Subquery_Fragment_Symbol And C.i_tbl_SyntaxElementsId = @c_i_IBracketRight_Symbol
And A.i_SubQuery_Source Is Null And C.i_SubQuery_Source Is Null

Union

Select A.Id As A_Id, D.Id As C_Id, D.Id - A.Id As Diff, 2 As flag

From @TV As A Inner Join @TV As B
On A.Id = B.i_parent_Id Inner Join @TV As C
On B.Id = C.i_parent_Id Inner Join @TV As D
On C.Id = D.i_parent_Id
Where A.i_tbl_SyntaxElementsId = @c_i_Subquery_Or_EL_Symbol And B.i_tbl_SyntaxElementsId = @c_i_Subquery_Or_EL_Fragment_Symbol
And C.i_tbl_SyntaxElementsId = @c_i_Subquery_Fragment_Symbol And D.i_tbl_SyntaxElementsId = @c_i_IBracketRight_Symbol
) As E Order By E.Diff) As D
On A.Id Between D.A_Id And D.C_Id
Where A.i_SubQuery_Source Is Null

Set @i = @@RowCount
End

Der fett markierte Abschnitt war ursprünglich eine Klammerungsebene tiefer, zusätzlich hatten die beiden per Union verknüpften Ausdrücke ein Top 1 - da ich davon ausgegangen war, daß die OrderBy auf beide wirkt (wie bei der Verwendung von Union auf der obersten Ebene).