LEFT JOIN mit IS NULL

Hallo,
bisher dachte ich, SQL sei eine wohldefinierte Sprache, wo in verschiedenen
Implementationen bei der gleichen Fragestellung und dem gleichen
Datenbestand immer das gleiche Ergebnis herauskommt, und maximal einige
Syntax-Varianten in bestimmten Dialekten unzulässig sind.
Z.B. verlangt Postgres in
SELECT feld1 AS f1 ...
das 'AS' während es in Oracle optional ist etc.

Nun habe ich aber das erste Mal die Erfahrung gemacht, das das selbe
SQL-Statement in zwei Datenbanken (Oracle und Postgres) mit einem
identischen Datenbestand ein unterschiedliches Ergebnis liefert.

Es geht darum aus einer Tabelle mit Timestamps zu Statuswechseln von
Vorgängen, den kompletten Satz des jeweils letzten (spätesten) Wechsels auf
einen bestimmten Status bzw. einen aus einer Gruppe Stati herauszufinden.

Hier der Trivial-Aufbau des Problems:

Ich habe eine Tabelle mit folgenden Feldern
VORGANG NUMBER(6)
ZEITPUNKT DATE
STATUS_ALT NUMBER(4)
STATUS_NEU NUMBER(4)
Das Feld ZEITPUNKT ist in der Postgres ein 'Timestamp' und in der Oracle ein
'Date', was dann effektif (zumindest für diese Fragestellung) das Gleiche
ist.

Diese Tabelle hat zwei Sätze
VORGANG ZEITPUNKT STATUS_ALT STATUS_NEU
1 2006-01-01 00:00:00.0 20 30
1 2006-01-01 12:00:00.0 25 30

Die Abfrage lautet
SELECT *
FROM t t1
LEFT JOIN t t2 ON t2.zeitpunkt > t1.zeitpunkt
AND t2.vorgang = t1.vorgang
AND t2.status_neu IN (30,40)
WHERE t1.status_neu IN (30,40)
AND t2.vorgang IS NULL;

In Postgres kommt das (meiner Ansicht nach) richtige Ergebis heraus:
Ein Satz mit den Daten des zweiten Satzes in den Feldern von T1 und
leeren Einträgen in den Feldern von T2.
1 2006-01-01 12:00:00.0 25 30 - - - -

In Oracle kommen zwei Ergebnissätze heraus:
Satz 1 und 2 jeweils in den Feldern von T1 mit leeren Feldern für T2.
1 2006-01-01 00:00:00.0 20 30 - - - -
1 2006-01-01 12:00:00.0 25 30 - - - -

Meiner Meinung nach hat dieses Statement einen JOIN aus den beiden Tabellen
nach den in der ON-Klausel definierten Regeln zu bilden und auf dieses
Ergebnis die WHERE-Klausel anzuwenden.

Wenn man den letzten Teil der WHERE-Klausel ('AND t2.vorgang IS NULL')
wegläßt, ist das Ergebnis in beiden Datenbanken gleich:
1 2006-01-01 00:00:00.0 20 30 1 2006-01-01 12:00:00.0 25 30
1 2006-01-01 12:00:00.0 25 30 - - - -

Wenn man auf diesen Zwischenstand die WHERE-Klausel anwendet, bleibt nur die
zweite Zeile übrig, was Postgres ja auch zurückliefert.

Deshalb meine Frage:
Macht Oracle hier einen Fehler oder ist es erlaubt zu einem JOIN einfach
wieder Sätze hinzuzuerfinden, wenn der ursprüngliche Satz durch eine
WHERE-Klausel ausgeschlossen wurde?

mfg Volker

PS: Ganz bunt und lustig wird's übrigens, wenn man die Teil-Klausel des
JOINs
t2.status_neu IN (30,40)
in
t2.status_neu = 30
umschreibt.

Dann kommt nämlich auch bei Oracle das gewünschte Ergebnis raus.

Re: LEFT JOIN mit IS NULL

So'n Mist, jetzt ist mir die Frage aus Versehen in die
PHP-Datenbanken-Gruppe gerutscht. Das war nicht so gemeint.

