ITPub博客

首页 > 数据库 > Oracle > 测试一下你的Oracle有Full Outer Join的Bug么?9i以上请进

测试一下你的Oracle有Full Outer Join的Bug么?9i以上请进

原创 Oracle 作者:lastwinner 时间:2005-11-23 15:08:46 0 删除 编辑
BUG描述:FULL OUTER JOIN <> LEFT OUTER JOIN UNION RIGHT OUTER JOIN
不知道Oracle网站上有没有相关描述,我在网上相关信息只搜索到left outer join可使用户可以查询任何表

我的数据库服务器环境:OS:Windows 2000 Server DB:Oracle 9.2.0.1
测试请写出你的数据库服务器环境
[@more@]创建测试表
--子表
CREATE TABLE TS
(
ID VARCHAR2(4 BYTE),
PID VARCHAR2(4 BYTE),
DM1 VARCHAR2(4 BYTE),
DM2 VARCHAR2(4 BYTE)
);
--父表,ID与子表PID对应
CREATE TABLE TP
(
ID VARCHAR2(4 BYTE),
CON VARCHAR2(4 BYTE)
);
--代码表
CREATE TABLE TDM
(
TYPE VARCHAR2(4 BYTE),
DM VARCHAR2(4 BYTE),
MC VARCHAR2(10 BYTE)
);
插入测试数据
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'1', '1', 'a', 'aa');
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'2', '2', 'b', NULL);
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'3', '1', 'a', 'ss');
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'4', '3', 'a', 'ss');
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'5', '3', 'c', NULL);
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'7', '7', 'c', 'ee');
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'9', NULL, 'a', NULL);
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'10', '9', NULL, NULL);
COMMIT;
INSERT INTO TP ( ID, CON ) VALUES (
'1', 'a');
INSERT INTO TP ( ID, CON ) VALUES (
'2', 'b');
INSERT INTO TP ( ID, CON ) VALUES (
'3', 'c');
INSERT INTO TP ( ID, CON ) VALUES (
'6', 'f');
COMMIT;
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'1', 'a', 'AA');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'1', 'b', 'BB');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'1', 'c', 'CC');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'1', 'd', 'DD');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'1', 'e', 'EE');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'2', 'aa', 'AAAA');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'2', 'ss', 'SSSS');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'2', 'dd', 'DDDD');
COMMIT;
表TS与表TDM外连接两次,查出DM1和DM2对应的MC
select a.*,b.mc MC1, c.mc MC2
from ts a,tdm b, tdm c
where A.dm1 = B.DM(+) AND B.type(+)='1'
and A.dm2 = c.DM(+) AND c.type(+)='2'
这个sql将会在下面的测试中多次用到
(其实只要3和6都做了就能看到这个BUG)
1)测试LEFT OUTER JOIN
select s.*,p.* from
(
select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c
where A.dm1 = B.DM(+) AND B.type(+)='1'
and A.dm2 = c.DM(+) AND c.type(+)='2'
) s left outer join
(
select * from tp
) p
on s.pid=p.id
2)测试RIGHT OUTER JOIN
select s.*,p.* from
(
select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c
where A.dm1 = B.DM(+) AND B.type(+)='1'
and A.dm2 = c.DM(+) AND c.type(+)='2'
) s right outer join
(
select * from tp
) p
on s.pid=p.id
3)测试LEFT OUTER JOIN UNION RIGHT OUTER JOIN
select s.*,p.* from
(
select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c
where A.dm1 = B.DM(+) AND B.type(+)='1'
and A.dm2 = c.DM(+) AND c.type(+)='2'
) s left outer join
(
select * from tp
) p
on s.pid=p.id
union
select s.*,p.* from
(
select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c
where A.dm1 = B.DM(+) AND B.type(+)='1'
and A.dm2 = c.DM(+) AND c.type(+)='2'
) s right outer join
(
select * from tp
) p
on s.pid=p.id
4)测试LEFT OUTER JOIN UNION ALL RIGHT OUTER JOIN
select s.*,p.* from
(
select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c
where A.dm1 = B.DM(+) AND B.type(+)='1'
and A.dm2 = c.DM(+) AND c.type(+)='2'
) s left outer join
(
select * from tp
) p
on s.pid=p.id
union all
select s.*,p.* from
(
select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c
where A.dm1 = B.DM(+) AND B.type(+)='1'
and A.dm2 = c.DM(+) AND c.type(+)='2'
) s right outer join
(
select * from tp
) p
on s.pid=p.id
5)测试内连接(INNER JOIN)
select s.* from
(
select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c
where A.dm1 = B.DM(+) AND B.type(+)='1'
and A.dm2 = c.DM(+) AND c.type(+)='2'
) s inner join
(
select * from tp
) p
on s.pid=p.id
6)测试全外连接(FULL OUTER JOIN)
select s.*,p.* from
(
select a.*,b.mc MC1, c.mc MC2
from ts a,tdm b, tdm c
where A.dm1 = B.DM(+) AND B.type(+)='1'
and A.dm2 = c.DM(+) AND c.type(+)='2'
) s full outer join
(
select * from tp
) p
on s.pid=p.id
下面是测试结果(只列举了3和6)
代码:

SQL
> select * from ts;
ID PID DM1 DM2 ---- ---- ---- ---- 1 1 a aa
2 2 b
3 1 a ss
4 3 a ss
5 3 c
7 7 c ee
9 a
10 9

已选择8行。

SQL
> select * from tp
;
ID CON ---- ---- 1 a
2 b
3 c
6 f

SQL
> select * from tdm
;
TYPE DM MC ---- ---- ---------- 1 a AA
1 b BB
1 c CC
1 d DD
1 e EE
2 aa AAAA
2 ss SSSS
2 dd DDDD

