9i下含有connect by会导致以关联字段为条件做查询时，结果不正确的现象

lastwinner · 发表于 2005-12-23 16:06

9i下含有connect by会导致以关联字段为条件做查询时，结果不正确的现象

测试环境
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）
先看看里面的数据
[php]
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       蓝色
。。。。。。。。。。。
[/php]
总共才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的执行结果
[php]
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       蓝色

.....................
[/php]

发现有不少重复记录
而若以其他非两表的关联字段作为查询条件，则此现象不会出现

不知道其他版本下是否还有类似情况，有兴趣的做个试验？：）

lastwinner · 发表于 2005-12-23 16:12

相关文章：
http://www.itpub.net/454624.html
http://www.itpub.net/455982.html

jackywood · 发表于 2005-12-23 16:43

我这里正常, oracle 10.2.0.1.0
SQL> select N_ID,C_NAME,C_COLOR_MC from tatest where c_color='A';

N_ID C_NAME C_COLOR_MC
----- -------- ----------
1 a       黑色

已选择 1 行。

SQL> select N_ID,C_NAME,C_COLOR_MC from tatest where c_color='B';

N_ID C_NAME C_COLOR_MC
----- -------- ----------
2 b       红色

已选择 1 行。

SQL> select N_ID,C_NAME,C_COLOR_MC from tatest where c_color='C';

N_ID C_NAME C_COLOR_MC
----- -------- ----------
8 ac    蓝色
  3 c       蓝色

已选择2行。