Volker Boehm wrote:
> Hallo,
> bisher dachte ich, SQL sei eine wohldefinierte Sprache, wo in
> verschiedenen Implementationen bei der gleichen Fragestellung und dem
> gleichen Datenbestand immer das gleiche Ergebnis herauskommt, und maximal
> einige Syntax-Varianten in bestimmten Dialekten unzulässig sind.
> Z.B. verlangt Postgres in
> SELECT feld1 AS f1 ...
> das 'AS' während es in Oracle optional ist etc.
>
> Nun habe ich aber das erste Mal die Erfahrung gemacht, das das selbe
> SQL-Statement in zwei Datenbanken (Oracle und Postgres) mit einem
> identischen Datenbestand ein unterschiedliches Ergebnis liefert.
>
> Es geht darum aus einer Tabelle mit Timestamps zu Statuswechseln von
> Vorgängen, den kompletten Satz des jeweils letzten (spätesten) Wechsels
> auf einen bestimmten Status bzw. einen aus einer Gruppe Stati
> herauszufinden.
>
> Hier der Trivial-Aufbau des Problems:
>
> Ich habe eine Tabelle mit folgenden Feldern
> VORGANG NUMBER(6)
> ZEITPUNKT DATE
> STATUS_ALT NUMBER(4)
> STATUS_NEU NUMBER(4)
> Das Feld ZEITPUNKT ist in der Postgres ein 'Timestamp' und in der Oracle
> ein 'Date', was dann effektif (zumindest für diese Fragestellung) das
> Gleiche ist.
>
> Diese Tabelle hat zwei Sätze
> VORGANG ZEITPUNKT STATUS_ALT STATUS_NEU
> 1 2006-01-01 00:00:00.0 20 30
> 1 2006-01-01 12:00:00.0 25 30
>
> Die Abfrage lautet
> SELECT *
> FROM t t1
> LEFT JOIN t t2 ON t2.zeitpunkt > t1.zeitpunkt
> AND t2.vorgang = t1.vorgang
> AND t2.status_neu IN (30,40)
> WHERE t1.status_neu IN (30,40)
> AND t2.vorgang IS NULL;
>
> In Postgres kommt das (meiner Ansicht nach) richtige Ergebis heraus:
> Ein Satz mit den Daten des zweiten Satzes in den Feldern von T1 und
> leeren Einträgen in den Feldern von T2.
> 1 2006-01-01 12:00:00.0 25 30 - - - -
>
> In Oracle kommen zwei Ergebnissätze heraus:
> Satz 1 und 2 jeweils in den Feldern von T1 mit leeren Feldern für T2.
> 1 2006-01-01 00:00:00.0 20 30 - - - -
> 1 2006-01-01 12:00:00.0 25 30 - - - -
>
> Meiner Meinung nach hat dieses Statement einen JOIN aus den beiden
> Tabellen nach den in der ON-Klausel definierten Regeln zu bilden und auf
> dieses Ergebnis die WHERE-Klausel anzuwenden.
>
> Wenn man den letzten Teil der WHERE-Klausel ('AND t2.vorgang IS NULL')
> wegläßt, ist das Ergebnis in beiden Datenbanken gleich:
> 1 2006-01-01 00:00:00.0 20 30 1 2006-01-01 12:00:00.0 25 30
> 1 2006-01-01 12:00:00.0 25 30 - - - -
>
> Wenn man auf diesen Zwischenstand die WHERE-Klausel anwendet, bleibt nur
> die zweite Zeile übrig, was Postgres ja auch zurückliefert.
>
> Deshalb meine Frage:
> Macht Oracle hier einen Fehler oder ist es erlaubt zu einem JOIN einfach
> wieder Sätze hinzuzuerfinden, wenn der ursprüngliche Satz durch eine
> WHERE-Klausel ausgeschlossen wurde?
>
> mfg Volker
>
> PS: Ganz bunt und lustig wird's übrigens, wenn man die Teil-Klausel des
> JOINs
> t2.status_neu IN (30,40)
> in
> t2.status_neu = 30
> umschreibt.
>
> Dann kommt nämlich auch bei Oracle das gewünschte Ergebnis raus.