Back in the days, when I was just beginning working as an Oracle developer, I used to view Oracle Database Server as such a great piece of software to the extent where I wouldn’t even consider that a problem which I’ve just encountered wasn’t my fault, but Oracle’s bug!

Why am I bringing this up, you might ask? Well, a few weeks ago I’ve stumbled upon a weird bug in Oracle. I was using Oracle 11.2.0.1.0. Take a look at the below example:

CREATE TABLE tab1 (id NUMBER, val NUMBER);
CREATE TABLE tab2 (id NUMBER, val NUMBER);

INSERT INTO tab1 VALUES (1, 2);
INSERT INTO tab2 VALUES (1, NULL);
COMMIT;

MERGE INTO tab1 d
USING (SELECT id, val FROM tab2) s
  ON (d.id = s.id)
WHEN MATCHED THEN
  UPDATE 
    SET d.val = nvl(s.val, d.s.val)
;

Output of running the commands:

table TAB1 created.
table TAB2 created.
1 rows inserted.
1 rows inserted.
committed.
1 rows merged.

And now:

SELECT * FROM tab1;

        ID        VAL
---------- ----------
         1            --< NULL! Wait, what?

Did you expect that result? I surely did not! The MERGE from my example should fail with an error – after all, the second argument of NVL has two aliases! If you would run the same example on Oracle 11.2.0.2.0, you would get the expected error:

Error at Command Line : 19 Column : 28
Error report -
SQL Error: ORA-00904: "D"."S"."VAL": invalid identifier

Well, what do you know – maybe it’s a feature after all 😉