Thursday, August 14, 2008

Strange Oracle behaviour

In our project we have one very complicated query that involves 10 or more tables joined with left joins or inner joins

IN EACH TABLE there was a field DELETED.

Developer who writes a SQL query made a mistake forgetting to add an alias to the DELETED field in the following WHERE clause

select ...
from table1 t1 join table2 t2 on t1.table1_id = t2.table1_id etc...

and what was strange - there were no compilation errors, oracle executes that query but guess from what table it takes DELETED field? I don't know! But not from the first table :) This behaviour leads to the bug that was very hard to find, debug and correct.

Finally when developer corrected where condition, it starts work as expected


It is not the first case that makes me furrious about Oracle!

No comments: