ANSI Join Syntax und Prädikatanordnung

Die ANSI-Syntax zur Formulierung von Joins finde ich grundsätzlich deutlich lesbarer als Oracles traditionelle Schreibweise und würde sie vermutlich regelmäßig einsetzen, wenn sie nicht so viele Bugs hervorrufen würde. Einer davon ist gestern in einem OTN-Thread aufgetaucht. Dort hatte sich der Fragesteller darüber gewundert, dass zwei Varianten eines Outer-Joins unterschiedliche Ergebnisse lieferten, je nachdem, ob die ON-Prädikate direkt bei den zugehörigen FROM-Klauseln erschienen, oder getrennt von diesen in einem gemeinsamen abschließenden Block. Mein erster Gedanke war, dass die zweite Variante syntaktisch falsch sein sollte, und mein erstes Beispiel schien diese Annahme zu bestätigen, aber offenbar hängen der Erfolg oder Misserfolg - und auch das Ergebnis - von der Reihenfolge der Bedingungen ab. Dazu ein Beispiel:

-- 12.1.0.2
drop table t1;
drop table t2;
drop table t3;
 
create table t1
as
select rownum id
     , 'AAA' col1
  from dual
connect by level <= 15;
 
create table t2
as
select rownum id
     , 'BBB' col1
  from dual
connect by level <= 15;
 
create table t3
as
select rownum id
     , 'CCC' col1
  from dual
connect by level <= 10;

select /* normal ANSI syntax */
        count(*)
  from t1
  left outer join t3
    on (t3.id = t1.id)
 inner join t2
    on (t3.id = t2.id);
 
  COUNT(*)
----------
        10
 
select /* strange ANSI syntax: predicate order 1, 2 */
        count(*)
  from t1
  left outer join t3
 inner join t2
    on (t3.id = t1.id)
    on (t3.id = t2.id);

    on (t3.id = t1.id)
                *
FEHLER in Zeile 6:
ORA-00904: "T1"."ID": ungültiger Bezeichner
 
select /* strange ANSI syntax: predicate order 2, 1 */
        count(*)
  from t1
  left outer join t3
 inner join t2
    on (t3.id = t2.id)
    on (t3.id = t1.id);
 
  COUNT(*)
----------
        15

Demnach ergibt sich:

• die (mehr oder minder) normale Anordnung der Elemente liefert ein Ergebnis mit 10 rows.
• die erste Version mit Trennung der FROM-Klauseln von den ON-Prädikaten und einer Anordnung der Prädikate (t3-t1, t3-t2) liefert einen Fehler.
• die zweite Version mit Trennung der FROM-Klauseln von den ON-Prädikaten und einer Anordnung der Prädikate (t3-t2, t3-t1) liefert 15 rows.

Wenn man dazu ein CBO-Trace erzeugt (Event 10053) wird deutlich, dass der Optimizer im Rahmen seiner Transformationen den Outer Join aus der ersten Query ausschließt, während er in der zweiten (lauffähigen) Query erhalten bleibt, woraus sich die 10 bzw. 15 Ergebnissätze ergeben:

-- Final query after transformations (reformatted)
/* normal ANSI syntax */
SELECT COUNT(*) "COUNT(*)"
   FROM "C##TEST"."T1" "T1"
      , "C##TEST"."T3" "T3"
      , "C##TEST"."T2" "T2"
  WHERE "T3"."ID" = "T2"."ID"
    AND "T3"."ID" = "T1"."ID"

/* strange ANSI syntax: predicate order 2, 1 */
SELECT COUNT(*) "COUNT(*)"
  FROM "C##TEST"."T1" "T1"
     , (SELECT "T3"."ID" "ID"
          FROM "C##TEST"."T3" "T3"
             , "C##TEST"."T2" "T2"
         WHERE "T3"."ID" = "T2"."ID") "from$_subquery$_004"
WHERE "from$_subquery$_004"."ID"(+) = "T1"."ID"

Ich muss gestehen, dass ich mir nicht völlig sicher bin, welche der beiden Interpretationen der Join-Bedingungen korrekt ist - grundsätzlich handelt es sich offenbar um eine Frage der Reihenfolgen:

• wenn zunächst der Outer Join von t1 und t3 erfolgt, der 15 rows liefert, und dann ein Inner Join dieses Ergebnisses an t2 durchgeführt wird und die Verknüpfung über die t3.id erfolgt, die im ersten Zwischenergebnis NULL ist, dann ist das Ergebnis 10 korrekt.
• wenn zunächst der Inner Join von t2 und t3 erfolgt und dann der Outer Join dieses Zwischenergebnisses an t1, dann sollte sich die 15 ergeben.

Persönlich würde ich in einem solchen Fall wahrscheinlich eine explizite Klammerung über Inline-Views (oder CTEs) durchführen (und hoffen, dass der Optimizer diese Klammerung nicht wieder durch Transformationen beseitigt). Ein Bug scheint jedenfalls zu sein, dass die Reihenfolge der Prädikate darüber entscheidet, ob ein Fehler oder ein Ergebnis zurückgeliefert wird. Ich vermute, dass tatsächlich die Reihenfolge der Prädikate entscheidet, welche Verknüpfung Vorrang besitzt - und möglicherweise sagt die ANSI-Spezifikation mehr darüber aus, wie die Verarbeitungsreihenfolge der Join Operationen gedacht ist - wenn ich dazu noch Hinweise finde, ergänze ich sie. Ein Indiz dafür, dass tatsächlich der Inner Join Vorrang haben sollte, liefert postgres: dort liefern beide Varianten 15 Datensätze.

Korrektur: die abschließende Aussage zu postgres war falsch (basierend auf einem copy&paste-Fehler) - tatsächlich verhält sich postgres exakt genauso wie Oracle und liefert 10 rows, einen Fehler ("ERROR:  invalid reference to FROM-clause entry for table t1. TIP:  There is an entry for table t1, but it cannot be referenced from this part of the query.") bzw. 15 rows.

Nachtrag 21.11.2014: Patrick Barel hat mich in seinem Kommentar davon überzeugt, dass es sich nicht um einen Bug handelt (worauf bereits das postgres-Verhalten hindeutete), sondern um ein definiertes Verhalten. Seltsam sieht es trotzdem aus...