Hello, I'm just wondering for below SQL, why is the optimizer not smart enough to avoid the ORA-00918: column ambiguously defined error:
Sample SQL:
select a.* from table1 a, table2 b
where a.col1 = b.col1
and a.col2 = b.col2
order by col1, col2;
Since I'm only selecting a.*, shouldn't the server automatically knows to order by a.col1, a.col2?
Or even order by b.col1, b.col2 would produce same result due to the equality conditions in the SQL clauses.
Why would Oracle throw the ORA error and not include the above checkings?
Or maybe my above assumptions above are flawed and do not work all the time?
Many thanks.