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...
WHERE DELETED = 0
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
WHERE t1.DELETED = 0
It is not the first case that makes me furrious about Oracle!
No comments:
Post a Comment