Running Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

I am currently porting our software to run with ORACLE and have come across the following problem.

VIEW SQL :-
CREATE VIEW supplieraccountinfo AS
SELECT T.id AS supplierid, T.balance, T.onorderval AS onordervalue
FROM traders T
WHERE T.tradertype LIKE 'S%'

SELECT SQL:-
SELECT id, name, source
FROM traders
INNER JOIN supplieraccountinfo SI ON traders.id = SI.supplierid

Running the select you get:
"ORA-00918: column ambiguously defined"

Now this SQL is valid on SQL SERVER, DB2 & INFORMIX but not ORACLE.

Now as far as I'm aware, through all my years of using SQL you should not need to qualify "id" as it is unique to the traders table. The supplieraccountinfo view has supplierid and onordervalue columns only.

It seems to be that if a column is used in the JOIN criteria and it is also in the select list then it must be either unique at the base (table) level across all of the referenced tables/views or else qualified.

Proof of this is that if you change the join and replace "traders.id = SI.supplierid" with "traders.balance = SI.balance" then the SQL is OK.

Any ORACLE SQL experts comments on this are most welcome.

regards

Andy

Sorry - that should read:

Now as far as I'm aware, through all my years of using SQL you should not need to qualify "id" as it is unique to the traders table. The supplieraccountinfo view has supplierid, BALANCE and onordervalue columns only.

Andy

when you type

desc supplieraccountinfo


what do you see?


try running the following command.

views are nothing but query stored in db , whenever view is used basically that query is called. Hence in this case we accessing traders object twice and hence the error ambiguously defined.

Here is explain plan of above query when alias is used for id column

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 46 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TRADERS | 1 | 26 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TRADERS | 1 | 20 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------

gets:

Name Null Type
------------------------------ -------- -------------
SUPPLIERID NOT NULL NVARCHAR2(30)
BALANCE NOT NULL NUMBER(19,4)
ONORDERVALUE NUMBER(19,4)

Yes I know that you can think of a view as 'just' a 'compiled' SELECT statement but as with all other DB providers that I have come across they are a little more sophisticated than you imply.

You should (and can with all the other main DB vendors) be able to reference a view as a totally independent object and not think of it with regard to what it is based on.

We have a very large product with hundreds of very complex SQL statements and views. Some of these are based on multiple self joins etc and views are the industry standard to simplifying SQL statements.

I will do some more investigation for I cannot believe that a vendor such as ORACLE can be so different with regard to view SQL.

Andy

does error still occur for SQL below?

Well yes this works but it doesn't help.
The example I have given is a very much simplified version of one of our actual statements.
It needs to use FULL ANSI SQL syntax.

It won't help you, but I can confirm that this is not working as expected on 11g installation (tried it with 11.1.0.6.0 on Windows XP)
I too would have expected the column not to be ambigous.

I tried this with 10.2.0.3.0 and there it is working as expected i.e. there is no error.

Seems to be a regression in 11.x
Extremely annoying I agree.

Did you search metalink? Maybe there is a patch for that already although I doubt it.

Yes shammat - I concur with your findings.

Running the same test on 10g (10.2.0.3.0) the error does not occur.

My simplified test scenario is thus:

CREATE TABLE mytable (mycol NVARCHAR2(10), mycol2 NVARCHAR2(10));

CREATE VIEW myview AS SELECT mycol AS v_mycol, mycol2 AS v_mycol2 FROM mytable;

SELECT mycol
FROM mytable
INNER JOIN myview ON mytable.mycol = myview.v_mycol;

- Above works on 10g (10.2.0.3.0) but fails with “ORA-00918: column ambiguously defined” on 11g (11.1.0.7.0)

SELECT mycol
FROM mytable
LEFT OUTER JOIN myview ON mytable.mycol = myview.v_mycol;

- Above works on both 10g and 11g.

I have raised a log through Metalink.

Will post a reply when I hear anything.

Andy

Confirmed with Oracle through Metalink support log that this is indeed a bug.

Oracle have also confirmed this as a bug in:
10.2.0.4
11.1.0.6
11.1.0.7 (where I found it)

The fix for this is not yet included in 11.1.0.7 patch bundles.

Workaround:
Do not use ANSI JOINS.
Prefix the column name.
Use 10.2.0.3.

This Oracle port is going to be fun...

Andy

For info.

Now fixed in 11.1.0.7 patch set 14.

Regards,
Andy