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
184.108.40.206.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.
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
220.127.116.11.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 😉