4

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'

?

Andrey Eremin
  • 287
  • 1
  • 4
  • 14
  • 3
    I'm really surprised that it worked in Oracle 9. That query should have never worked. –  Aug 31 '11 at 08:37
  • 1
    Didn't you add the column TYPE to one of the tables in the same process as migrating to Oracle 11? Or maybe a GRANT has made one of the columns visible to you? – Benoit Aug 31 '11 at 08:43
  • actually i cannot answer this question cause i'm not a DBA. so i just do not know how this db was migrated( – Andrey Eremin Aug 31 '11 at 08:46
  • So i asked DBA and got that info: old DB version 10.2.0.4.0 - the query worked fine new DB version 11.2.0.2.0 - i get the error – Andrey Eremin Aug 31 '11 at 08:49
  • please add the real ddl for the two tables, and the real query – Florin Ghita Aug 31 '11 at 08:52
  • 1) the thing is that all data in a.type and b.type is the same. i mean - i work with test DB. in production db it is possible to have a different data in these columns – Andrey Eremin Aug 31 '11 at 10:08

4 Answers4

5

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.

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • yeah i know. but in oracle 9 there was no such a mistake...so somehow it worked and returned results. so now to avoid this mistake i have to modify the query but i do not understand how this worked earlier. – Andrey Eremin Aug 31 '11 at 08:40
  • 1
    @Andrey: Test the ambiguous query in Oracle 9 (where you say it works) and check the output to see which of the two it uses: `where a.type = 'A'` or the `where b.type = 'A'` – ypercubeᵀᴹ Aug 31 '11 at 08:45
5

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.

Gary Myers
  • 34,963
  • 3
  • 49
  • 74
  • So. thanks all of you for the help. So i finally got to the person who wrote this query long time ago and he explained to me the logic of the query. – Andrey Eremin Aug 31 '11 at 11:17
2

I think you should test in Oracle 9 (where you say it works) and compare the output of the ambiguous query:

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

with both the non-ambiguous ones:

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

and:

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

Something like this would do:

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

(in short):

(Base)
MINUS
(QueryA)

and then:

(QueryA)
MINUS
(Base)

If both of the above MINUS queries return 0 rows, then BASE query is interpreted as QueryA.

Check similarly and compare Base with QueryB.

Another plausible reason for this error is that during (or about the same period with ) the migration, a type column was added in the 2nd table. Do you have old versions of the database tables' definitions to check that?

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
1

All - keep in mind there was a major change to the optimization engine for 11g. If you set your query optimizer level to 10.2.x on your 11g instance I bet the query would start working again.

That being said you should provide the alias for it so it's not ambiguous to the database server or the DBA / Developer coming behind you. :)

tsells
  • 2,751
  • 1
  • 18
  • 20