Oracle: column ambiguously defined

Question

I know there are lots of questions like these, but my question is not how to get rid of this error but to know how this worked earlier in 9-th version of Oracle.

I've got an old sources written in Ruby and Oracle DB which recently was upgraded to version=11.

I cannot edit data in Oracle DB, only read. so there are two tables lets say: table A(id, name, type, customer) and table B(id,a_id,type,person)

so. there is a query in the source code:

select a.id,b.id from a join b on a.id = b.a_id where type = 'A'

so in Oracle 9 this worked perfectly but now i've got "column ambiguously defined" error.

What i'd like to know is:

where type = 'A'

is the same as

where a.type = 'A' AND b.type = 'A'

or

where a.type = 'A' OR b.type = 'A'

?

Answer 1

I think this was a bug with the ANSI style join. Use DBMS_XPLAN to find which table was being filtered in the old database.

Or better still, work out from the business logic what they query SHOULD have been.

Answer 2

No, and that's the problem: It could mean

where a.type = 'A'

or it could mean

where b.type = 'A'

with potentially different results; hence the error saying it is ambiguously defined.