===========================================================
===========================================================
9i下含有connect by会导致以关联字段为条件做查询时，结果不正确的现象
作者: lastwinner(http://lastwinner.itpub.net)发表于: 2005.12.23 16:24
分类: Oracle ,
出处: http://lastwinner.itpub.net/post/7102/49089
---------------------------------------------------------------
两表做关联，其中包含connect by子句，以关联字段作为条件去查询，结果显示不正确。改成先connect by，再做两表关联，然后以关联字段去做查询，就不会出现这个问题了 。
文章发表地址：http://www.itpub.net/470311.html
测试环境
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）
先看看里面的数据
总共才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的执行结果
发现有不少重复记录
而若以其他非两表的关联字段作为查询条件，则此现象不会出现
文章发表地址：http://www.itpub.net/470311.html
测试环境
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 蓝色
.....................
发现有不少重复记录
而若以其他非两表的关联字段作为查询条件，则此现象不会出现
向往平静的生活