已选择8行。

SQL
> select s.*,p.*
from
2
(
3 select a.*,b.mc MC1, c.
mc MC2
4 from ts a
,tdm b,
tdm c
5 where A
.dm1 = B.DM(+) AND B.type(+)=
'1'
6 and A.dm2 = c.DM(+) AND c.type(+)=
'2'
7 )
s full outer join
8
(
9 select *
from tp
10
)
p
11 on s
.pid=p.
id
12
/
ID PID DM1 DM2 MC1 MC2 ID CON ---- ---- ---- ---- ---------- ---------- ---- ---- 3 1 a ss AA SSSS 1 a
1 1 a aa AA AAAA 1 a
2 2 b BB 2 b
5 3 c CC 3 c
4 3 a ss AA SSSS 3 c
9 a AA
10 9
7 7 c ee CC
6 f
2 b

已选择10行。

SQL
> select s.*,p.*
from
2
(
3 select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b,
tdm c
4 where A
.dm1 = B.DM(+) AND B.type(+)=
'1'
5 and A.dm2 = c.DM(+) AND c.type(+)=
'2'
6 )
s left outer join
7
(
8 select *
from tp
9
)
p
10 on s
.pid=p.
id
11 union
12 select s
.*,p.*
from
13
(
14 select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b,
tdm c
15 where A
.dm1 = B.DM(+) AND B.type(+)=
'1'
16 and A.dm2 = c.DM(+) AND c.type(+)=
'2'
17 )
s right outer join
18
(
19 select *
from tp
20
)
p
21 on s
.pid=p.
id
22
/
ID PID DM1 DM2 MC1 MC2 ID CON ---- ---- ---- ---- ---------- ---------- ---- ---- 1 1 a aa AA AAAA 1 a
10 9
2 2 b BB 2 b
3 1 a ss AA SSSS 1 a
4 3 a ss AA SSSS 3 c
5 3 c CC 3 c
7 7 c ee CC
9 a AA
6 f

已选择9行。
。。。。。。。



可见在这里出现了FULL OUTER JOIN <> LEFT OUTER JOIN UNION RIGHT OUTER JOIN的现象,而按常理这两个应该是相等的
即FULL OUTER JOIN = LEFT OUTER JOIN UNION RIGHT OUTER JOIN并且还有
LEFT OUTER JOIN UNION ALL RIGHT OUTER JOIN - INNER JOIN = FULL OUTER JOIN
如果表TS与表TDM只外连接一次,那么不会出现这个现象。

另外还发现一个有趣的怪现象,对表TP做一个简单的变换(并不影响其结果集的记录数),做一次full outer join
然后加上一个条件限制(也不影响其结果集的记录数),再做一次full outer join
两次的结果居然不一样,而且还都是不对的
代码:

SQL
> select s.*,p.* from
2
(
3 select a.*,b.mc MC1, c.
mc MC2
4 from ts a
,tdm b,
tdm c
5 where A
.dm1 = B.DM(+) AND B.type(+)=
'1'
6 and A.dm2 = c.DM(+) AND c.type(+)=
'2'
7 )
s full outer join
8
(
9 select * from (select tp.*,rank()over(order by id) rn from tp) --where rn<
10
10
)
p
11 on s
.pid=p.
id
12
/
ID PID DM1 DM2 MC1 MC2 ID CON RN ---- ---- ---- ---- ---------- ---------- ---- ---- ---------- 3 1 a ss AA SSSS 1 a 1
1 1 a aa AA AAAA 1 a 1
2 2 b BB 2 b 2
5 3 c CC 3 c 3
4 3 a ss AA SSSS 3 c 3
9 a AA
10 9
7 7 c ee CC
6 f
2 b

已选择10行。

这个和刚才的测试6效果一样,最后一条记录是多余的

SQL
> select s.*,p.*
from
2
(
3 select a.*,b.mc MC1, c.
mc MC2
4 from ts a
,tdm b,
tdm c
5 where A
.dm1 = B.DM(+) AND B.type(+)=
'1'
6 and A.dm2 = c.DM(+) AND c.type(+)=
'2'
7 )
s full outer join
8
(
9 select * from (select tp.*,rank()over(order by id) rn from tp) where rn<
10
10
)
p
11 on s
.pid=p.
id
12
/
ID PID DM1 DM2 MC1 MC2 ID CON RN ---- ---- ---- ---- ---------- ---------- ---- ---- ---------- 3 1 a ss AA SSSS 1 a 1
1 1 a aa AA AAAA 1 a 1
2 2 b BB 2 b 2
5 3 c CC 3 c 3
4 3 a ss AA SSSS 3 c 3
9 a AA
10 9
7 7 c ee CC

已选择8行。

这个结果跟左连接没啥区别
-V
-
SQL> select s.*,p.*
from
2
(
3 select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b,
tdm c
4 where A
.dm1 = B.DM(+) AND B.type(+)=
'1'
5 and A.dm2 = c.DM(+) AND c.type(+)=
'2'
6 )
s left outer join
7
(
8 select *
from tp
9
)
p
10 on s
.pid=p.
id
11
/
ID PID DM1 DM2 MC1 MC2 ID CON ---- ---- ---- ---- ---------- ---------- ---- ---- 3 1 a ss AA SSSS 1 a
1 1 a aa AA AAAA 1 a
2 2 b BB 2 b
5 3 c CC 3 c
4 3 a ss AA SSSS 3 c
9 a AA
10 9
7 7 c ee CC

已选择8行。

。。。。。。。。。。。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29867/viewspace-810583/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 铅笔的原则
请登录后发表评论 登录
全部评论

注册时间:2007-12-12

  • 博文量
    223
  • 访问量
    2813040