ORA-00904 and MERGE

Oracle 10.1.0.2 on Windows

I just came across the following parser whim:

CREATE TABLE test_a (a NUMBER, b NUMBER, c NUMBER, d NUMBER)
/
CREATE TABLE test_b (a NUMBER, b NUMBER, c NUMBER, d NUMBER)
/

   MERGE INTO test_a t1
   USING (SELECT a, b
                 c, d from test_b) t2
   ON (t1.a = t2.a)
   WHEN MATCHED THEN
     UPDATE SET t1.b = t2.b,
                t1.c = t2.c
   WHEN NOT MATCHED THEN
     INSERT (a, b, c, d)
     VALUES (t2.a, t2.b, t2.c, t2.d)
/

ORA-00904: "T2"."B": invalid identifier

As you can see, there is a missing comma after the "b" field in the SELECT clause at the beginning of the MERGE statement.

The parser is not complaining about the "c" identifier in the SELECT clause but on the "b" identifier in the NOT MATCHED clause!
This is absolutely misleading, it took me 10 minutes to spot this quirk.

Funnily enough, if you modify the above statement in the following way:

    MERGE INTO test_a t1
    USING (SELECT a, b
                  c, d from test_b) t2
    ON (t1.a = t2.a)
    WHEN NOT MATCHED THEN
      INSERT (a, c, d)
      VALUES (t2.a, t2.c, t2.d);

The statement gets executed without complaints about the missing comma after "b"!

This is definitely a parser flaw.

Bye,
Flavio

See message translations for ORA-00904 and search additional resources.

---

ORA-00904: : identificativo non valido
ORA-00904: : identificador no válido
ORA-00904: : identificador incorrecte
ORA-00904: : identificateur non valide
ORA-00904: Ungültiger Spaltenname
ORA-00904: : μη αποδεκτό αναγνωριστικό
ORA-00904: : ugyldig identifikator
ORA-00904: : ogiltig identifierare
ORA-00904: : ugyldig ID
ORA-00904: : virheellinen tunniste
ORA-00904: : érvénytelen azonosító
ORA-00904: : identificator nevalid
ORA-00904: : ongeldige ID.
ORA-00904: nome inválido de coluna
ORA-00904: : identificador inválido
ORA-00904: : недопустимый идентификатор
ORA-00904: : neplatný identifikátor
ORA-00904: : neplatný identifikátor
ORA-00904: : niepoprawny identyfikator
ORA-00904: : geçersiz belirleyici