9i下含有connect by會導致以關聯欄位為條件做查詢時，結果不正確的現象

兩表做關聯，其中包含connect by子句，以關聯欄位作為條件去查詢，結果顯示不正確。改成先connect by，再做兩表關聯，然後以關聯欄位去做查詢，就不會出現這個問題了 。
文章發表地址：http://www.itpub.net/470311.html[@more@]測試環境
Windows 2000 Server SP4 , Oracle 9.2.0.1

現有兩表TA（資料表）、TCODE（程式碼表）如下：
CREATE TABLE TA
(
N_ID NUMBER(10) NOT NULL,
C_NAME VARCHAR2(50 BYTE),
N_SUP_ID NUMBER(10),
C_COLOR VARCHAR2(10 BYTE),
N_YXX NUMBER(1) DEFAULT 1 NOT NULL
);
ALTER TABLE TA ADD (
PRIMARY KEY
(N_ID));
COMMENT ON COLUMN TA.N_SUP_ID IS '父記錄的ID編號';

CREATE TABLE TCODE
(
C_DM VARCHAR2(10 BYTE),
C_MC VARCHAR2(50 BYTE)
);
ALTER TABLE TCODE ADD (
PRIMARY KEY
(C_DM));

測試資料如下：
INSERT INTO TA ( N_ID, C_NAME, N_SUP_ID, C_COLOR, N_YXX ) VALUES (
1, 'a', NULL, 'A', 0);
INSERT INTO TA ( N_ID, C_NAME, N_SUP_ID, C_COLOR, N_YXX ) VALUES (
2, 'b', NULL, 'B', 1);
INSERT INTO TA ( N_ID, C_NAME, N_SUP_ID, C_COLOR, N_YXX ) VALUES (
3, 'c', NULL, 'C', 1);
INSERT INTO TA ( N_ID, C_NAME, N_SUP_ID, C_COLOR, N_YXX ) VALUES (
4, 'ab', 1, 'D', 0);
INSERT INTO TA ( N_ID, C_NAME, N_SUP_ID, C_COLOR, N_YXX ) VALUES (
5, 'ca', 3, 'D', 1);
INSERT INTO TA ( N_ID, C_NAME, N_SUP_ID, C_COLOR, N_YXX ) VALUES (
6, 'abc', 4, 'A', 0);
INSERT INTO TA ( N_ID, C_NAME, N_SUP_ID, C_COLOR, N_YXX ) VALUES (
7, 'abd', 4, 'A', 1);
INSERT INTO TA ( N_ID, C_NAME, N_SUP_ID, C_COLOR, N_YXX ) VALUES (
8, 'ac', 1, 'C', 0);
COMMIT;

INSERT INTO TCODE ( C_DM, C_MC ) VALUES (
'A', '黑色');
INSERT INTO TCODE ( C_DM, C_MC ) VALUES (
'B', '紅色');
INSERT INTO TCODE ( C_DM, C_MC ) VALUES (
'C', '藍色');
INSERT INTO TCODE ( C_DM, C_MC ) VALUES (
'E', '灰色');
COMMIT;

模擬問題出現的SQL語句
/*816不支援這種寫法,9201下這種寫法，在以C_COLOR為條件做查詢時會導致記錄數不正確
*/
create view tatest as
select A.N_ID, A.C_NAME, A.N_SUP_ID, A.C_Color, nvl(b.c_mc, a.c_color)
c_color_mc, prior a.c_name c_sup_name, prior a.C_color c_sup_color
from ta A, tcode B
where a.c_color=b.c_dm
start with a.n_sup_id is null
connect by prior n_id=n_sup_id;
（是否做成檢視都會出現下面的bug）
先看看裡面的資料

程式碼:


SQL> select * from tatest;



      N_ID C_NAME       N_SUP_ID     C_COLOR    C_COLOR_MC  C_SUP_NAME  C_SUP_COLO
----------  ---------- ----------   ----------  --------    --------    --------

         1 a                         A          黑色

         8 ac               1        C          藍色              a       A

         2 b                         B          紅色

         3 c                         C          藍色

。。。

總共才4條
然而分別執行下面的語句
select * from tatest where c_color='A';--得到33條記錄
select * from tatest where c_color='B';--得到1條記錄
select * from tatest where c_color='C';--得到5條記錄
貼出最後一條sql的執行結果

程式碼:


SQL> select * from tatest where c_color='C';



      N_ID C_NAME       N_SUP_ID C_COLOR    C_COLOR_MC    C_SUP_NAME   C_SUP_COLO
---------- ----------- ---------- ---------- ------------ ----------- ----------

         8 ac                   1 C          藍色          a           A

         8 ac                   1 C          藍色          a           A

         8 ac                   1 C          藍色          a           A

         8 ac                   1 C          藍色          a           A

         3 c                      C          藍色


.....................

發現有不少重複記錄
而若以其他非兩表的關聯欄位作為查詢條件，則此現象不會出現