資料庫升級導致ORA-918錯誤

客戶的資料庫從10.2.0.1升級到10.2.0.5後，出現了ORA-918錯誤，不過導致錯誤出現的原因並不是升級碰到了BUG，而是升級解決了BUG。

在Oracle 10.2.0.5中，解決了一個Bug 5368296  ANSI join SQL may not report ORA-918 for ambiguous column，結果原本客戶受這個bug影響而沒有報錯的SQL語句，在升級之後開始大面積報錯。

而解決辦法除了修改SQL語句外，只有回退一個辦法，Oracle顯然不會為了重現一個bug而提供什麼解決方案。當然這個問題的避免應該透過前期的測試來避免，不過這裡還是關注一下這個bug。

在如果使用標準查詢寫法，當關聯表的個數超過2個，且表都包含相同的列名，那麼在查詢的時候如果不指定這個列名的屬主，是不會報錯的。

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Nov 8 15:51:41 2011

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create user u1 identified by u1 default tablespace users;

User created.

SQL> grant connect, resource to u1;

Grant succeeded.

SQL> conn u1/u1
Connected.
SQL> create table t1 (id number);

Table created.

SQL> create table t2 (id number);

Table created.

SQL> create table t3 (id number);
Table created.

SQL> select id from t1, t2, t3 where t1.id = t2.id and t1.id = t3.id;
select id from t1, t2, t3 where t1.id = t2.id and t1.id = t3.id
*
ERROR at line 1:
ORA-00918: column ambiguously defined

SQL> select id from t1 join t2 on t1.id = t2.id join t3 on t1.id = t3.id;

no rows selected

SQL> select id from t1 join t2 on t1.id = t2.id;
select id from t1 join t2 on t1.id = t2.id
*
ERROR at line 1:
ORA-00918: column ambiguously defined

可以看到，Oracle的寫法不存在這個問題，而如果使用標準SQL寫法，在表連線數超過2張的時候，就會引發bug，Oracle會忽略列重名問題。

SQL> alter table t3 add (id1 number);

Table altered.

SQL> select id from t1 join t2 on t1.id = t2.id join t3 on t1.id = t3.id1;

no rows selected

SQL> alter table t3 drop (id);

Table altered.

SQL> select id from t1 join t2 on t1.id = t2.id join t3 on t1.id = t3.id1;
select id from t1 join t2 on t1.id = t2.id join t3 on t1.id = t3.id1
*
ERROR at line 1:
ORA-00918: column ambiguously defined

測試還發現，導致問題的原因只和表中是否存在列有關，而與是否是連線列沒有關係，因此必須三張或以上的表擁有相同的列名，才會引發這個bug。