無意中發現了ORACLE JOIN語法的獨到之處!! 讓一向對ORACLE很感冒的我再次肅然起敬!

為了示範,我建了三個TABLE,各INSERT一筆資料。

CREATE TABLE JFSoftware (
Code VARCHAR2(8), Name VARCHAR2(32), VendorCode VARCHAR2(4), CatgCode VARCHAR2(4)
);
CREATE TABLE JFVendor (Code VARCHAR2(4), Name VARCHAR2(16));
CREATE TABLE JFCategory (Code VARCHAR2(4), Name VARCHAR2(16));
INSERT INTO JFVendor VALUES ('MS','Microsoft');
INSERT INTO JFCategory VALUES ('DEV','Developer Tools');
INSERT INTO JFSoftware VALUES ('VS2005','Visual Studio 2005','MS','DEV');

好,我們現在有三個資料表,一個存軟體資料,一個放廠商資料,一個則是分類,接著我想列出軟體、分類名稱與廠商名稱,所以組出了以下的JOIN寫法:

SELECT S.CODE, C.NAME, NAME
FROM JFSoftware S, JFVendor V, JFCategory C
WHERE S.VendorCode=V.Code AND S.CatgCode=C.Code

這裡故意犯了一個錯,第二個NAME應該是V.NAME才對,依我多年的SQL經驗,此語法會被資料庫抓出錯誤而無法執行。Yes,ORACLE傳回了ORA-00918: column ambiguously defined! 錯誤。

有些人習慣用ANSI JOIN,如果上述的錯誤是發生在以下的寫法呢?

SELECT S.CODE, C.NAME, NAME
FROM JFSoftware S
JOIN JFVendor V ON S.VendorCode=V.Code
JOIN JFCategory C ON S.CatgCode=C.Code

ORACLE被太多的JOIN搞昏頭了(實驗結果是JOIN兩個TABLE時ORACLE會認定語法錯誤,但JOIN三個TABLE時就...),居然讓這段SQL放行,並傳回以下的結果。

CODE     NAME             NAME 
-------- ---------------- ---------------- 
VS2005   Developer Tools  Developer Tools
1 row(s)

公司的DBA協助查出,這是個Bug(ORACLE的官方文件要登入才能看,但大家可以查這篇文章中的Bug 3584515,看看標題,心領神會一番),據說應會在9.2.0.7及10.2.0.1更正。


Comments

# by Trey

你好 今日工作上意外碰到類似該篇的情況 Oracle 10.2.0.1.0版本環境下可以執行並查出數據,同樣指令卻在10.2.0.5.0出現 ORA-00918: column ambiguously defined 猜測可能於10.2.0.5.0版 修復 因為之後又有查到別人的文章說是11g才修復 請問你之後有找出確切的版本嗎? 謝謝

# by tony hung

re Trey : 明確地對每個欄位指定是那個table的是比較安全的作法,不然再換版本你又會發生, 如下所示 : SELECT S.CODE, C.NAME, V.NAME FROM JFSoftware S, JFVendor V, JFCategory C WHERE S.VendorCode=V.Code AND S.CatgCode=C.Code

# by Trey

感謝回應,實際上最後還是堅持原則(要把SQL寫對),才是必要的吧XDD 若有機會也許可以嘗試在11G上試試看你的這個問題是否解決

# by sohbet

güzel bir blog

# by oper@mit.tc

nice blog

# by Jack

原本的 SQL 在 9i 是 ok 的。 我在 11g 遇到這個錯誤,有給予欄位指定明確的 TableName,還是出錯...

Post a comment