Kategorie: "SQL Server"
Geek City: What's Worse Than a Table Scan?
SQL Server Locking
Unterabfragen und Namensauflösung
Die Möglichkeit, Unterabfragen zu erstellen und SELECT Statements zu korrelieren ist ein ziemlich mächtiges Feature in SQL und SQL Server unterstützt glücklicherweise die Erstellung von korrelierten und unkorrelierten Unterabfragen. Allerdings gibt es auch hier einige Stolperfallen, die man kennen sollte, um nicht später unangenehm überrascht zu werden. Eine dieser Stolperfallen hängt mit der Namensauflösung von Tabellenspalten in Unterabfragen zusammen. Sie ist Gegenstand dieses Beitrages.
Gegeben ist folgende Ausgangssituation:
2 Tabellen (dbo.t1 und dbo.t2), die über das Spaltenpaar p1 - c1 miteinander verknüpft sind.
IF OBJECT_ID('dbo.t1') IS NOT NULL
DROP TABLE dbo.t2, dbo.t1;
GO
CREATE TABLE dbo.t1
(
p1 int PRIMARY KEY,
v1 varchar(20)
);
GO
CREATE TABLE dbo.t2
(
c1 int
CONSTRAINT FK_t2_t1 FOREIGN KEY REFERENCES dbo.t1(p1),
d1 datetime,
);
SET NOCOUNT ON;
INSERT INTO dbo.t1 SELECT 1, 'Hallo';
INSERT INTO dbo.t1 SELECT 2, 'Welt';
INSERT INTO dbo.t1 SELECT 3, 'WTF';
INSERT INTO dbo.t2 SELECT 1, '20080520';
INSERT INTO dbo.t2 SELECT 1, '20080521';
INSERT INTO dbo.t2 SELECT 1, '20080522';
SET NOCOUNT OFF;
Nun sollen in einer Abfrage alle Zeilen aus t1 zurückgegeben, für die ein Match in der Tabelle t2 existiert, wobei zusätzlich noch eine Einschränkung auf einen bestimmten Datumsbereich von t2 vorgenommen wird (d1 = '20080520'). Bevor wir nun die Abfrage tatsächlich schreiben, zunächst einmal ein kurzer Blick auf das erwartete Resultset. Man stellt fest, dass es genau ein Spaltenpaar p1 - c1 gibt, welches einen Match in seinen Werten aufweist und wo das Prädikat d1 = '20080520' erfüllt ist. Also erwarten wir einen Datensatz in der Ergebnismenge.
Eine Möglichkeit, diese Abfrage zu formulieren ist die Verwendung einer unkorrelierten Unterabfrage in einer IN Klausel:
SELECT
*
FROM
dbo.t1
WHERE
p1 IN
(SELECT
p1
FROM
dbo.t2
WHERE
d1 = '20080520');
Hier ist die Ergebnismenge:
p1 v1
----------- --------------------
1 Hallo
2 Welt
3 WTF
(3 row(s) affected)
Wir erhalten alle Zeilen aus t1. Und nicht nur das. Schaut man mal genauer auf die Abfrage und dort speziell auf die IN Klausel, wird man feststellen, dass die Spalte p1 in der Tabelle dbo.t2 überhaupt nicht existiert. Trotzdem wird die Abfrage fehlerfrei ausgeführt und liefert auch noch ein Ergebnis. Warum?
An dieser Stelle kommt nun die bereits erwähnte Namensauflösung ins Spiel. SQL Server versucht p1 nach dem inneren SELECT zu dbo.t2.p1 aufzulösen. Da dies nicht gelingt, weil es in der Tabelle dbo.t2 diese Spalte eben nicht gibt, wir uns aber mit unserer Abfrage auf mehr als eine Tabelle beziehen, wird p1 nach dem äusseren SELECT zu dbo.t1.p1 aufgelöst. Diese Spalte existiert in t1 und somit haben wir nun effektiv eine korrelierte Unterabfrage der Form:
SELECT
t1.p1
FROM
dbo.t2
WHERE
d1 = '20080520'
Sobald es auch nur eine Zeile in t2 gibt, für die diese WHERE Klausel WAHR ist, ist die WHERE Klausel der gesamten äusseren IN Klausel WAHR und somit werden alle Zeilen aus t1 zurückgegeben.
Wie kann man so einen Fehler vermeiden?
Nun, zunächst ist es wichtig zu verstehen, dass in der inneren Abfrage sowohl Referenzen zu inneren als auch zur äusseren Abfrage möglich sind. Anderenfalls gäbe es eben keine Möglichkeit korrelierte Unterabfragen zu erstellen. Hat man dies verinnerlicht, besteht die einfachste Lösung hierfür in der vollständigen Qualifizierung sämtlicher Spaltennamen in der Form:
SELECT
*
FROM
dbo.t1
WHERE
p1 IN
(SELECT
dbo.t2.p1
FROM
dbo.t2
WHERE
d1 = '20080520');
oder
SELECT
*
FROM
dbo.t1
WHERE
p1 IN
(SELECT
MeinAlias.p1
FROM
dbo.t2 MeinAlias
WHERE
MeinAlias.d1 = '20080520');
Führt man nun diese beiden Abfragen aus, erhält man in beiden Fällen sofort eine Fehlermeldung:
Msg 207, Level 16, State 1, Line xy
Invalid column name 'p1'.
Fazit?
Dieser Fehler mag vielleicht in der Praxis nicht unbedingt häufig auftreten, wenn er aber doch einmal auftritt, ist aber meiner Meinung nicht unbedingt einfach zu troubleshooten, da er auf der logischen Ebene angesiedelt und daher nicht offensichtlich ist. Die Verwendung von Tabellenaliasen, die generell eine gute Praxis ist, kann hier helfen den Fehler abzufangen, bevor er überhaupt auftreten kann.
Non Recursive Common Table Expressions - Performance Sucks 2 - ROW_NUMBER() is executed {number of CTE references} x {number of rows from the anchor}
Non Recursive Common Table Expressions - Performance Sucks 1 - CTE Self-Join / CTE Sub Query inline expansion
Query Processing Presentation
How hard is it to pick the right non-clustered indexes?
